topic Re: Hash Merge in Data step in SAS Procedures

Hash Merge in Data step

wcpatton — Thu, 14 Apr 2011 14:12:24 GMT

Trying to determine the syntax for converting the following (simplified) process to a hash merge for speed purposes. Thanks!

data c;
merge a(in=ina keep = loan_no) b(in=inb keep=loan_no product);
by loan_no;
if ina;
run;

Should only keep loans in A and find the product in B....

Re: Hash Merge in Data step

sbb — Thu, 14 Apr 2011 20:25:19 GMT

The SAS support http://support.sas.com/ website has several excellent technical / conference reference items on this topic.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

hash table merge site:sas.com

Re: Hash Merge in Data step

SASKiwi — Thu, 14 Apr 2011 21:19:50 GMT

Please note that a hash merge is not the only way to improve performance. A lookup SAS format created from your product table can also be a lot faster and simpler to code.The number of discrete loan numbers are you joining on should influence your chosen lookup technique. Be aware there are memory limitations with both hash tables and lookup formats.

Re: Hash Merge in Data step

Ksharp — Fri, 15 Apr 2011 05:04:30 GMT

[pre]
data a(drop=rc);
declare hash hh(hashexp:10);
hh.definekey('loan_no');
hh.definedata('product');
hh.definedone();

do until(last);
set b( keep=loan_no product) end=last;
then hh.add();
end;

do until(_last);
set a(keep = loan_no) end=_last;
call missing(product);
rc=hh.find();
output;
end;
stop;
run;
[/pre]

Ksharp Message was edited by: Ksharp