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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.