topic Re: Two tables check all columns in SAS Programming

Two tables check all columns

mmkr — Tue, 05 Nov 2019 16:46:24 GMT

Hi,

Iam looking for check two tables and check all the columns and produce the reuslt

Example :

data table1;

input col1 $ col2 $ col3 $ col4 $ col5 $;

cards;

value11 value12 value13 value14 value15

value21 value22 value23 value24 value25

value31 value32 value33 value34 value35

value41 value42 value43 value44 value45

value51 value52 value53 value54 value55

;

run;

data table2;

input col1 $ col2 $ col3 $ col4 $ col5 $;

cards;

value11 value12 value13 value14 value15

value21 value22 value23 value24 value25

value31 value32 value33 value34 value35

value41 value42 value00 value44 value45

value51 value52 value53 value00 value55

;

run;

result iam expecting :

col1 col2 col3 col4 col5 result

value11 value12 value13 value14 value15 All_columns_matched

value21 value22 value23 value24 value25 All_columns_matched

value31 value32 value33 value34 value35 All_columns_matched

value41 value42 value00 value44 value45 All_columns_not_matched

value51 value52 value53 value00 value55 All_columns_not_matched

Re: Two tables check all columns

Reeza — Tue, 05 Nov 2019 17:04:34 GMT

PROC COMPARE - have you tried that yet? Does it not do what you want?

Re: Two tables check all columns

mmkr — Tue, 05 Nov 2019 17:12:05 GMT

It's doesn't giving me exact result ..it's actually showing in log result window

I tried to put out= resulttablename but it is displaying only the differences as XX Mark

Re: Two tables check all columns

hashman — Tue, 05 Nov 2019 17:13:51 GMT

@mmkr: What @Reeza said. If you just want to compare record by record, proc COMPARE is your friend, for example:

data table1 ;                                                                                                                           
  input (col1-col5) ($) ;                                                                                                               
  cards ;                                                                                                                               
value11 value12 value13 value14 value15                                                                                                 
value21 value22 value23 value24 value25                                                                                                 
value31 value32 value33 value34 value35                                                                                                 
value41 value42 value43 value44 value45                                                                                                 
value51 value52 value53 value54 value55                                                                                                 
;                                                                                                                                       
run;                                                                                                                                    
                                                                                                                                  
                                                                                                                                        
data table2 ;                                                                                                                           
  input (col1-col5) ($) ;                                                                                                               
  cards ;                                                                                                                               
value11 value12 value13 value14 value15                                                                                                 
value21 value22 value23 value24 value25                                                                                                 
value31 value32 value33 value34 value35                                                                                                 
value41 value42 value00 value44 value45                                                                                                 
value51 value52 value53 value00 value55                                                                                                 
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
proc compare noprint base=table1 comp=table2 out=diff ;                                                                                 
run ;                                                                                                                                   
                                                                                                                                        
data want (drop = _:) ;                                                                                                                 
  merge table2 diff (rename=(col1-col5=_c1-_c5)) ;                                                                                      
  if findc (catx (of _c:), "X") then result = "All columns not matched" ;                                                               
  else  result = "All columns matched" ;                                                                                                
run ;

Kind regards

Paul D.

Re: Two tables check all columns

Reeza — Tue, 05 Nov 2019 17:36:02 GMT

@mmkr wrote:

It's doesn't giving me exact result ..it's actually showing in log result window

I tried to put out= resulttablename but it is displaying only the differences as XX Mark

It's a good idea to mention things like that in your original post. In generaly, the PROC COMPARE output isn't great, but you can capture the ODS table and filter it as desired. I think @hashman solution should work, if it doesn't I suggest that approach.

Here's some instructions and explanations on how to capture output that is shown.
https://blogs.sas.com/content/sastraining/2017/03/31/capturing-output-from-any-procedure-with-an-ods-output-statement/

Re: Two tables check all columns

hashman — Tue, 05 Nov 2019 18:04:37 GMT

@mmkr: It does if you do it correctly.

But if you don't like proc COMPARE for some reason, in this particular case you can do it in a single step using just MERGE:

data table1 ;                                                                                                                           
  input (col1-col5) ($) ;                                                                                                               
  cards ;                                                                                                                               
value11 value12 value13 value14 value15                                                                                                 
value21 value22 value23 value24 value25                                                                                                 
value31 value32 value33 value34 value35                                                                                                 
value41 value42 value43 value44 value45                                                                                                 
value51 value52 value53 value54 value55                                                                                                 
;                                                                                                                                       
run;                                                                                                                                    
                                                                                                                                        
data table2 ;                                                                                                                           
  input (col1-col5) ($) ;                                                                                                               
  cards ;                                                                                                                               
value11 value12 value13 value14 value15                                                                                                 
value21 value22 value23 value24 value25                                                                                                 
value31 value32 value33 value34 value35                                                                                                 
value41 value42 value00 value44 value45                                                                                                 
value51 value52 value53 value00 value55                                                                                                 
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want (drop = _:) ;                                                                                                                 
  merge table1 table2 (rename=(col1-col5=_c1-_c5)) ;                                                                                    
  if catx (":", of col:) ne catx (":", of _c:) then result = "All columns not matched" ;                                                
  else  result = "All columns matched" ;                                                                                                
run ;

In general, it's more reliable to compare the COL and _C pairwise one by one, lest the CATX delimited should happen to be an endpoint of some value (though this snag can be circumvented). That is:

data want (drop = _:) ;                                                                                                                 
  merge table1 table2 (rename=(col1-col5=_c1-_c5)) ;                                                                                    
  array col col: ;                                                                                                                      
  array _c _c: ;                                                                                                                        
  do _i_ = 1 to dim (col) until (col ne _c) ;                                                                                           
  end ;                                                                                                                                 
  if _i_ <= dim (col) then result = "All columns not matched" ;                                                                         
  else  result = "All columns matched" ;                                                                                                
run ;

Kind regards

Paul D.

Re: Two tables check all columns

mmkr — Tue, 05 Nov 2019 18:08:20 GMT

thank you so much

It worked

Re: Two tables check all columns

mmkr — Tue, 05 Nov 2019 18:14:54 GMT

One followup question

how to write all columns instead of _C ?

Because all my actual column names are not in some unique ....

can i write some thing like _all_col ? is there any keyword ?

if findc (catx (of _c:), "X")

Re: Two tables check all columns

Reeza — Tue, 05 Nov 2019 18:17:27 GMT

_all_

Re: Two tables check all columns

mmkr — Tue, 05 Nov 2019 18:19:52 GMT

super ! Working fine