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,
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.
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,
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;
Is there any way to output not found observation in any dataset by using proc compare ?
Proc compare will not provide an output data set as specified in your example.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.