topic Re: How to search if a value in dataset1 is in dataset 2 in SAS Programming

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

hammerman — Mon, 26 Oct 2015 05:37:52 GMT

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

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

LinusH — Mon, 26 Oct 2015 06:08:10 GMT

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

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

dcruik — Mon, 26 Oct 2015 13:19:25 GMT

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;

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

Steelers_In_DC — Mon, 26 Oct 2015 13:29:28 GMT

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;

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

MikeZdeb — Mon, 26 Oct 2015 16:42:28 GMT

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;

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

hammerman — Mon, 26 Oct 2015 23:59:49 GMT

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

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

hammerman — Tue, 27 Oct 2015 00:02:43 GMT

Thanks Mike, even your solution works 🙂

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

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

hammerman — Tue, 27 Oct 2015 00:08:02 GMT

Thanks dcruik, even your solution works 🙂

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

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

dcruik — Tue, 27 Oct 2015 12:54:36 GMT

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