topic cartesian merge with hash tables in SAS Procedures

cartesian merge with hash tables

deleted_user — Thu, 10 Feb 2011 15:49:12 GMT

Hi,

I have two tables which have one common column.
First table have 222874 rows and 43 columns. Second table have 4537 rows and 8 columns.
Normally I use sql to merge them, you can see below:
===========================================================
proc sql;
create table RAWDB2 as select a.*, b.linea, rskod, rsshd, rstag from lib.zH41006
a, line b where a.urkod=b.urkod;
===========================================================
But it takes too much time. How can I use hash table for this issue.

Thanks,
sinan

Re: cartesian merge with hash tables

polingjw — Thu, 10 Feb 2011 21:01:00 GMT

Take a look at this paper. It has some useful information about using duplicate keys to make SQL like joins with hash objects.

support.sas.com/rnd/base/datastep/dot/better-hashing-sas92.pdf

Re: cartesian merge with hash tables

Ksharp — Fri, 18 Feb 2011 03:19:54 GMT

Code is not test.

[pre]
data result;
declare hash find(hashexp: 16);
find.definekey('urkod');
find.definedata('linea',' rskod',......);
find.definedone();

do until(last);
set line end=last;
find.add();
end;

do until(_last);
set lib.zh41006;
rc= find.find();
if rc=0 then output;
end;
stop;
run;

Ksharp