BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hammerman
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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

8 REPLIES 8
LinusH
Tourmaline | Level 20
Do a left join on Code.
Test (using Case When construct) if dataset.Code is Null Then 'N' Else 'Y'.
Data never sleeps
dcruik
Lapis Lazuli | Level 10

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
Obsidian | Level 7
Thanks dcruik, even your solution works 🙂

Sorry, blog doesn't allow to accept two solutions 😞
dcruik
Lapis Lazuli | Level 10

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
Barite | Level 11

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
Obsidian | Level 7
Thanks Steelers for the solutions. It **bleep** works :-).
MikeZdeb
Rhodochrosite | Level 12

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
Obsidian | Level 7

Thanks Mike, even your solution works 🙂

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

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
  • 8 replies
  • 1252 views
  • 5 likes
  • 5 in conversation