Compare two variables list

[Ujjawal]

Hi Experts,

I have 2 datasets - file1 and file2. I want to compare the text appeared in variable.

File1:

Variable

A

B

C

D

E

F

G

File2

Variable

A

B

D

E

G

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".

[RW9]

Its a good idea to put your test data as a datastep.  Form that I haven't tested this, but something like:

proc sql;

     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;

quit;

[Ksharp]

Check SQL operator   EXCEPT  and INTERSECT .

[Tommywhosc]

You said you wanted a macro, so here's my go at it....

/* create the 2 tables: */

data one; length VAR $ 1;
input VAR  @@;
cards;
A B C D E F G H I
run;
data two; length VAR $ 1;
input VAR @@;
cards;
A B C D E F G H
run;

/* 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
select *
from ONE
LEFT JOIN
TWO
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 ;
quit;

/* 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                           */
%macro test;
%let MACs = %symexist(var1);
%if &MACs = 0 %then
%put NO MISMATCH;
%else
%put These Values are in ONE, but not TWO: &var1 ;
%mend test;

%test 

Some NOTES:

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.