topic Re: Hash Object Use Selected Key Only in SAS Procedures

Hash Object Use Selected Key Only

MikeX — Wed, 08 Apr 2015 15:49:27 GMT

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
aaa	aa	1
aab	aa	2
aba	ab	3
ac	ac	4
ad	ad	5

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

Re: Hash Object Use Selected Key Only

slchen — Wed, 08 Apr 2015 17:05:10 GMT

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;

Re: Hash Object Use Selected Key Only

MikeX — Wed, 08 Apr 2015 17:14:51 GMT

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

Re: Hash Object Use Selected Key Only

Astounding — Wed, 08 Apr 2015 17:26:11 GMT

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.

Re: Hash Object Use Selected Key Only

MikeX — Wed, 08 Apr 2015 17:59:10 GMT

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