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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1997 views
  • 0 likes
  • 2 in conversation