I have two large datasets, where variables are different but shares similar id (or name in this example), how do I find out which obs are common and not?
data one;
length name $30;
input name;
datalines;
mary
peter
john
;
run;
data two;
length name $30;
input name;
datalines;
mary
peter
luke
;
run;
This is what I do in such cases:
proc sort data=one;
by name;
run;
proc sort data=two;
by name;
run;
data
common
only_one
only_two
;
merge
one (in=o)
two (in=t)
;
by name;
if o and t
then output common;
else if o
then output only_one;
else output only_two;
run;
This is what I do in such cases:
proc sort data=one;
by name;
run;
proc sort data=two;
by name;
run;
data
common
only_one
only_two
;
merge
one (in=o)
two (in=t)
;
by name;
if o and t
then output common;
else if o
then output only_one;
else output only_two;
run;
PS to speed up the process, sort to new datasets and keep only the key variable:
proc sort
data=one (keep=name)
out=one_s
;
by name;
run;
proc sort
data=two (keep=name)
out=two_s
;
by name;
run;
data
common
only_one
only_two
;
merge
one_s (in=o)
two_s (in=t)
;
by name;
if o and t
then output common;
else if o
then output only_one;
else output only_two;
run;
data one;
length name $30;
input name;
datalines;
mary
peter
john
;
run;
data two;
length name $30;
input name;
datalines;
mary
peter
luke
;
run;
proc sql;
create table common_in_both as
select name from one
intersect
select name from two;
create table in_one_but_not_in_two as
select name from one
except
select name from two;
create table in_two_but_not_in_one as
select name from two
except
select name from one;
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!
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.