DATA STEP merge on composite key?

tomcmacdonald — Wed, 04 Apr 2018 16:00:00 GMT

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;

Re: DATA STEP merge on composite key?

Astounding — Wed, 04 Apr 2018 16:12:07 GMT

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.

topic DATA STEP merge on composite key? in SAS Programming

DATA STEP merge on composite key?

Re: DATA STEP merge on composite key?