Proc loan + estimatedcase - add values from a dataset

[chris2377]

Say I have an adjustable rate loan of 1000 granted in 2015 with interest rate being adjusted annually. I want to obtain repayment schedule for this loan given known past rates and find the outstanding value of the loan at the end of 2020:

data rates;
input year rate;
datalines;
2015 1.5
2016 2
2017 3
2019 2.5
2019 4
2020 5
;
run;

The proc loan, together with the ESTIMATEDCASE option seems to be doing what I want, but can I read the values to be placed in the ESTIMATEDCASE from a dataset instead of giving them manually?

proc loan start=2010:12;
arm amount=1000 rate=1.5 life=180 ESTIMATEDCASE=(12=2, 24=3, 36=2.5, 48=4, 52=5)
label='BANK3, Adjustable Rate';
run;

[AMSAS · Accepted solution]

The documentation indicates that PROC LOAN does not accept a dataset, so we can work around that using macros

Here's an example:

data rates;
	/* define temporary varible and retain */
	/* Note if your periods and rates, exceed the length you will have problems */
	length 
		tempEC $200 ;
	retain 
		tempEC ""
		start  2015 ; /* Set loan start year */
	/* Read your data */
	input year rate;
	/* Convert the years into periods (month) from the start date */
	months=(year-start)*12 ;
	/* Handle case where 1st rate is at the start of the loan */
	if months=0 then 
		months=1 ;
	/* Add seperator if more than 1 period/rate */
	if _n_ ne 1 then do ;
		tempEC=cats(tempEC,", ") ;
	end ;
	/* Build code syntax */
	tempEC=cats(tempEC,putn(months,"8."),"=",putn(rate,"8.2")) ;
	/* Capture if the length of tempEC hits the maximum (200) and retutn an ERROR */
	if length(tempEC)=200 then 
		put "ERROR: tempEC exceeeded length : " ;
		put "       " tempEC ;
	/* Create macro variable for use in PROC LOAN */
	call symput("EC",tempEC) ;
	/* Put the syntax to the log (for debugging) */
	put tempEC= ;
datalines;
2015 1.5
2016 2
2017 3
2019 2.5
2019 4
2020 5
;
run;

/* Put the macro variable to the log (for debugging) */
%put &=EC ;

proc loan start=2015:6;
	/* use the macro variable EC in the ESTIMATEDCASE option */
	arm amount=1000 rate=0 life=180 ESTIMATEDCASE=(&EC)
	label='BANK3, Adjustable Rate';
run;