SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to merge two data sets with collective unique IDs?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

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?

Posted in reply to NonSleeper

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;

View solution in original post


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

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

Posted in reply to NonSleeper

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
Super User
Posts: 7,942

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

Posted in reply to NonSleeper

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...

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 284 views
  • 0 likes
  • 3 in conversation