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

I have two data sets, setA and setB. Both have ID column. I want to add one more column in setA, called "In_setB", that when the ID is found in set B, In_setB = "Yes"; when there is no such a match, In_setB = "No".

 

The only way I can do it, is using merge and IN options, like codes below. It works, but I wonder if others have simpler solutions. Thank you.

 

data setA;
input ID $ amount;
datalines;
001 150
002 200
003 100
004 160
005 180
006 220
;
run;

data setB;
input ID $ amount;
datalines;
002 230
004 180
005 200
007 190
009 210
;
run;

/* I want to have want like this:
ID Amount In_setB
001 150 No
002 200	Yes
003 100	No	
004 160 Yes
005 180 Yes
006 220 No
*/

data want;
merge setA(IN=A) setB(IN=B);
by ID;
If A and B then In_setB = "Yes"; 
if A and not B then In_setB = "No"; 
If not A and B then delete;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

If this were my data I would do:

data want;
merge setA(IN=A) setB(IN=B);
by ID;
If A ;
In_SetB = B;
run;

I have a custom format that will display 1 as Yes and 0 as No when needed.

 

I prefer the numeric coding for Yes/No because I can dump the variable into procedures like Tabulate, Report or Means. The N= number of records, mean = percent yes, sum=number yes.

View solution in original post

4 REPLIES 4
ballardw
Super User

If this were my data I would do:

data want;
merge setA(IN=A) setB(IN=B);
by ID;
If A ;
In_SetB = B;
run;

I have a custom format that will display 1 as Yes and 0 as No when needed.

 

I prefer the numeric coding for Yes/No because I can dump the variable into procedures like Tabulate, Report or Means. The N= number of records, mean = percent yes, sum=number yes.

ChrisNZ
Tourmaline | Level 20

If you want to avoid sorting the tables:

 


data want;
set setA;
if _N_=1 then do;
  dcl hash B (dataset:'setB');
  B.definekey('ID');
  B.definedone();
end;
In_setB =^B.check();
run;
fengyuwuzu
Pyrite | Level 9

Yes. This indeed is much simpler and better. I like it. Thank you.

fengyuwuzu
Pyrite | Level 9
Thank you, Chris! I am not familiar with definekey, but I will take a look and learn about it. Thanks!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

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

Browse our catalog!

Discussion stats
  • 4 replies
  • 1948 views
  • 0 likes
  • 3 in conversation