topic Re: To see the observations in a dataset but not in the other in SAS Programming

To see the observations in a dataset but not in the other

manya92 — Tue, 24 Jul 2018 17:36:14 GMT

Hi,

I have two datasets, and i want to see what observations are in dataset2 that are not in dataset1.

How to do that ?

My code is this

/*proc sql ;
	create table DATASET1 as
	select distinct enrolid, code, (svcdate) as index_cpt format mmddyy10. 
	from derived._02_cpt ;
quit ;/*179*/

/*proc sql ;
	create table DATASET2 as  
	select distinct a.enrolid, min(a.index_diag, b.index_cpt) as index_date 
        format mmddyy10.
	from derived._02_tr2 as a 
	inner join derived._02_cpt2 as b 
	on a.enrolid = b.enrolid 
	where -7 le (a.index_diag-b.index_cpt) le 7
	order by a.enrolid, index_date;
quit ;/*188*/

Re: To see the observations in a dataset but not in the other

PaigeMiller — Tue, 24 Jul 2018 19:00:57 GMT

In PROC SQL, do a FULL JOIN, the resulting data set will include all observations from both data sets, and the ones that are in one data set but not the other will be obvious.

Re: To see the observations in a dataset but not in the other

PeterClemmensen — Tue, 24 Jul 2018 19:48:07 GMT

If your data is simple, you can so something like this

data data1;
a=1;output;
a=2;output;
a=3;output;
run;

data data2;
a=1;output;
a=3;output;
run;

proc sql;
   create table want as
   select * from data1
   where a not in
   (
   select distinct a from data2
   );
quit;

Otherwise, when you compare two data sets, PROC COMPARE is a pretty good place to start.