topic Proc Compare output - Only not found observation from any dataset (base or compare) in SAS Programming

Proc Compare output - Only not found observation from any dataset (base or compare)

nkm123 — Wed, 27 Apr 2016 14:50:11 GMT

Hi, Is there any way to output all observations that are no found in any of comparision dataset (base or compare ) by proc compare?

proc compare base = test compare = test1 listall ;

id sales_id;

run;

What should be "out" option to get not found observations from any dataset ?

Thanks,

Re: Proc Compare output - Only not found observation from any dataset (base or compare)

ballardw — Wed, 27 Apr 2016 15:04:15 GMT

You should provide a few example rows of data from two sets and the desired output.

Yout may be looking for LISTBASE or LISTBASEOBS but I'm not quite sure of your intent.

Re: Proc Compare output - Only not found observation from any dataset (base or compare)

nkm123 — Wed, 27 Apr 2016 17:01:37 GMT

Hi,

Please see example. as only store id = 3 is common I need 1,2, from test dataset and 4,5 from test1 dataset into output. I can do merge/sql but is there any ay doing this by proc compare.

								  data test;
								  store_id =1;type='P';output;
								  store_id =2;type='Q';output;
								  store_id =3;type='R';output;
								  run;
								  data test1;
								  store_id =3;type='R';output;
								  store_id =4;type='S';output;
								  store_id =5;type='T';output;
								  run;
									
								  data want;
								  store_id =1;type='P';output;
								  store_id =2;type='Q';output;
								  store_id =4;type='S';output;
								  store_id =5;type='T';output;
								  run;

Thanks,

Re: Proc Compare output - Only not found observation from any dataset (base or compare)

ballardw — Wed, 27 Apr 2016 17:35:59 GMT

For what you are attempting you would be better off with an OUTER JOIN



proc sql;
   create table want as
   (select * from test
      except
      select * from test1)
   union
   (select * from test1
      except
      select * from test)
   ;
quit;

Re: Proc Compare output - Only not found observation from any dataset (base or compare)

nkm123 — Wed, 27 Apr 2016 19:04:47 GMT

Is there any way to output not found observation in any dataset by using proc compare ?

Re: Proc Compare output - Only not found observation from any dataset (base or compare)

ballardw — Wed, 27 Apr 2016 21:27:59 GMT

Proc compare will not provide an output data set as specified in your example.

Re: Proc Compare output - Only not found observation from any dataset (base or compare)

SASKiwi — Wed, 27 Apr 2016 21:37:22 GMT

Not just by using PROC COMPARE alone. You could use the OUTBASE, OUTCOMP and OUTDIF options and then process these in a following DATA step to keep just the OUTBASE and OUTCOMP records without a corresponding OUTDIF record (OUTDIF only records matches), but this won't cope with duplicate values of your ID variables.

@ballardw's solution will give you what you want in one process that will also cope with duplicate ID values.