DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

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

[ Edited ]

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

 


Accepted Solutions
Solution
‎10-26-2015 08:00 PM
Valued Guide
Posts: 860

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

Posted in reply to hammerman

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;

View solution in original post


All Replies
Super User
Posts: 5,429

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

Posted in reply to hammerman
Do a left join on Code.
Test (using Case When construct) if dataset.Code is Null Then 'N' Else 'Y'.
Data never sleeps
Frequent Contributor
Posts: 130

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

Posted in reply to hammerman

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;
Occasional Contributor
Posts: 13

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

Thanks dcruik, even your solution works :-)

Sorry, blog doesn't allow to accept two solutions :-(
Frequent Contributor
Posts: 130

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

Posted in reply to hammerman

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

Solution
‎10-26-2015 08:00 PM
Valued Guide
Posts: 860

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

Posted in reply to hammerman

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;

Occasional Contributor
Posts: 13

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

Posted in reply to Steelers_In_DC
Thanks Steelers for the solutions. It **bleep** works :-).
Valued Guide
Posts: 765

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

[ Edited ]
Posted in reply to hammerman

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;

Occasional Contributor
Posts: 13

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

[ Edited ]

Thanks Mike, even your solution works :-)

Sorry, blog doesn't allow to accept two solutions :-(

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 350 views
  • 5 likes
  • 5 in conversation