DATA Step, Macro, Functions and more

Hash Join

Reply
Super Contributor
Posts: 648

Hash Join

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;

Respected Advisor
Posts: 3,124

Re: Hash Join

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

Haikuo

Super Contributor
Posts: 648

Re: Hash Join

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

Respected Advisor
Posts: 3,124

Re: Hash Join

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:

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

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

Super Contributor
Posts: 648

Re: Hash Join

Thanks Haiko.I will try that and let you know

Ask a Question
Discussion stats
  • 4 replies
  • 212 views
  • 0 likes
  • 2 in conversation