Creating a number of variables after reading a value in the Observation(dynamically)

[thepushkarsingh]

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

APP	n	amount	rate1	rate2
1	84	10000	0.09	0.095
2	60	5000	0.1	0.12
3	12	1000	0.13	0.135
4	24	5000	0.12	0.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.)

[Reeza]

Use two arrrays.

[thepushkarsingh]

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_

[Reeza · Accepted solution]

Don't use macros, use arrays.

https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

[thepushkarsingh]

Thank you very much. 🙂