Hi,
Iam looking for check two tables and check all the columns and produce the reuslt
Example :
@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.
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
@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...
@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.
@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.
thank you so much
It worked
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")
super ! Working fine
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.