Hi All,
I'm a relatively inexperienced SAS user and this is my first post. Thanks in advance for any help!
I've inherited a huge code at the company that uses hash objects to merge tables due to runtime concerns. The problem here boils down to I'm trying to update the code such that for some tables that are loaded into memory with multiple keys, do a find on one key, and if not found, use another key. I've created an example below:
data basedata;
input key1 $ key2 $ value;
datalines;
aaa aa 0
aab aa 0
aba ab 0
ac ac 0
ad ad 0
;
run;
data hashdata;
input key1 $ key2 $ value;
datalines;
aaa aa 1
aab aa 2
aba ab 3
aca ac 4
ada ad 5
;
run;
data results;
set basedata;
if _n_=1 then do;
declare hash hs(dataset:'hashdata');
hs.definekey('key1','key2')
hs.definedata('value');
hs.definedone();
end;
rc=hs.find();
run;
This will give the result of
key1 | key2 | value | rc |
---|---|---|---|
aaa | aa | 1 | 0 |
aab | aa | 2 | 0 |
aba | ab | 3 | 0 |
ac | ac | 0 | -2147450842 |
ad | ad | 0 | -2147450842 |
Given that in basedata, when key1=key2, key2 will have no duplicates in either basedata or hashdata, how should I code this such that if key1/key2 combination not found, then use key2 only?
(edit) I'm trying to get the result of:
key1 | key2 | value | rc |
---|---|---|---|
aaa | aa | 1 | 0 |
aab | aa | 2 | 0 |
aba | ab | 3 | 0 |
ac | ac | 4 | 0 |
ad | ad | 5 | 0 |
I know how to achieve this by changing the code altogether using data step or proc SQL, but I'm trying to avoid doing that because it will mean changing a lot of code completely..
Regards,
Michael Xu
Making a couple of assumptions first ...
Does the VALUE you look up actually contain a set of variables rather than just one?
Is your first search based on KEY1 only (not on the combination of KEY1 and KEY2)?
If both assumptions are true, here is a strategy you could try.
Create two hash tables. The first looks up VALUE based on KEY1 only. The second looks up KEY1 based on KEY2. If VALUE really represents a set of variables, the second hash table will take up significantly less memory.
Then perform the lookup based on KEY1. If no match is found, look up a new value for KEY1 based on KEY2. Then repeat the search for VALUE based on the new KEY1.
Good luck.
If I understand your question correctly, you could define other hash table, do like this:
data results;
set basedata;
if _n_=1 then do;
declare hash hs1(dataset:'hashdata');
hs1.definekey('key1','key2');
hs1.definedata('value');
hs1.definedone();
declare hash hs2(dataset:'hashdata');
hs2.definekey('key2');
hs2.definedata('value');
hs2.definedone();
end;
if rc=hs1.find()=0 then output;
else do;
if hs2.find()=0 then output;
end;
run;
Hi,
You are understanding me right. But the problem with doing this is I will have many instances where I run out of memory to load tables...
Making a couple of assumptions first ...
Does the VALUE you look up actually contain a set of variables rather than just one?
Is your first search based on KEY1 only (not on the combination of KEY1 and KEY2)?
If both assumptions are true, here is a strategy you could try.
Create two hash tables. The first looks up VALUE based on KEY1 only. The second looks up KEY1 based on KEY2. If VALUE really represents a set of variables, the second hash table will take up significantly less memory.
Then perform the lookup based on KEY1. If no match is found, look up a new value for KEY1 based on KEY2. Then repeat the search for VALUE based on the new KEY1.
Good luck.
Hi Astounding,
That makes perfect sense! I kept getting stuck in the mindset that my second load of the same table will involve loading all the data again, which will exceed limits..
I just tested and everything checks out
Thank you,
Michael
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.