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;
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.
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.
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;
Yes. This indeed is much simpler and better. I like it. Thank you.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.