topic Re: Compare two variables list in SAS Programming

Compare two variables list

Ujjawal — Tue, 21 Jul 2015 13:49:28 GMT

Hi Experts,

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

File1:

Variable

File2

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

Re: Compare two variables list

RW9 — Tue, 21 Jul 2015 13:57:37 GMT

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;

Re: Compare two variables list

Ksharp — Tue, 21 Jul 2015 14:47:56 GMT

Check SQL operator EXCEPT and INTERSECT .

Re: Compare two variables list

Tommywhosc — Wed, 22 Jul 2015 20:39:34 GMT

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.