topic Re: Hash Join in SAS Programming

Hash Join

SASPhile — Wed, 14 Nov 2012 15:50:59 GMT

How could the following be done using hash join,there are 13 million records in each table and wanted to check if hash would reduce the processing time.

                          proc sql;
                         create table _all as
                         select a.*
                        ,b.CUST_ID_CD
                        ,b.CUST_TYPE_CD
                        ,b.CUST_ACCT_LINK_DT
                        ,b.PHONE1_TYPE_CD
                         from Amea_before a left outer join amea_after b on a.acct_no=b.acct_no
                        group by a.acct_no
                        order by a.acct_no;
                        quit;

Re: Hash Join

Haikuo — Wed, 14 Nov 2012 15:58:39 GMT

What is the "group by" for here? I don't see any summary functions? Just want to clear the fog before moving on.

Haikuo

Re: Hash Join

SASPhile — Wed, 14 Nov 2012 16:06:45 GMT

Yes, I forgot to comment that out.Thanks for pointing it out

Re: Hash Join

Haikuo — Wed, 14 Nov 2012 16:33:43 GMT

Please bear with me, here are some my 2cents along with a possible Hash() solution:

In general scenario of large datasets merging, you may have the following options:

Option1: Doing merge on their native server. If you are merging datasets which are natively from Oracle, Teradata etc, and their native datasets are still accessible, then your best bet is to use pass-through utility and have it done on their native server. This is the most optimal solution, and in most cases is much efficient than other following options.
Option2: Well, if you have no access to their native format or they just came as SAS tables, the best way to do it is SAS/SPDS:

http://www.sas.com/technologies/dw/storage/spds/index.html

I hope your pocket is deep enough for it.

Option3: If you fall short on above two options, let’s talk about Hash(). Since both of your table are large, so there is a risk that you may not be able to load the whole table into RAM, and if that happens, then you can’t use HASH(). Let’s assume that you can fit one of the table into RAM, then you can try the following code (not tested):

data _all;

if _n_=1 then do;

if 0 then set amea_after (keep=acct_no CUST_ID_CD CUST_TYPE_CD CUST_ACCT_LINK_DT PHONE1_TYPE_CD);

declare hash h(dataset:'amea_after (keep=acct_no CUST_ID_CD CUST_TYPE_CD CUST_ACCT_LINK_DT PHONE1_TYPE_CD)', multidata:'y');

h.definekey('acct_no');

h.definedata('CUST_ID_CD', 'CUST_TYPE_CD', 'CUST_ACCT_LINK_DT', 'PHONE1_TYPE_CD');

h.definedone();

end;

set Amea_before;

_rc=h.find();

do _rc=0 by 0 while (_rc=0);

output;

_rc=h.find_next();

end;

drop _rc;

run;

Haikuo

Re: Hash Join

SASPhile — Thu, 15 Nov 2012 19:10:55 GMT

Thanks Haiko.I will try that and let you know