BookmarkSubscribeRSS Feed
nkm123
Calcite | Level 5

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,

6 REPLIES 6
ballardw
Super User

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.

nkm123
Calcite | Level 5

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,

 

ballardw
Super User

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;
nkm123
Calcite | Level 5

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

ballardw
Super User

 

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

SASKiwi
PROC Star

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. 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1401 views
  • 0 likes
  • 3 in conversation