topic Re: Comparing datasets and returning identical observations in SAS Programming

Comparing datasets and returning identical observations

serge68 — Mon, 22 Aug 2016 15:48:48 GMT

I'm looking to compare two datasets that have just two variables each(Name & Num). Rather than return a listing of the differences between the datasets, what I'm after is a listing of identical obervations in the two datasets.

DATA ONE;                  
INPUT NAME NUM $ ;         
CARDS;                     
FRED   1234                
WILMA  4567                
BARNEY 8369                
BAMBAM 7894                
;                          
RUN;                       
                           
DATA TWO;                  
INPUT NAME NUM $ ;         
CARDS;                     
FRED   1234                
WILMA  8369                
BETTY  7894                
;                        
RUN;                                 
                                     
PROC SORT DATA=ONE NODUPKEY;         
BY NAME NUM;                         
RUN;                                 
                                     
PROC SORT DATA=TWO NODUPKEY;         
BY NAME NUM;                         
RUN;                                 
                                     
DATA FINAL;                          
PROC COMPARE BASE=ONE COMPARE=TWO;   
RUN;                                 
OUTPUT:

I can't seem to find anything that will allow me to report on identical oberservations.

Any suggestions?

Thanks

Re: Comparing datasets and returning identical observations

Haikuo — Mon, 22 Aug 2016 16:01:49 GMT

Try DUPOUT= option in Proc Sort,

data _stack;
set one two;
run;

proc sort data=_stack dupout=_wanted_dup nodupkey;
by _all_;
run;

Re: Comparing datasets and returning identical observations

ballardw — Mon, 22 Aug 2016 16:06:32 GMT

First you'll likely get better results if Name is character.

Try this;

DATA ONE;                  
INPUT NAME $ NUM $ ;         
CARDS;                     
FRED   1234                
WILMA  4567                
BARNEY 8369                
BAMBAM 7894                
;                          
RUN;                       
                           
DATA TWO;                  
INPUT NAME $ NUM $ ;         
CARDS;                     
FRED   1234                
WILMA  8369                
BETTY  7894                
;                        
RUN;                                 
                                     
PROC SORT DATA=ONE NODUPKEY;         
BY NAME NUM;                         
RUN;                                 
                                     
PROC SORT DATA=TWO NODUPKEY;         
BY NAME NUM;                         
RUN;    

data three;
   merge one (in=inone)
         two (in=intwo);
   by name num;
   if inone and intwo;
run;