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 ....
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.