topic Re: Compare two datasets and find the non matching records in SAS Programming

Compare two datasets and find the non matching records

David_Billa — Tue, 26 Nov 2019 07:20:23 GMT

I would like to compare two datasets and find the non matching records. In below example, both the datasets has same Observation and I believe only order of the records should be differ in both the datasets. When I run the following code, it says 4096 records are differ in both the datasets.

I would like to know whether proc compare will consider the order of the record also to be identical and I wanted to the best method to compare two datasets irrespective of order of the record. If there is a difference, I want to see the records which differs.

proc compare base=test compare=test1;
run;

%put &sysinfo.;

Log:

26         proc compare base=test compare=test1;
27         run;

NOTE: There were 5985917 observations read from the data set WORK.TEST.
NOTE: There were 5985917 observations read from the data set WORK.TEST1.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
NOTE: PROCEDURE COMPARE used (Total process time):
      real time           28.39 seconds
      cpu time            27.54 seconds
      

28         
29         %put &sysinfo.;
SYMBOLGEN:  Macro variable SYSINFO resolves to 4096

Re: Compare two datasets and find the non matching records

PeterClemmensen — Tue, 26 Nov 2019 07:28:33 GMT

Yes, PROC COMPARE considers observation order. Sort your two data sets by the same variable and compare again. In my book, PROC COMPARE is still the best tool for the job.

See this small example

data a;
input var1 var2;
datalines;
1 2
3 4
;

data b;
input var1 var2;
datalines;
3 4
1 2
;

proc compare base=a comp=b;
run;

Re: Compare two datasets and find the non matching records

David_Billa — Tue, 26 Nov 2019 08:14:18 GMT

OK, how and where can I difference records in the output?

Re: Compare two datasets and find the non matching records

HIRO2020 — Tue, 26 Nov 2019 08:56:06 GMT

How about the following

data a;
input ID var1 var2;
datalines;
001 1 2
002 2 2
003 1 2
004 1 2
006 1 3
;
run;

data b;
input ID var1 var2;
datalines;
001 1 2
002 1 2
003 1 2
004 1 3
007 2 2
;
run;

proc compare base=a comp=b noprint OUTNOEQUAL OUT=COMP ;
by ID;
run;

Re: Compare two datasets and find the non matching records

David_Billa — Tue, 26 Nov 2019 09:52:42 GMT

I'm unable to understand the data in the Output dataset COMP.

Re: Compare two datasets and find the non matching records

Kurt_Bremser — Tue, 26 Nov 2019 10:32:45 GMT

A description of the OUT= dataset is found here.