BookmarkSubscribeRSS Feed
Ujjawal
Quartz | Level 8

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

Check SQL operator   EXCEPT  and INTERSECT .

Tommywhosc
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 5440 views
  • 0 likes
  • 4 in conversation