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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.