DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 30

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

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,

Super User
Posts: 11,343

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

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.

Contributor
Posts: 30

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

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,

 

Super User
Posts: 11,343

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

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;
Contributor
Posts: 30

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

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

Super User
Posts: 11,343

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

 

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

Super User
Posts: 3,257

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

[ Edited ]

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. 

Ask a Question
Discussion stats
  • 6 replies
  • 256 views
  • 0 likes
  • 3 in conversation