I have a lookup table to be used for multiple datasets.
How can I use hash table method to perform subset and output for each of them in the same step?
I have experimented the following, but clearly it is not working.
data outA outB; if 0 then set lookup(keep=ppn); if _n_ = 1 then do; declare hash hash(dataset: 'lookup(keep=ppn)');
hash.defineKey('ppn'); hash.defineDone(); end; do until(last); set tableA end = last; if ~hash.find() then output outA; end; do until(last); set tableB end = last; if ~hash.find() then output outB; end; run;
The main thing is to reset variable LAST before the 2nd do-while loop.
I would use the check() method because you don't need to read the value from the hash table into the PDV. You just want to check for existence.
I've never seen the ~ used in front of the hash reference. It appears to work but can you explain to me what's that for?
Here your code amended - inclusive a few cosmetic changes.
data tableA tableB lookup;
set sashelp.class;
ppn=name;
output tableA;
output tableB;
if _n_<4 then
output lookup;
run;
data outA outB;
if 0 then set lookup(keep=ppn);
declare hash h1(dataset: 'lookup(keep=ppn)');
h1.defineKey('ppn');
h1.defineDone();
do until(last);
set tableA end = last;
if h1.check()=0 then output outA;
end;
last=0;
do until(last);
set tableB end = last;
if h1.check()=0 then output outB;
end;
stop;
run;
The main thing is to reset variable LAST before the 2nd do-while loop.
I would use the check() method because you don't need to read the value from the hash table into the PDV. You just want to check for existence.
I've never seen the ~ used in front of the hash reference. It appears to work but can you explain to me what's that for?
Here your code amended - inclusive a few cosmetic changes.
data tableA tableB lookup;
set sashelp.class;
ppn=name;
output tableA;
output tableB;
if _n_<4 then
output lookup;
run;
data outA outB;
if 0 then set lookup(keep=ppn);
declare hash h1(dataset: 'lookup(keep=ppn)');
h1.defineKey('ppn');
h1.defineDone();
do until(last);
set tableA end = last;
if h1.check()=0 then output outA;
end;
last=0;
do until(last);
set tableB end = last;
if h1.check()=0 then output outB;
end;
stop;
run;
Thanks Patrick.
"~hash.check" is the same as "hash.check = 0".
This is what I learnt when someone taught me hash table method for the first time. Then got used to it.
Your solution partially worked. But both data were merged.
Is there anyway I can prevent them from merging?
What do you mean by merging?
It's certainly not merging rows but it will add all the variables from the source tables to the PDV and though to the target tables. You can use a data set KEEP option to prevent this from happening.
data outA(keep=<variable list>) outB(keep=<variable list>);
You are absolutely right. Thank you.
@Patrick wrote:
I've never seen the ~ used in front of the hash reference. It appears to work but can you explain to me what's that for?
It's just one of the (operating system dependent) symbols for the logical operator NOT, by no means specific to hash object syntax. (Strangely enough, the linked documentation seems to contain a typo: ° instead of ^ as it's correctly listed in WHERE syntax.)
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.