Proc compare for 200 Datasets using macro

[vickys]

Hi experts,

 I have a requirement to compare 200+ datasets with other datasets.

 The variable are different for each dataset and i need to compare them one by one and store the results in excel file

The Results I need are 

1) No of observations between both datasets

2) no of Variables

3) any unmatched observations

I know we need to sort the datasets before comparing, I am looking for Macro which will do this as manually it takes time and not efficient.

Any idea or solution will be appreciated

Thanks,

Vicks

[andreas_lds]

Before you start to write a macro, write the code necessary to compare two datasets, including the creation of the excel-file. Then you can identify those parts that have to be dynamic easily. As last part you have to create the iteration over the datasets that have to be compared. Having the names / pairs in a dataset seems to be a good starting point for that step.

One thing will make the comparison difficult: "The variable are different for each dataset ... ." So, how do you know which variable to compare with?

The first two points of your result can be answered by querying sashelp.vtable.

[Sajid01 · Accepted solution]

Hello @vickys 
As suggested by @andreas_lds I recommend that you write the core code for comparison.
I am giving the core what I would use.
I amusing sashelp.class as the base, I have created another dataset class2 for comparison

data class2;
set sashelp.class;
if mod(_n_,3) ne 0 then do;
age=age+1;
height=height+1;
weight=weight-1;
end;
run;
proc compare base=sashelp.class compare=class2;
id name;
ods select Compare.CompareSummary;
run;

You will find the details you wanted to  in the results tab as follows

                                                       The COMPARE Procedure                                                        
                                            Comparison of SASHELP.CLASS with WORK.CLASS2                                            
                                                           (Method=EXACT)                                                           
                                                                                                                                    
                                                        Observation Summary                                                         
                                                                                                                                    
                                            Observation      Base  Compare  ID                                                      
                                                                                                                                    
                                            First Obs           1        1  Name=Alfred                                             
                                            First Unequal       1        1  Name=Alfred                                             
                                            Last  Unequal      19       19  Name=William                                            
                                            Last  Obs          19       19  Name=William                                            
                                                                                                                                    
                                  Number of Observations in Common: 19.                                                             
                                  Total Number of Observations Read from SASHELP.CLASS: 19.                                         
                                  Total Number of Observations Read from WORK.CLASS2: 19.                                           
                                                                                                                                    
                                  Number of Observations with Some Compared Variables Unequal: 13.                                  
                                  Number of Observations with All Compared Variables Equal: 6.                                      
                                                                                                                                    
                                                                                                                                    
                                                     Values Comparison Summary                                                      
                                                                                                                                    
                                  Number of Variables Compared with All Observations Equal: 1.                                      
                                  Number of Variables Compared with Some Observations Unequal: 3.                                   
                                  Total Number of Values which Compare Unequal: 39.                                                 
                                  Maximum Difference: 1.                                                                            
                                                                                                                                    
                                                                                                                                    
                                                   Variables with Unequal Values                                                    
                                                                                                                                    
                                                Variable  Type  Len  Ndif   MaxDif                                                  
                                                                                                                                    
                                                Age       NUM     8    13    1.000                                                  
                                                Height    NUM     8    13    1.000                                                  
                                                Weight    NUM     8    13    1.000 

You can output the result to an external file using ods for example to output to a csv file you could do like this

ods csv File=file_name_andpath;
/* include proc compare code here*/
ods csv close;

Please test and let us know if you have any issues.

Once you have tested this successfully, you can include the above in your macrocode.
We can discuss the macro part once you are successful up to this point.