I suspect you can speed things up reading the small dataset (HAVE) twice, and the large dataset (EXTRAINFO) once. The strategy is to store only the needed subset of extrainfo data (x1,x2, and x3 just for the HAVE-matched) in memory-resident hash objects. Then the second reading of have retrieves data from these hash objects.
I.e.:
- read have and create a hash object of its CUSTID values. Call it HAVE_CUSTID
- read EXTRAINFO, and if the custid is found in HAVE_CUSTID, then
- Add its X1 value to hash object HX!, keyed on custid and month_h (using MONTH from EXTRAINFO as MONTH_H in the object).
- Add its X2 value to hash object HX2, keyd on custid and month.
- If month=2012 then add X3 to hash object HX3, keyed on custid.
- reread HAVE and retrieve X1 X2 and X3 from the relevant hash objects.
I assume that the CUSTIDs in HAVE are a relatively small subset of those in EXTRAINFO. If not, there may be performance benefits in using the HASHEXP option in the hash declaration statements. You could experiment with hashexp values greater than the default 8. See DECLARE Statement: Hash and Hash Iterator Objects and Run Time Effect Of Hash Object HASHEXP Argument Size
More importantly, this also assumes that the observations in EXTRAINFO are unique for each CUSTID/MONTH value pair.
Data Have;
format date date9.;
Input CustID month date :date9. W1 W2 W3 month_H;
cards;
1 2101 15JAN2021 10 20 30 2011
1 2101 16JAN2021 11 12 13 2011
1 2102 13FEB2021 15 17 19 2012
2 2102 15FEB2021 18 16 14 2011
2 2103 03MAR2021 13 15 12 2101
3 2101 19JAN2021 11 18 15 2102
4 2103 17MAR2021 13 12 11 2101
4 2104 18APR2021 19 18 13 2102
Run;
Data ExtraInfo;
Input CustID month X1 X2 X3;
cards;
1 2011 30 40 50
1 2012 35 45 55
1 2101 25 20 30
1 2102 15 20 40
1 2103 30 23 43
1 2104 19 21 82
2 2011 12 15 17
2 2012 32 54 28
2 2101 23 21 98
2 2102 43 54 12
2 2103 12 43 12
2 2104 43 23 25
3 2011 23 42 98
3 2012 87 67 34
3 2101 34 42 21
3 2102 98 23 65
3 2103 23 24 26
3 2104 43 64 48
4 2011 43 54 38
4 2012 32 54 27
4 2101 23 65 28
4 2102 43 26 87
4 2103 54 87 29
4 2104 67 45 53
;
Run;
data want (rename=(x1=x1_h x2=x2_c x3=x3_2012));
if _n_=1 then do;
/* The DATASET option makes this the first reading of HAVE*/
declare hash have_custid (dataset:'have (keep=custid)');
have_custid.definekey('custid');
have_custid.definedone();
declare hash hx1 ();
hx1.definekey('custid','month_h');
hx1.definedata('x1');
hx1.definedone();
declare hash hx2 ();
hx2.definekey('custid','month');
hx2.definedata('x2');
hx2.definedone();
declare hash hx3 ();
hx3.definekey('custid');
hx3.definedata('x3');
hx3.definedone();
/*Now read EXTRAINFO and populate HX1, HX2, and HX3 */
do until (end_of_extra);
set extrainfo end=end_of_extra;
if have_custid.check()^=0 then continue; /*Skip this iteration of the loop*/
hx1.add(key:custid,key:month,data:x1);
hx2.add();
if month=2012 then hx3.add();
end;
end;
set have; /*This is the second reading of HAVE*/
call missing(x1,x2,x3);
hx1.find();
hx2.find();
hx3.find();
run;
