SAS Proc Compare question

Question

First off, I know pretty much nothing about SAS and I am not a programmer but an accountant, but here it goes:

I am trying to compare two data sets to identify differences between them, so I am using the 'proc compare' command as follows:

proc compare data=table1 compare=table2
criterion=.01;
run;

This works fine, but it compares line by line and in order, so if table2 is missing a row half way through, then all entries after that row will be returned as not equal.

How do I ask the comparison to be made based on a variable so that the proc compare finds the value associated with variable X in table 1, and then makes sure that the same variable X in table 2 has the same value?

Answer 1

The ID statement in PROC COMPARE is used to match rows. This code may work for you:

proc compare data=table1 compare=table2 criterion=.01; 
  id X;
run;

You may need to use PROC SORT to sort the data by X before doing the PROC COMPARE. Refer to the PROC COMPARE documentation for details on the ID statement to determine if you should sort or not.

Here is a link to the PROC COMPARE documentation:

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a000057814.htm