How to search if a value in dataset1 is in dataset 2

[hammerman]

I am new bee to SAS programming, in my task I have to search for values in dataset1, with dataset2 , if it exist then set a flag as Y 0r N.

Dataset1 contains

Emp_id | Code

12345      ING

12344      UMG

54312      YGA  

Dataset2 contains
Code

IMG

UMG
UMM

XRC

If code in dataset1 is in dataset2 then

Dataset1 or new dataset as

Emp_id | Code | Flag

12345     ING       Y

12344     UMG     Y

54312     YGA      N

Hope my explanation is clear

Thanks

Varun

[LinusH]

Do a left join on Code.
Test (using Case When construct) if dataset.Code is Null Then 'N' Else 'Y'.

[dcruik]

You can approach this in two ways:

1).  You can do a left join with dataset1 and dataset2 on the variable Code and create a Y/N flag if the Code matches or not.

2).  You can create a macro variable list of Code from dataset2 and do a data step checking Code from dataset1 to see what's in the macro variable list from dataset2 and create a Y/N flag.

The two examples are below, hope this helps!

Example 1:

proc sql;
create table want as
select data1.Emp_ID,
	   data1.Code,
	   IFC(data2.Code="","N","Y","N") as Code_Flag
from dataset1 as data1 left join dataset2 as data2
where (data1.Code=data2.Code);
quit;

Example 2:

proc sql noprint;
select cats('"',Code,'"') into:Code separated by ","
from dataset2;
quit;

data want;
set dataset1;
If Code in (&Code) then Code_Flag="Y";
	Else Code_Flag="N";
run;

[hammerman]

Thanks dcruik, even your solution works 🙂

Sorry, blog doesn't allow to accept two solutions 😞

[dcruik]

No worries.  As long as you got the help you were looking for, that's all that matters.  Glad we could help you at.

[Steelers_In_DC · Accepted solution]

Here is a solution:

data one;
input emp_id$ code$;
cards;
12345      ING
12344      UMG
54312      YGA
;

data two;
input code$;
cards;
ING
UMG
UMM
XRC
;

data want;
merge one(in=a)
      two(in=b);
by code;
if a;
if a and b then Flag = 'Y';
else flag = 'N';
run;

[hammerman]

Thanks Steelers for the solutions. It **bleep** works :-).

[MikeZdeb]

Hi, another way(s) ...

data one;
input emp_id code :$3. @@;
datalines;
12345 IMG 12344 UMG 54312 YGA 
;
 
data two;
input code :$3. @@;
datalines;
IMG UMG UMM XRC
;

proc datasets lib=work nolist;
modify two;
index create code;
quit;

data one_plus;
set one;
set two key=code / unique;
flag = ifc(^_error_, 'Y','N');
_error_=0;
run;

or without adding the index to data set two ...

data one_plus;
dcl hash h(dataset: 'two');
h.definekey('code');
h.definedone();
do until (last);
   set one end=last;
   flag = ifc(^h.find(), 'Y','N');
   output;
end;
stop;
run;

[hammerman]

Thanks Mike, even your solution works 🙂

Sorry, blog doesn't allow to accept two solutions 😞