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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.