How to merge two data sets with collective unique IDs?

[NonSleeper]

I have two data sets with collective ID variables. That is, each of these variable may not be unique across the data set (i.e., duplicated), but together they form a unique set of ID. Suppose the first hypothetical data set looks like:

ID1          ID2          Var1          Var2         

Joe         A

Joe         B

Smith     A

Smith     C

Ann       D

The second hypothetical data set looks like:

ID1          ID2          Var3          Var4         

Joe         A

Wil         B

Smith     A

Smith     C

Ann       E

So how can I merge these variable using data step?

[gergely_batho · Accepted solution]

proc sort data=data1;

     by ID1 ID2;

run;

proc sort data=data2;

     by ID1 ID2;

run;

data want;

     merge data1 data2;

     by ID1 ID2;

run;

[RW9]

You can also use SQL joins, example:

proc sql;

  create table WANT as

  select  COALESCE(A.MAKE,B.MAKE) as MAKE,

          A.MODEL as BASE_MODEL,

          B.MODEL as COMP_MODEL

  from    SASHELP.CARS A

  full join SASHELP.CARS B

  on      A.MAKE=B.MAKE;

quit;

mysql - What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? - Stack O...