So it's not always the last of the month. In this case, our lookup table best covers all days.
data table1;
input rank_no crsp_fundno mgr_dt :yymmdd10.;
format mgr_dt yymmddd10.;
cards;
1 53 1991-01-01
1 53 1999-01-01
2 105 1985-01-01
2 105 1985-06-01
;
run;
data table2;
input rank_no crsp_fundno caldt :yymmdd10. mret;
format caldt yymmddd10.;
cards;
1 53 1990-11-30 1.0459
1 53 1990-12-31 1.0250
1 53 1991-01-31 1.0435
1 53 1991-02-28 1.0499
1 53 1991-03-28 1.0078
1 53 1999-01-29 0.9810
1 53 1999-02-26 1.0002
1 53 1999-03-31 1.0238
2 105 1984-12-31 1.0238
2 105 1985-01-31 1.0238
2 105 1985-02-28 1.0238
2 105 1985-03-29 1.0238
2 105 1985-04-30 1.0238
2 105 1985-05-31 1.0238
2 105 1985-06-28 1.0238
2 105 1985-07-31 0.9990
;
run;
%let maxdate=%sysfunc(date());
data t1_int (keep=rank_no crsp_fundno oldmgrdt caldt rename=(oldmgrdt=mgr_dt));
set table1 (keep=rank_no crsp_fundno mgr_dt);
by rank_no crsp_fundno;
format oldmgrdt caldt yymmddd10.;
oldmgrdt = intnx('month',lag(mgr_dt),0,'end');
if not first.crsp_fundno
then do;
do caldt = oldmgrdt to mgr_dt - 1;
output;
end;
end;
if last.crsp_fundno
then do;
oldmgrdt = mgr_dt;
do caldt = mgr_dt to &maxdate;
output;
end;
end;
run;
data table3;
merge
t1_int (in=a)
table2 (in=b)
;
by rank_no crsp_fundno caldt;
if b;
run;
proc print data=table3 noobs;
run;
And note, once again, how to provide test data so that your possible helpers do not have to waste time typing those steps themselves. Its not rocket science and just a matter of basic forum courtesy.
This is the result:
crsp_
rank_no fundno caldt mret mgr_dt
1 53 1990-11-30 1.0459 .
1 53 1990-12-31 1.0250 .
1 53 1991-01-31 1.0435 1991-01-31
1 53 1991-02-28 1.0499 1991-01-31
1 53 1991-03-28 1.0078 1991-01-31
1 53 1999-01-29 0.9810 1999-01-01
1 53 1999-02-26 1.0002 1999-01-01
1 53 1999-03-31 1.0238 1999-01-01
2 105 1984-12-31 1.0238 .
2 105 1985-01-31 1.0238 1985-01-31
2 105 1985-02-28 1.0238 1985-01-31
2 105 1985-03-29 1.0238 1985-01-31
2 105 1985-04-30 1.0238 1985-01-31
2 105 1985-05-31 1.0238 1985-01-31
2 105 1985-06-28 1.0238 1985-06-01
2 105 1985-07-31 0.9990 1985-06-01
... View more