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 ....
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.