topic Re: Proc Compare in SAS Programming

Proc Compare

GPatel — Wed, 23 Aug 2017 02:19:03 GMT

I am comparing two datasets, say A and B.

My SAS code is :

proc compare base=N_402612(Keep=receipt ) compare=N_389132(Keep=receipt ) out=check ;
run;

LOG:

23         GOPTIONS ACCESSIBLE;
24         	
24       !  proc compare base=N_402612(Keep=receipt ) compare=N_389132(Keep=receipt ) out=check  ;
25         
26         	run;

NOTE: There were 402612 observations read from the data set WORK.N_402612.
NOTE: There were 389132 observations read from the data set WORK.N_389132.
NOTE: The data set WORK.CHECK has 389132 observations and 3 variables.

Results:

                                                                                                                                    
                                                                                                                                    
Observation Summary                                                                                                                 
                                                                                                                                    
Observation      Base  Compare                                                                                                      
                                                                                                                                    
First Obs           1        1                                                                                                      
First Unequal       9        9                                                                                                      
Last  Unequal  389132   389132                                                                                                      
Last  Match    389132   389132                                                                                                      
Last  Obs      402612        .                                                                                                      
                                                                                                                                    
Number of Observations in Common: 389132.                                                                                           
Number of Observations in WORK.N_402612 but not in WORK.N_389132: 13480.                                                            
Total Number of Observations Read from WORK.N_402612: 402612.                                                                       
Total Number of Observations Read from WORK.N_389132: 389132.                                                                       
                                                                                                                                    
Number of Observations with Some Compared Variables Unequal: 389124.                                                                
Number of Observations with All Compared Variables Equal: 8.                                                                        
                                                                                                                                    
                                                                                                                                    
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: 389124.                                                                               
                                                                                                                                    
                                                                                                                                    
All Variables Compared have Unequal Values                                                                                          
                                                                                                                                    
Variable  Type  Len   Label          Ndif   MaxDif                                                                                  
                                                                                                                                    
Receipt   CHAR   13   Recipt Number389124

In the output dataset Check, I want to keep only n=13480 records in my output dataset check.

Number of Observations in WORK.N_402612 but not in WORK.N_389132: 13480.

Re: Proc Compare

Reeza — Wed, 23 Aug 2017 04:21:42 GMT

An SQL query to do that is likely easier than trying to get the numbers out of PROC COMPARE. Otherwise look at the OUTDIFF data set.

Re: Proc Compare

Reeza — Wed, 23 Aug 2017 04:22:44 GMT

Also, note that value is the difference, it did a line by line comparison when I suspect you're trying to check for a specific variable. You need to add that option to proc compare, I think it's a VAR statement or ID statement.

Re: Proc Compare

ChrisNZ — Wed, 23 Aug 2017 04:33:34 GMT

You want to only keep 10,000 records? Which ones?

How does proc compare know?

As @Reeza said, you probably need a key (an ID statement in proc compare, or another join like a proc sql) to give some sense to your comparison.

Re: Proc Compare

GPatel — Wed, 23 Aug 2017 10:52:05 GMT

Thanks for your insight and suggested solution. Proc SQL and Data Merge doesn't give me correct/exact counts. I need to

create a SAS dataset of n=13,480 records as outlined by Proc Compare results.

Number of Observations in WORK.N_402612 but not in WORK.N_389132: 13480.

I hope somebody will shed some lights and offers desired solution.

Re: Proc Compare

Reeza — Wed, 23 Aug 2017 14:43:49 GMT

Your PROC COMPARE is wrong, so expecting that number in the first place is wrong.

Re: Proc Compare

ChrisNZ — Wed, 23 Aug 2017 23:00:06 GMT

Let's try another way.

What don't you just look at the first 13,480 records? or the the last 13,480 records?

Why not? How do *you* know these are not the ones you are after?

Re: Proc Compare

Ron_MacroMaven — Wed, 23 Aug 2017 23:16:40 GMT

these papers describes macros that may have hints to what you want to do.

http://www.sascommunity.org/wiki/Macro_ComparWS_Compare_with_summary

http://www.sascommunity.org/wiki/Macro_Extract

Ron Fehd comparatively, a maven

Re: Proc Compare

GPatel — Wed, 23 Aug 2017 23:21:01 GMT

I did try it. No luck.
Is there any way to isolate only n=13,480 records?

Re: Proc Compare

Reeza — Thu, 24 Aug 2017 01:28:29 GMT

Of course, but you need to define how, what are the rules that define what to be extracted. You haven't commented on the fact that the PROC COMPARE is wrong and that you don't know the difference. You don't know how many are in one and not the other, because you can have duplicates. If each data set has no duplicate records then you may be ok.

If you have no duplicates in either data set this approach may work:

proc sort data=have1; by ID_VAR;
proc sort data=have2; by ID_VAR;

data both /*includes records from both datasets*/
        in1 /*records only in have1*/
       in2 /*records only in have2*/;
merge have1 (in=a)
           have2 (in=b);

by ID_VAR;

if a and b then output both;
if a and not b then output in1;
if b and not a then output in2;

run;

Re: Proc Compare

Quentin — Thu, 24 Aug 2017 01:55:25 GMT

Interesting. There is a LISTBASEOBS options which will list the records in the base dataset only, but I don't see an OUTBASEOBS option that would output those records. So I'm going to go out on a limb and say no, PROC COMPARE can't do it.

But do note the point made above by many folks. Right now you are doing a one-to-one sequential comparison (first record of each dataset is compared, then second record of each, etc.) When you compare N_402612 to N_389132, the 13,480 records reported as being in N_402612 are simply the last 13,480 records from N_402612 .

You are doing the equivalent of:

proc compare base=sashelp.class compare=sashelp.class(obs=10) ;
run;

Which will report a difference of 9 records.

If you really want the last 13,480 records from N_402612 you can get them like:

data want;
  set N_402612(firstobs=%eval(402612-13479));
run;

Re: Proc Compare

ChrisNZ — Thu, 24 Aug 2017 03:19:56 GMT

Or maybe you just want:

proc sql;
select * from TAB1 
except 
select * from TAB2;
quit;

from the paucity of your explanations, it is hard to guess what you want.

Re: Proc Compare

GPatel — Fri, 25 Aug 2017 10:33:56 GMT

Well, very good responses recieved from Reeza, ChrisNZ, Ron_Fehd_macro_maven, and Quentin.

Each of them shed very intereting insights and comments.

It was found that my dataset A has n=xxxx,xxxx number of UNIQUE records not observed in dataset B. Similarly, dataset B has n=xxx,xxx number of Unique records not observed in dataset A.

So, I learned that Proc Compare should not be employed, but data step or SQL is a best solution. Case closed.

Thanks to all who have responded.

Girish Patel

Re: Proc Compare

Reeza — Fri, 25 Aug 2017 15:09:33 GMT

@GPatel wrote:

So, I learned that Proc Compare should not be employed, but data step or SQL is a best solution. Case closed.

That's not correct. You can use PROC COMPARE, but you were not using it correctly. You misinterpreted the results because you didn't have a BY or ID or VAR/WITH statements.