Hash Object Use Selected Key Only

[MikeX]

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

[slchen]

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;

[MikeX]

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...

[Astounding · Accepted solution]

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.

[MikeX]

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