topic Creating Hash tables in SAS Procedures

Creating Hash tables

Hima — Fri, 24 Feb 2012 15:24:29 GMT

I am trying to create a hash table for the below criteria. Please help.

Table_A
ID Name Fee_Amt
1 a 0
2 b 200
3 c 0
4 d 400
5 e 500

Table_B
ID City
1 Test1
2 Test2
3 Test3
4 Test4
5 Test5

Result
ID Name City      Fee_Amt      Fee
1      a      Test1    0                No
2      b      Test2   200             Yes
3      c      Test3    0                No
4      d      Test4   400             Yes
5      e      Test5   500             Yes

Usually, in PROC SQL the below code works good but it takes hours to run if the observations are in millions. I happened to know that Hash tables are effective in this case. If some can help me creating a hash table for left join, it would be a great help. Thank you.

Proc SQL Code:
PROC SQL;
CREATE TABLE RESULT AS
SELECT A.*, B.CITY, CASE WHEN (A.ID = B.ID AND A.FEE_AMT > 0) THEN 'YES' ELSE 'NO' END AS FEE
FROM TABLE_A LEFT JOIN TABLE_B
ON A.ID = B.ID;
QUIT;

Creating Hash tables

Haikuo — Fri, 24 Feb 2012 15:52:47 GMT

Hi Hima,

I am new to hash as well, so just to start discussion. Assume that your table_b is smaller that you want to put it into hash(), also you want a merge(inner join), not a left join showing in your SQL code.

data Table_A;

input ID Name $ Fee_Amt;

cards;

1 a 0

2 b 200

3 c 0

4 d 400

5 e 500

6 t 100

;

data Table_B;

input ID City $;

cards;

1 Test1

2 Test2

3 Test3

4 Test4

5 Test5

;

data want;

if 0 then set table_b;

if _n_=1 then do;

declare hash hh(dataset: 'table_b');

hh.definekey ('id');

hh.definedata('city');

hh.definedone();

end;

set table_a;

fee=ifc(fee_amt=0,'NO','YES');

_n_=hh.find();

if _n_=0 then output;

run;

proc print;run;

Regards,

Haikuo

Creating Hash tables

Hima — Fri, 24 Feb 2012 16:03:00 GMT

Thank you. This is exactly what I need. Great Expertise!!!!

Re: Creating Hash tables

MikeZdeb — Fri, 24 Feb 2012 18:50:00 GMT

hi ... you can also do some reading (my experience with these is that I think that this order works best) ...

Getting Started with the DATA Step Hash Object

http://support.sas.com/rnd/base/datastep/dot/hash-getting-started.pdf

Getting Started with the DATA Step Hash Iterator

http://support.sas.com/rnd/base/datastep/dot/iterator-getting-started.pdf

How Do I Love Hash Tables? Let Me Count The Ways!

http://www2.sas.com/proceedings/forum2008/029-2008.pdf

Better Hashing in SAS 9.2

http://support.sas.com/resources/papers/sgf2008/hashing92.pdf

Hash Crash and Beyond

http://www2.sas.com/proceedings/forum2008/037-2008.pdf

and this is nice to have around ...

Hash Object Tip Sheet

http://support.sas.com/rnd/base/datastep/dot/hash-tip-sheet.pdf

Creating Hash tables

sassharp — Tue, 13 Mar 2012 19:00:04 GMT

can anybody give example and code for how to incorporate an option 'NODUPKEY' in hash sort as in proc sort with nodupkey?

thanks.

Creating Hash tables

Ksharp — Wed, 14 Mar 2012 04:23:19 GMT

By default, Hash Table is NODUPKEY, it keeps the first one of duplicated key.