Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 37 ยท Data Manipulation

Combining Data Sets: Stacking, Interleaving and MERGE

Combine SAS data sets: stack with SET, interleave with SET and BY, match-merge with MERGE and BY, use IN= flags for inner and outer joins, handle name clashes and avoid many-to-many merges.

Data rarely arrives in one table. Customers live in one, orders in another, prices in a third. SAS combines data sets in the DATA step with SET (adding rows) and MERGE (adding columns side by side by matching keys). Understanding exactly how each behaves, especially when keys do not match, saves you from silently wrong results.

Stacking rows: SET with several data sets

Listing more than one data set on SET appends them one after the other (concatenation):

SAS
data all_years;
   set sales_2023 sales_2024;
run;

All observations of sales_2023 come first, then those of sales_2024. The output has the union of the variables. If a variable is missing from one input, its values are missing for that input's rows. Watch for variables with the same name but different types (a number in one and text in the other): SAS stops with an error, so convert one first.

The in= option marks where each row came from:

SAS
data all_years;
   set sales_2023 (in=a) sales_2024 (in=b);
   if a then source = "2023";
   else if b then source = "2024";
run;

in=a creates a temporary variable that is 1 when the current row came from that data set. It is not written to the output.

For appending a small data set to a large one, proc append base=big data=small; is much faster because it does not rewrite the big one. PROC SQL offers union all for the same stacking.

Interleaving: SET with BY

Add a by statement and SAS merges the inputs into sorted order by the key instead of stacking:

SAS
data combined;
   set east west;
   by date;
run;

Each input must already be sorted by date. The result is sorted by date, with rows from both sets interleaved. This is useful when you want one sorted file from several sorted sources, for example daily files from different regions.

Merging columns: MERGE with BY

MERGE combines observations from two or more data sets side by side. With a by statement it does a match-merge: rows with the same key value are joined into one row.

SAS
proc sort data=customers; by cust_id; run;
proc sort data=orders;    by cust_id; run;

data customer_orders;
   merge customers orders;
   by cust_id;
run;

Rules to remember:

  • Both inputs must be sorted by the BY variables. Otherwise SAS gives an error about the sort order.
  • Variables with the same name in both inputs (other than the BY variable) are overwritten: the value from the last data set listed wins for matching rows. Rename to avoid it: orders (rename=(date=order_date)).
  • Unmatched rows are kept by default; the columns from the other data set are missing for them. That is a full outer join.

Without by, merge does a one-to-one merge: row 1 with row 1, row 2 with row 2, and so on, with no matching on values. This is almost never what you want, because a missing or extra row silently shifts everything after it.

IN= flags: inner, left and anti joins

To control which rows survive, use in= to record which data sets contributed:

SAS
data matched;      /* inner join */
   merge customers (in=c) orders (in=o);
   by cust_id;
   if c and o;
run;

data customers_no_orders;    /* anti join */
   merge customers (in=c) orders (in=o);
   by cust_id;
   if c and not o;
run;
Condition Result
if c and o; Only keys found in both (inner join)
if c; All customers, orders where they exist (left join)
if o; All orders (right join)
if c and not o; Customers without orders
if c ne o; Keys found in only one
(no if) Everything (full outer join)

The in= variables are 1 or 0 and are never written to the output unless you assign them to a new variable (has_order = o;).

Many-to-many merges

A key can repeat in both data sets: a customer with three orders merged with a table of three addresses. A DATA step MERGE does not create all 9 combinations. It matches rows in order, which gives a result that is almost certainly not what you intended, and the log warns:

TEXT
NOTE: MERGE statement has more than one data set with repeats of BY values.

Treat this note as an error. Make one side unique first (for example with nodupkey) or use PROC SQL, whose joins do create the Cartesian combination per key. SQL joins arrive in the SQL in SAS chapter.

Update and modify

Two more statements are worth knowing by name:

  • UPDATE master transactions; by key; applies changes from a transaction data set to a master data set; missing transaction values do not overwrite.
  • MODIFY changes a data set in place without rewriting it. It is powerful and risky; make a backup first.

Choosing between SET, MERGE and PROC SQL

You want Use
Add rows set a b; or proc append
Join by key, keys unique on at least one side merge with by
Join with repeated keys on both sides proc sql join
Apply updates update
Look up a value from a small table Format, proc sql or a hash object

A match-merge model

This Python stand-in (not SAS) reproduces a match-merge with in= flags: unmatched keys on either side, and the "last data set wins" rule for a shared variable name.

Python Python stand-in: MERGE with BY and IN= flags

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that customer 1's city is Paris in the output: the order's value overwrote the customer's value because the orders data set came last. In SAS, rename one of the variables first.

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does 'set a b;' do?

    Several data sets on one SET statement are concatenated.
  2. 2. What must be true of the data sets before a MERGE with BY?

    Match-merge requires each input to be sorted by the BY variables.
  3. 3. How do you keep customers that have no orders?

    With in= flags c and o, 'c and not o' selects customers without any matching order.
  4. 4. A shared non-BY variable name appears in both data sets of a MERGE. What happens for matching rows?

    Rename variables before merging to avoid silent overwriting.
  5. 5. What does MERGE do when BY values repeat in more than one input?

    A many-to-many merge is not a Cartesian product in the DATA step.

Summary

  • set a b; stacks rows; add by to interleave sorted data; in= records the source.
  • merge a b; by key; match-merges sorted data; without by it pairs rows by position, which is rarely right.
  • Use in= flags with if to get inner, left, right and anti joins.
  • Same-named variables are overwritten by the last data set; rename first.
  • Treat the repeats-of-BY-values note as an error; use PROC SQL for many-to-many joins.

Next: PROC PRINT and PROC CONTENTS