DATA Step, Macro, Functions and more

datastep: if observation occurs in another dataset then do certain things

Accepted Solution Solved
Reply
Contributor fre
Contributor
Posts: 33
Accepted Solution

datastep: if observation occurs in another dataset then do certain things

 

Dear Community,

 

I have 2 datasets:  dataset A and dataset B.

 

Dataset A contains several observations with unique identifier KEY, STATUS, name, address, etc...

 

Dataset B contains only 1 variable KEY.

 

What I would like to do:  for each observation in A, I want to check if the value for KEY occurs in dataset B, and if so, STATUS in dataset A has to change. (let's say status = 'Z')

 

I can manage that with SQL, by which I first create a new table based on a join of the 2 tables.

 

But I would like to know if there is a way to manage this easily in a datastep.

I would expect something like this:

 

data table_A_out;

set table_A_in;

 

if KEY in ( table_B.KEY) then STATUS = 'Z';

 

run;

 

But it doesn't work that way...

Any advise would be appreciated.

 

 

 

 


Accepted Solutions
Solution
‎05-13-2016 06:00 AM
Super User
Posts: 9,682

Re: datastep: if observation occurs in another dataset then do certain things

Hash Table

 

data IFILE ;
input  STATUS $       SEND_CNTY $;
cards;
T123                    EH
T456                    ZM 
T678                     US
T444                     ZW
;
run;
data CNTY_LOOKUP ;
input  CNTY_CODE2 $;
cards;
 EH
  YE
 ZM
 ZW
;
run;
data want;
 if _n_=1 then do;
  if 0 then set CNTY_LOOKUP;
  declare hash h(dataset:'CNTY_LOOKUP');
  h.definekey('CNTY_CODE2');
  h.definedone();
 end;
set IFILE;
if h.check(key:SEND_CNTY)=0 then STATUS ='Z';

run;

View solution in original post


All Replies
Solution
‎05-13-2016 06:00 AM
Super User
Posts: 9,682

Re: datastep: if observation occurs in another dataset then do certain things

Hash Table

 

data IFILE ;
input  STATUS $       SEND_CNTY $;
cards;
T123                    EH
T456                    ZM 
T678                     US
T444                     ZW
;
run;
data CNTY_LOOKUP ;
input  CNTY_CODE2 $;
cards;
 EH
  YE
 ZM
 ZW
;
run;
data want;
 if _n_=1 then do;
  if 0 then set CNTY_LOOKUP;
  declare hash h(dataset:'CNTY_LOOKUP');
  h.definekey('CNTY_CODE2');
  h.definedone();
 end;
set IFILE;
if h.check(key:SEND_CNTY)=0 then STATUS ='Z';

run;
Contributor fre
Contributor
Posts: 33

Re: datastep: if observation occurs in another dataset then do certain things

Thank you for this very compact code.  It is what I was looking for.

 

One thing I don't understand:  the second if:  

if 0 then ...

 

If what exactly is zero?  And what does it means in this context?

Super User
Posts: 9,682

Re: datastep: if observation occurs in another dataset then do certain things

It is creating(initiate) variables in PDV  which are in CNTY_LOOKUP table. 

Another way is  hard code Like :

 

length CNTY_CODE2  $ 8 

 

instead of 

if 0 then set ....

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 206 views
  • 2 likes
  • 2 in conversation