...
> Of course, it might be better to use the newer
> technology available since SAS9 introduced
> associative arrays - otherwise knows as hash
> tables. In fact this would make a good example to be
> incorporated in the on-line doc
...
I don't think hash helps since the difficulty here is to lookup a date in a table with only the starting dates of new rates. Your format approach works great because we can make a format with input *ranges*.
If we first "expand" the rate table so that the rate is given for each day (from the earliest date to today), then things become really simple. We can put this "expanded" rates table into hash and do lookups, but a simple merge will do nicely as well.
[pre]
/* test data */
data rates;
input date yymmdd8. rate;
cards;
20100701 2.50
20090801 2.25
20090302 2.00
;
run;
data loans;
input id date yymmdd8.;
cards;
1 20090626
2 20100708
;
run;
/* prep datasets. you don^t have to sort loans if you use hash to look up */
proc sort data=rates nodupkey;
by date;
run;
proc sort data=loans;
by date;
run;
/* expand rates table so that we can cover everyday
from min(date) to today. a single rate per day only. */
data daily;
format date yymmdd10.;
merge rates(rename=(date=start))
rates(firstobs=2 keep=date rename=(date=nextStart));
/* by omitted intentionally */
finish = ifn(missing(nextStart), today(), nextStart-1);
do date = start to finish;
output;
end;
keep date rate;
run;
/* attach rates to loans. simple merge. */
data loansr;
merge loans(in=inLoans) daily;
by date;
if inLoans;
run;
/* check */
proc print data=loansr noobs;
run;
/* on lst
id date rate
1 2009-06-26 2.0
2 2010-07-08 2.5
*/
[/pre]
If you have an SAS/ETS licensed, then you can take advantage of PROC EXPAND to create the dataset daily (you have to append today's date first, then use BEGINNING and METHOD=STEP options in your CONVERT statement.)
For the look-ups, simple merge is fine as above. If you insist using hash, then here you go:
[pre]
data hash;
if 0 then set daily; /* to prep pdv */
if _n_=1 then do;
dcl hash h(dataset:'dailyRates');
h.defineKey('date');
h.defineData('rate');
h.defineDone();
end;
set loans;
h.find();
run;
/* check */
proc compare base=loansr compare=hash;
run;
/* on lst -- in part
NOTE: No unequal values were found. All values compared are exactly equal.
*/
[/pre]