So you have three tables, not two. There are two very small (B and C) and one large (A). If this is the case, then DON'T convert table A into a hash. Instead
Read B and C into independent hashes
Create a third hash (BC) as the cartesian product of the first 2. It will still be a small object.
Then read A one obs at a time. Each time iterate through BC and output one obs per iteration.
The resulting data will be in the physical order of table A, but within each A, there is no guarantee of order.
Using this simulated data, with the szies you mention for tables A, B, and C:
data table_a;
do numbers=1 to 110000; output; end;
run;
data table_b (keep=letters);
length letters $2 ;
do L1=rank('A') to rank('Z') until (nout=140);
letters=byte(L1);
do L2=rank('A') to rank('Z') until (nout=140);
substr(Letters,2)=byte(L2);
output;
nout+1;
end;
end;
run;
data table_c;
do other=-207 to -1; output; end;
run;
data template_b_c;
set table_b table_c;
stop;
run;
The program below creates a 3-way cartesian product and took 2:19 (139 seconds) on my machine. But notice it is a DATA _NULL_ step, so the output statement does not actually write to disk. All I am measuring is the time it takes to read in B, C, and A, and cross all their observations. The rest will depend on the speed of your output device and output channel. Whatever total time the program would take to create the dataset you want, will be almost solely driven by the time to write the data, not the time to do the cross-products.
data _null_ (drop=_:);
set table_a;
if _n_ =1 then do;
if 0 then set table_b;
declare hash b (dataset:'table_b');
b.definekey('letters');
b.definedata(all:'Y');
b.definedone();
declare hiter bi ('b');
if 0 then set table_c;
declare hash c (dataset:'table_c');
c.definekey('other');
c.definedata(all:'Y');
c.definedone();
declare hiter ci ('c');
declare hash bc (dataset:'template_b_c');
bc.definekey(all:'Y');
bc.definedata(all:'Y');
bc.definedone();
declare hiter bci ('bc');
do _rb=bi.first() by 0 until (bi.next()^=0);
do _rc=ci.first() by 0 until (ci.next()^=0);
bc.add();
end;
end;
end;
do _rc=bci.first() by 0 until (bci.next()^=0);
output;
end;
run;
... View more