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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.