How do I merge datasets on more than one key?
Something equivalent to:
PROC SQL;
SELECT *
FROM table_a JOIN table_b ON (table_a.col1 = table_b.col1 AND table_a.col2 = table_b.col2);
QUIT;
The equivalent would be along these lines.
The data sets must be in order first. If not, sort them:
proc sort data=table_a;
by col1 col2;
run;
proc sort data=table_b;
by col1 col2;
run;
Then MERGE:
data want;
merge table_a (in=found_in_table_a)
table_b (in=found_in_table_b);
by col1 col2;
run;
You can also add to the DATA step, utilizing the temporary flags (in this case named FOUND_IN_TABLE_A and FOUND_IN_TABLE_B). Those flags could subset the records to get the equivalent of left, right, or inner joins.
One thing that is different in a DATA step vs. SQL: the variables COL1 and COL2 should have same attributes in both incoming data sets. Results are not guaranteed, for example, if COL1 has a different length in TABLE_A vs. in TABLE_B.
The equivalent would be along these lines.
The data sets must be in order first. If not, sort them:
proc sort data=table_a;
by col1 col2;
run;
proc sort data=table_b;
by col1 col2;
run;
Then MERGE:
data want;
merge table_a (in=found_in_table_a)
table_b (in=found_in_table_b);
by col1 col2;
run;
You can also add to the DATA step, utilizing the temporary flags (in this case named FOUND_IN_TABLE_A and FOUND_IN_TABLE_B). Those flags could subset the records to get the equivalent of left, right, or inner joins.
One thing that is different in a DATA step vs. SQL: the variables COL1 and COL2 should have same attributes in both incoming data sets. Results are not guaranteed, for example, if COL1 has a different length in TABLE_A vs. in TABLE_B.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.