topic Re: Hash tables merging more then 2 tables in SAS Programming

Hash tables merging more then 2 tables

Matt3 — Thu, 10 Aug 2017 17:27:09 GMT

Hi,

I am new to hash tables, I know this is an efficient way to merge tables. I have found articles how to merge two or more tables in one way only left join, right join or inner in one data step, already. However, I wonder if it is possible to merge three or more tables. Two of them like using left join, third inner join by hash tables in one data step?

Thanks.

Re: Hash tables merging more then 2 tables

mkeintz — Thu, 10 Aug 2017 18:24:20 GMT

Yes, it is possible. But if the datasets have variables in common other than the join keys, you'll have to program logic accordingly. SQL is easier in this regard in that you can just specify A.myvar or B.myvar, etc.

Re: Hash tables merging more then 2 tables

Matt3 — Thu, 10 Aug 2017 18:35:28 GMT

I am working with large datasets so, I guess hash tables should be more efficient.

Re: Hash tables merging more then 2 tables

SASKiwi — Thu, 10 Aug 2017 20:54:56 GMT

Please note hash tables need to be loaded into memory so the more tables you load the less available memory you will have for each table.

You need to balance any performance gains you might get against the increased coding complexity and any memory limitations you might have.

If you are doing any data lookups in your joins (looking up just one or two columns) you might want to consider using SAS format lookups instead. They are a lot less complicated to code and are just as fast as hash tables.

Re: Hash tables merging more then 2 tables

Matt3 — Thu, 10 Aug 2017 21:14:13 GMT

I have written this code already. It seems to be fast enough,

klasa3 is left join table and klasa1 is inner join table.

data hash_test;

 if 0 then set klasa1 klasa2 klasa3;
if _N_= 1 then do;
declare hash merge(dataset:'klasa1');
	merge.definekey('name');
	merge.definedata(all:'yes');
	merge.definedone();

declare hash merge2(dataset:'klasa3');
	merge2.definekey('name');
	merge2.definedata('test');
	merge2.definedone();
end;
	
 do until(eof);
 set klasa2 end=eof;
 
rc=merge2.find();
if rc =0 then do;
	if merge.find()=0 then output;
end;
else do;
	if merge.find()=0 then do;
    call missing (test);
   	output;
end;
end;

end;
stop;
run;