BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

The data I have is like following with thousands of rows:

APPnamountrate1rate2
184100000.090.095
26050000.10.12
31210000.130.135
42450000.120.124

 

For each row I need to calculate something like:

data one;
payment1=mort(amount, ., rate1/12,n);
int1_inc_yr1 = CUMIPMT (rate1/12, n, amount, 1, 12, 0);
bal1_endyr1 = AMOUNT - CUMPRINC (rate1/12, n, amount, 1, 12, 0);
int1_inc_yr2 = CUMIPMT (rate1/12, n, amount, 13, 24, 0);
bal1_endyr2 = bal_endyr1- CUMPRINC (rate1/12, n, amount, 13, 24, 0);

...

...

run;

...<For all the years in term, for 120 term, this will go for 12 years, this is not fixed as each application will have a different term>

...<This then I need to repeat for other Interest Rates as well, in data I have given 2 rates, it may go upto 5 rates>
the output I need is:

For each row(application), all the 'Interest Accumulated' and 'End of Year Principal Balance' in each year during it's term...

 

I tried PROC LOAN to do this but supplying each row one at a time takes too long.

Any idea on how to do this efficiently in DATA step or any other method?

 

(P.S. - I then have to use this output for further calculations in PROC IML. Each row will interact with other matrces there.)

1 ACCEPTED SOLUTION
4 REPLIES 4
thepushkarsingh
Quartz | Level 8

I tried like following with desired results but there's lots of notes and in the end a warning because of operations on missing values, (since I am selecting max term):

%macro _stupid_;

proc sql;

select max(n) into :max_term from one;

quit;
data two;
set one ;

%do j=1 %to 2;
payment&j.=mort(amount, ., rate&j./12,n);
%do i=1 %to &max_term.;
int_inc&j._yr_&i. = CUMIPMT (rate&j./12, n, amount, 1, 12, 0);
bal_begin&j._yr_&i. = amount;
%if &i.>1 %then %do;
int_inc&j._yr_&i. = CUMIPMT (rate&j./12, n, amount, %eval(%eval(&i.-1)*12+1), %eval(&i.*12), 0);
bal_begin&j._yr_&i. = bal_begin&j._yr_%eval(&i.-1)- CUMPRINC (rate&j./12, n, amount, %eval(%eval(&i.-2)*12+1), %eval(%eval(&i.-1)*12), 0);
%end;
%end;

%end;
run;
%mend;
%_stupid_

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 575 views
  • 1 like
  • 2 in conversation