Combining Data Sets with SET and MERGE

The examples in this lesson use two small data sets:

data customers;
   input id name $;
   datalines;
1 Ada
2 Alan
3 Grace
;
run;

data orders;
   input id amount;
   datalines;
1 50
1 20
3 75
4 10
;
run;

Stacking with SET

Listing several data sets on one SET statement concatenates them — all rows of the first, then all rows of the second:

data q1;
   input month $ sales;
   datalines;
Jan 100
Feb 120
;
run;

data q2;
   input month $ sales;
   datalines;
Apr 90
;
run;

data half;
   set q1 q2;
run;

proc print data=half noobs;
run;

month    sales

Jan       100
Feb       120
Apr        90

Variables that exist in only one data set are missing in rows from the other. Add a BY statement (with sorted inputs) to interleave rows in sorted order instead.

Match-merging with MERGE and BY

MERGE with BY joins rows that share a key. Both data sets must be sorted by the key (these already are):

data joined;
   merge customers(in=in_cust) orders(in=in_ord);
   by id;
   from_cust = in_cust;
   from_ord = in_ord;
run;

proc print data=joined noobs;
run;

id    name     amount    from_cust    from_ord

 1    Ada        50          1            1
 1    Ada        20          1            1
 2    Alan        .          1            0
 3    Grace      75          1            1
 4               10          0            1

• A plain MERGE keeps every key from both sides (a full outer join).
• IN= creates a temporary flag that is 1 when the current row came from that data set. (The flags aren't saved, so this example copies them into real variables to show them.)
• One-to-many works naturally: Ada's name is repeated for both orders.

Choose rows with the flags:

Common mistakes

• Unsorted inputs — ERROR: BY variables are not properly sorted. Run PROC SORT on each data set first.
• Same variable name in both (other than the key) — the value from the data set listed last wins, silently. Rename one with (rename=...).
• Many-to-many merges — when both sides repeat a key, SAS writes NOTE: MERGE statement has more than one data set with repeats of BY values. and pairs rows one by one, which is almost never what you want. Use PROC SQL for many-to-many joins.
• Keys with different lengths or types — make them match before merging.

Practice

Using customers and orders, create a data set of customers who have never ordered.

data no_orders;
   merge customers(in=in_cust) orders(in=in_ord);
   by id;
   if in_cust and not in_ord;
   keep id name;
run;

proc print data=no_orders noobs;
run;

id    name

 2    Alan