07-21-2015 09:49 AM
I have 2 datasets - file1 and file2. I want to compare the text appeared in variable.
I want the macro to return "C and F not found" based on the data shown above. If no mismatch found, it should return "No mismatch".
07-21-2015 09:57 AM
Its a good idea to put your test data as a datastep. Form that I haven't tested this, but something like:
create table FALLOUT as
select distinct COALESCE(A.VAR,B.VAR) as VAR
from HAVE A
full join HAVE2 B
on A.VAR != B.VAR;
07-22-2015 04:39 PM
You said you wanted a macro, so here's my go at it....
/* create the 2 tables: */
data one; length VAR $ 1;
input VAR @@;
A B C D E F G H I
data two; length VAR $ 1;
input VAR @@;
A B C D E F G H
/* create a table, ONE_ONLY, that has obs where VAR values are in ONE, but not TWO */
proc sql noprint;
create table ONE_ONLY as
ON one.VAR = two.VAR
WHERE one.VAR ^= two.VAR ;
/* Now, put those values of VAR into a macro VAR1 */
select distinct VAR into :var1 separated by ' '
from work.ONE_ONLY ;
/* test to see if VAR1 exists. If so, then write the values that mis-matched. */
/* if not, then var1 not created, so no mis-matches */
%let MACs = %symexist(var1);
%if &MACs = 0 %then
%put NO MISMATCH;
%put These Values are in ONE, but not TWO: &var1 ;
1) the code only looks for VAR values in ONE that are not in TWO. You can edit to make another table, TWO_ONLY, where VAR values are in TWO, but not ONE. (and even a table BOTH, where values are matched).
2) Make sure you don't have a macro variable &VAR1 already created. If you run this code multiple times to test it (changing the inputs in TWO), you should probably do a %symdel var1 ; to remove/delete the macro before running PROC SQL.
Hope this helps.