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?
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.