topic Re: compare in SAS Procedures

compare

helloSAS — Thu, 15 Aug 2013 18:50:26 GMT

how can I use proc compare for datasets that have different number of records?

Example: table A has 5000 records; table B has 5025 records.I want to spit out the 25 records that do not exist in table A and also any other differences.

Thanks

Re: compare

SASKiwi — Thu, 15 Aug 2013 19:47:05 GMT

Proc Compare works in two ways:

1) Compares record for record between two tables (1 with 1, 2 with 2 etc)

2) Compares records using key variables common in both tables that uniquely identify which record in the first table is to be compared with which record in the second table.

So do your tables A and B contain one of more key variables that will uniquely identify a record?

If so use proc compare with an ID statement using these variables.

Re: compare

LarryWorley — Thu, 15 Aug 2013 19:49:20 GMT

I like to use ID variables for this. If you have a set of columns which uniquely identifies each record in both data sets, then ID statement lets proc compare handle the missing records appropriately. So for example with two datasets with unique personids on each record and where the second data set contains 25 records not found on the first, this will workL

proc compare data=one

compare=two

;

id personid ;

run .

Note this assumes both data sets are sorted by personid.

Another option is to use a where clause on the one with more records, but you still have to worry about sort order.

proc compare data=one

compare=two (where = ( not new record condition))

;

run .

HTH

Larry

Re: compare

helloSAS — Thu, 15 Aug 2013 20:19:10 GMT

I tried with the Id statement like the below code. But it does not work.

proc sort data=prep7;by acc_id;run;

proc sort data=prep7o;by acc_id;run;

proc compare base=prep7 compare=prep7o; id acc_id; run;

The problem is, when i sort with acc_id at record 2400 there is a missing record in table A. And sas shows all reords to be different after that record.

Re: compare

LarryWorley — Thu, 15 Aug 2013 21:34:10 GMT

Are you sure your sorts worked? You can check your log to determine that. It does not make sense to me that if you sorted the table A by ACC_ID that record number 2400 should have a missing value, unless the 2399 record before it had missing values. I reach this conclusion because (at least on ASCII machines), missing values are always the smallest possible value, regardless of whether it is a string or numeric variable. I am real rusty on EBCDIC so if you are on a Z/OS machine I could be wrong.

If the sorts did work and you have 2399 missing values in each file, then the compare is pointing to a difference in your files.

Can you post some of the output from proc compare?

Re: compare

Anotherdream — Thu, 15 Aug 2013 22:40:41 GMT

IK don't believe he's saying his sort didn't work, what he's saying is the proc compare is throwing an error for every reord after number 2,400.

That's because BOTH datasets have the first 2,400 records in common, however his dataset #2 is missing record 2,401 that exists in Dataset number 1.

Therefore, every record after that point is now incorrect because SAS is trying to compare record 2,401 in dataset A to record 2,401 in dataset B, however datatset B's record 2,401 is NOT equal to the same one in dataset A.

Correct me if I'm wrong, but i'm pretty sure this is what you mean because i've had it happen to me before.

However, by using the ID variable on your Key, this should not happen. Proc Compare is supposed to get an output like this...


Number of Observations in Common	9
Number of Observations in WORK.person but not in WORK.person1	1
Total Number of Observations Read from WORK.person1	9
Total Number of Observations Read from WORK.person	10
Number of Observations with Some Compared Variables Unequal	0
Number of Observations with All Compared Variables Equal:	9

Re: compare

helloSAS — Thu, 15 Aug 2013 23:04:45 GMT

@Anotherdream: you are exactly right in understanding my issue.

But, I did not get the proc compare report like you showed it. Unless i misread it. I'll confirm it again. I do remember getting a list of all the records that are unequal and my output was so huge that it printed max 50 records.

I was only expecting the records that were not existing in my other table. Is there a way I can do this, since i need it as my validation report.

Re: compare

LarryWorley — Thu, 15 Aug 2013 23:19:56 GMT

So does your output look something like the following. I created a 10 record dataset and made a copy of it. In the copy I deleted the fifth record and changed the values of the non-id variable on the last five variables.

                                                                 The COMPARE Procedure
                                                                Comparison of WORK.X with WORK.Y
                                                                         (Method=EXACT)

Data Set Summary

Dataset Created Modified NVar NObs

WORK.X 15AUG13:17:47:52 15AUG13:17:47:52 2 10
WORK.Y 15AUG13:18:10:07 15AUG13:18:10:07 2 9

Variables Summary

Number of Variables in Common: 2.
Number of ID Variables: 1.

Observation Summary

Observation Base Compare ID

                                                            First Obs           1        1 i=1
                                                            First Unequal       6        5 i=6
                                                            Last Unequal      10        9 i=10
                                                            Last Obs          10        9 i=10

                                                Number of Observations in Common: 9.
                                                Number of Observations in WORK.X but not in WORK.Y: 1.
                                                Total Number of Observations Read from WORK.X: 10.
                                                Total Number of Observations Read from WORK.Y: 9.

Number of Observations with Some Compared Variables Unequal: 5.
Number of Observations with All Compared Variables Equal: 4.

Values Comparison Summary

                                                Number of Variables Compared with All Observations Equal: 0.
                                                Number of Variables Compared with Some Observations Unequal: 1.
                                                Total Number of Values which Compare Unequal: 5.
                                                Maximum Difference: 10.

Comparison of WORK.X with WORK.Y
(Method=EXACT)

All Variables Compared have Unequal Values

Variable Type Len Ndif MaxDif

j NUM 8 5 10.000

Value Comparison Results for Variables

                                                   _________________________________________________________
                                                             ||       Base    Compare
                                                          i ||          j          j      Diff.     % Diff
                                                    _______ || _________ _________ _________ _________
                                                             ||
                                                          6 ||   120.0000   130.0000    10.0000     8.3333
                                                          7 ||   140.0000   150.0000    10.0000     7.1429
                                                          8 ||   160.0000   170.0000    10.0000     6.2500
                                                          9 ||   180.0000   190.0000    10.0000     5.5556
                                                         10 ||   200.0000   210.0000    10.0000     5.0000
                                                   _________________________________________________________

Take a look at the output dataset options from proc compare and see if there is an output datset which you could post process to make a more compact report. The raw output from proc compare can be a little overwhelming when there are many data discrepencies.

Re: compare

helloSAS — Fri, 16 Aug 2013 14:01:45 GMT

Thank you all for your input. It worked now.

My ID variable had duplicate records. Hence I was not getting desired compare output. I added another variable that makes the ID unique, that worked.