Help using Base SAS procedures

Hash Object Use Selected Key Only

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Hash Object Use Selected Key Only

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

key1key2valuerc
aaaaa10
aabaa20
abaab30
acac0-2147450842
adad0-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:

key1key2valuerc
aaaaa10
aabaa20
abaab30
acac40
adad50

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


Accepted Solutions
Solution
‎04-08-2015 01:26 PM
Super User
Posts: 5,077

Re: Hash Object Use Selected Key Only

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.

View solution in original post


All Replies
Super Contributor
Posts: 275

Re: Hash Object Use Selected Key Only

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;

New Contributor
Posts: 3

Re: Hash Object Use Selected Key Only

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

Solution
‎04-08-2015 01:26 PM
Super User
Posts: 5,077

Re: Hash Object Use Selected Key Only

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.

New Contributor
Posts: 3

Re: Hash Object Use Selected Key Only

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 Smiley Happy

Thank you,

Michael

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 239 views
  • 3 likes
  • 3 in conversation