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!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4 replies
  • 1030 views
  • 0 likes
  • 3 in conversation