topic Re: How to merge two data sets with collective unique IDs? in SAS Data Management

How to merge two data sets with collective unique IDs?

NonSleeper — Mon, 11 May 2015 06:27:58 GMT

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?

Re: How to merge two data sets with collective unique IDs?

gergely_batho — Mon, 11 May 2015 07:01:17 GMT

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;

Re: How to merge two data sets with collective unique IDs?

RW9 — Mon, 11 May 2015 08:15:21 GMT

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 Overflow