Proc Compare

[rmartins]

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

[Jagadishkatam]

did you try using the out=datasetname in proc compare

[rmartins]

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.

[Haikuo]

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

[rmartins]

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.