topic Re: Compare two large datasets and see which observations are missing in SAS Programming

Compare two large datasets and see which observations are missing

ywon111 — Tue, 12 Oct 2021 07:04:35 GMT

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;

Re: Compare two large datasets and see which observations are missing

Kurt_Bremser — Tue, 12 Oct 2021 07:09:31 GMT

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;

Re: Compare two large datasets and see which observations are missing

Kurt_Bremser — Tue, 12 Oct 2021 07:13:10 GMT

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;

Re: Compare two large datasets and see which observations are missing

Ksharp — Tue, 12 Oct 2021 12:20:15 GMT

 

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;