I used your test data for tests
Table A is ~9.2GB and B is ~38MB.
I observed the ID_2 is character and ID_2 values are "1" or "0".
If you can make ID_2 to be numeric with 1 and 0 instead "1" and "0":
data _ids;
do ID = 1 to &max_id.;
ID_2 = 1; /* <-- numeric */
output;
/* We have some ids for which id_2 is 1 and 0*/
if rand('uniform',0,1) < 0.01 then do; ID_2=0; output; end;
end;
run;
Then you could use a temporary array for look-up.
%let max_i = 5600;
%let max_id = 55000;
%let max_category = 22;
%let max_ID_2=1;
/* if ID_2 is numeric */
%let range_up = %sysevalf(&max_id.*1000 + &max_ID_2.*100 + &max_category.);
%put &=range_up.;
/* Option C: Array*/
data WANT_OPTION_B_array(keep = I SUM_X);
/* populate array */
array XX[&range_up.] _temporary_; /* instead hash table */
do until(eofB);
set table_B end=EOFB;
XX[ID*1000 + ID_2*100 + CATEGORY]=X;
end;
do until (EOFA);
set table_A end=EOFA;
by I;
if first.I then SUM_X = 0;
SUM_X + XX[ID*1000 + ID_2*100 + CATEGORY]; /* direct keys addressing */
if last.I then output;
end;
stop;
run;
With numeric ID_2 the result for the hash table code on my machine was:
NOTE: There were 1221506 observations read from the data set WORK.TABLE_B.
NOTE: There were 307875035 observations read from the data set WORK.TABLE_A.
NOTE: The data set WORK.WANT_OPTION_B has 5545 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 1:22.39
cpu time 1:22.73
and the result with Array was:
NOTE: There were 1221506 observations read from the data set WORK.TABLE_B.
NOTE: There were 307875035 observations read from the data set WORK.TABLE_A.
NOTE: The data set WORK.WANT_OPTION_B_ARRAY has 5545 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 13.81 seconds
user cpu time 12.36 seconds
system cpu time 1.65 seconds
memory 430637.46k
OS Memory 458620.00k
(printout is with Fullstimer, so you can see memory usage too)
The formula for array size:
%let range_up = %sysevalf(&max_id.*1000 + &max_ID_2.*100 + &max_category.);
is based on assumption that category has 2 digits, and ID_2 is 0 or 1.
If number of categories changes to more than 99 or ID_2 increases above 9 you will have to adjust the 1000 and 100 accordingly.
Hope it helps.
Bart
P.S. I learned this technique from @hashman
[EDIT:]
P.S.2
Linear pass through data was very fast, but only ~6 seconds faster than "array lookup":
220 data WANT_OPTION_B_read(keep = I SUM_X);
221
222
223 set test.table_A(sortedby=I ID ID_2);
224 by I;
225
226
227 SUM_X +1;
228 if last.I;
229 run;
NOTE: There were 308619066 observations read from the data set WORK.TABLE_A.
NOTE: The data set WORK.WANT_OPTION_B_READ has 5558 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 7.52 seconds
cpu time 7.56 seconds
{EDIT 2:}
P.S.3 I did additional run of the hash approach to see memory usage:
NOTE: There were 1221506 observations read from the data set WORK.TABLE_B.
NOTE: There were 307875035 observations read from the data set WORK.TABLE_A.
NOTE: The data set WORK.WANT_OPTION_B has 5545 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 1:22.18
user cpu time 1:20.67
system cpu time 1.60 seconds
memory 91185.12k
OS Memory 119216.00k
Array took ~4.5 times more RAM.
But! if ID_is only 1 or 0 then we can re-arrange lookup key calculation to:
%let range_up = %sysevalf(&max_id. + &max_ID_2.*1e7 + &max_category.*1e5);
%put &=range_up.;
/* Option C: Array*/
data WANT_OPTION_B_array(keep = I SUM_X);
/* populate array */
array XX[&range_up.] _temporary_;
do until(eofB);
set table_B end=EOFB;
XX[ID + ID_2*1e7 + CATEGORY*1e5]=X;
end;
do until (EOFA);
set table_A end=EOFA;
by I;
if first.I then SUM_X = 0;
SUM_X + XX[ID + ID_2*1e7 + CATEGORY*1e5];
if last.I then output;
end;
stop;
run;
this makes array maximal size 12255000 instead 55000122 which is ~4 times smaller and the result is even better (with array RAM size comparable to hash table RAM):
NOTE: There were 1221506 observations read from the data set WORK.TABLE_B.
NOTE: There were 307875035 observations read from the data set WORK.TABLE_A.
NOTE: The data set WORK.WANT_OPTION_B_ARRAY has 5545 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 9.78 seconds
user cpu time 8.35 seconds
system cpu time 1.53 seconds
memory 96686.93k
OS Memory 124672.00k
