## How to merge two data sets with collective unique IDs?

Solved
Frequent Contributor
Posts: 75

# How to merge two data sets with collective unique IDs?

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?

Accepted Solutions
Solution
‎05-11-2015 03:01 AM
SAS Employee
Posts: 340

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

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;

All Replies
Solution
‎05-11-2015 03:01 AM
SAS Employee
Posts: 340

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

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;

Super User
Posts: 9,441

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

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&#39;s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? - Stack O...

🔒 This topic is solved and locked.