DATA Step, Macro, Functions and more

matching variable from another file

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

matching variable from another file

[ Edited ]

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;

 


Accepted Solutions
Solution
‎04-19-2016 09:26 PM
Super User
Posts: 10,550

Re: matching variable from another file

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


All Replies
Solution
‎04-19-2016 09:26 PM
Super User
Posts: 10,550

Re: matching variable from another file

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.

PROC Star
Posts: 1,570

Re: matching variable from another file

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;
Super Contributor
Posts: 312

Re: matching variable from another file

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

Super Contributor
Posts: 312

Re: matching variable from another file

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 242 views
  • 0 likes
  • 3 in conversation