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

If the ID from data set 1 exists in dataset 2, I would like to create a new variable called flag in dataset 2 and it =1 and =0 otherwise. This is shown below. I tried using merge, but couldn't find a good way to do this. any help would be great.

 

Data 1:

ID X1 X2  
54 9 3  
55 3 2  
56 2 0  
57 4 4  
58 4 1  
59 6 8  
60 1 7  

Data 2:

ID Y1 Y2 Y3
54 55 79 55
55 83 61 55
56 74 77 60
57 64 88 56
60 87 78 83
61 84 87 77
62 78 68 50
63 89 59 52
64 74 77 60
65 55 79 55

 

Desired data:

ID Y1 Y2 Y3 FLAG
54 55 79 55 1
55 83 61 55 1
56 74 77 60 1
57 64 88 56 1
60 87 78 83 1
61 84 87 77 0
62 78 68 50 0
63 89 59 52 0
64 74 77 60 0
65 55 79 55 0
1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

The easy way is to use SQL:

proc sql;
  create table want as 
  select *,id in(select id from data1) as flag
  from data2;
quit;

View solution in original post

4 REPLIES 4
s_lassen
Meteorite | Level 14

The easy way is to use SQL:

proc sql;
  create table want as 
  select *,id in(select id from data1) as flag
  from data2;
quit;
Kurt_Bremser
Super User

And if performance is of importance, use a data step hash:

data want;
set data2;
if _n_ = 1
then do;
  declare hash d1 (dataset:"data1");
  d1.definekey("id");
  d1.definedone();
end;
flag = (d1.check() = 0);
run;
SteveDenham
Jade | Level 19

One more way, but definitely slower:

 

data want;
merge one(in=a) two(in=b);
by id;
if a and b then flag=1;
    else flag=0;
if y1=. or y2=. or y3=. then delete;
keep id y1 y2 y3 flag;
run;

How you handle the missings after the merge is dependent on your ultimate objective.  Here I deleted any record missing at least one of y1-y3.  I could understand making this different if the criteria for keeping a record was something other than at least one of the variables in dataset 2 is missing.

 

SteveDenham

 

Ksharp
Super User

proc sql;
create table want as
select *,exists(select * from data1 where id=a.id) as flag
from data2 as a;
quit;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 7999 views
  • 5 likes
  • 5 in conversation