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!

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
  • 4 replies
  • 911 views
  • 0 likes
  • 3 in conversation