BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tomcmacdonald
Quartz | Level 8

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

1 REPLY 1
Astounding
PROC Star

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1970 views
  • 0 likes
  • 2 in conversation