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

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

fre — Fri, 13 May 2016 07:29:20 GMT

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.

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

Ksharp — Fri, 13 May 2016 08:00:44 GMT

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;

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

fre — Fri, 13 May 2016 08:20:39 GMT

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?

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

Ksharp — Fri, 13 May 2016 09:25:28 GMT

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 ....