I want to concatenate two datasets (HAVE1 & HAVE2) and obtain a new dataset that only contains the variables that are in common (WANT) without having to explicitly DROP variables from the concatenated dataset. When I combine the two datasets using the SET statement (CONCAT), I get all variables from both of the original datasets (GET).
EXAMPLE CODE:
data have1;
input var1 var2;
datalines;
11 21
12 22
13 23
;
run;
data have2;
input var1 var2 var3;
datalines;
14 24 31
15 25 32
16 26 33
;
run;
data want;
input var1 var2;
datalines;
11 21
12 22
13 23
14 24
15 25
16 26
;
run;
data concat;
set have1 have2;
run;
data get;
input var1 var2 var3;
datalines;
11 21 .
12 22 .
13 23 .
14 24 31
15 25 32
16 26 33
;
run;
This does it, but it depends on exactly what you want. If you want duplicates removed, you can omit the `ALL` from the `UNION` operator.
proc sql;
create table want2 as
select
*
from
have1
union corr all
select
*
from
have2;
quit;
var1 var2 11 21 12 22 13 23 14 24 15 25 16 26
I think that's the difference between the two, but someone can correct me if I'm mistaken.
proc sql noprint;
select t1.name into :keeplist separated by" "
from dictionary.columns t1 inner join dictionary.columns t2
on upcase(t1.name) = upcase(t2.name)
where t1.libname = "LIB1" and t1.memname = "DS1"
and t2.libname = "LIB2" and t2.memname = "DS2";
quit;
data want;
set
lib1.ds1
lib2.ds2
;
keep &keeplist.;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.