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.
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;
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;
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?
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 ....
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.