topic Re: matching variable from another file in SAS Programming

matching variable from another file

fengyuwuzu — Tue, 19 Apr 2016 22:40:27 GMT

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;

Re: matching variable from another file

ballardw — Tue, 19 Apr 2016 22:49:26 GMT

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.

Re: matching variable from another file

ChrisNZ — Wed, 20 Apr 2016 01:26:15 GMT

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;

Re: matching variable from another file

fengyuwuzu — Wed, 20 Apr 2016 01:28:04 GMT

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

Re: matching variable from another file

fengyuwuzu — Wed, 20 Apr 2016 01:30:19 GMT

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