topic Proc Compare in SAS Procedures

Proc Compare

rmartins — Fri, 15 Aug 2014 16:57:59 GMT

I'm using PROC COMPARE to compare two datasets with a common record ID. I want to create a new dataset that lists all variables and contains a count of the number of records where the value is not equal.

Basically the output will look like this, with a record for every variable (or at least every variable that is different in the two recordsets):

Variable	Records Not Equal
Field 1	10
Field 2	15

The proc compare statement below comes close, but the dataset created by outstats only has 68 records (there are several hundred variables in the original dataset).

proc compare

base=old

compare=new

outstats=differences (where=(_type_ = 'NDIF'))

transpose;

id record_id;

run;

After running this, I see almost exactly what I want in the report in the results viewer but I can't figure out how to get this into a dataset.

Thanks for your help,

Rob

Re: Proc Compare

Jagadishkatam — Fri, 15 Aug 2014 17:32:41 GMT

did you try using the out=datasetname in proc compare

Re: Proc Compare

Haikuo — Fri, 15 Aug 2014 18:13:37 GMT

I don't think there is a straightforward solution, but ODS may work it out, only it needs some additional work, say the sample below.

data h1 h2;

set sashelp.class;

run;

data h2;

set h2;

if ranuni(1)>0.5 then

age=age+1;

if ranuni(2) >0.5 then

weight=weight+2;

if ranuni(3) >0.5 then

height=height+3;

run;

ods output CompareSummary=want1;

proc compare

base=h1

compare=h2;

id name;

run;

ods output close;

data want;

set want1 (firstobs=35);

if not missing(batch);

name_var=scan(batch,1);

n_diff=scan(batch,4);

keep name_var n_diff;

run;

You may need to play with some parameters (firstobs=, etc) to make it work for you.

Good Luck,

Haikuo

Re: Proc Compare

rmartins — Mon, 18 Aug 2014 16:23:31 GMT

thanks Jagadishkatam. out=datasetname creates a dataset that contains a record for every record different between the datasets. What I want is a summary of the differences grouped by variable. outstats= comes close, but for some reason only outputs some of the variables.

Re: Proc Compare

rmartins — Mon, 18 Aug 2014 16:25:17 GMT

Thanks Hai.Kuo. That's clever and I'll play around with it a bit. outstats= is so close to what I want I was hoping there would be a way to tweak that to make it work, but maybe ODS is the answer.