topic Re: Help with do loop in SAS Programming

Help with do loop

JT99 — Wed, 19 Apr 2017 03:39:37 GMT

Hello!

I have a code for a 24-month installment plan. I need to get the principal, balance, interest and amortization of each account for each month. Amort is fixed in all 24 months.

/*code for 1st month*/

data data1;set Datanew;

where amort_period =24;

Month1=1;

Amort1=mort(bal_0,.,.2241502/12,24);

Interest1= ((bal_0)*(.2241502)/12);

Prin1= Amort1-Interest1;

Bal1=bal_0-Prin1;

run;

/*code for 2nd month */

data data2;set data1;

Month2=2;

Amort2 =amort1;

Interest2= ((bal1)*(.2241502)/12);

Prin2= Amort2-Interest2;

Bal2=bal1-Prin2;

run;

Can you guys help me simplify the code so I won't have to make a seperate code for each month? I tried using array but I get errors. I'm also not sure if array is applicable in this problem.

I will greatly appreciate any help or advice.

Thanks!

Re: Help with do loop

Reeza — Wed, 19 Apr 2017 03:46:16 GMT

Post the array code you tried.

Re: Help with do loop

art297 — Wed, 19 Apr 2017 04:40:22 GMT

It is definitely a do-loop-driven array problem but, I agree with @Reeza, post the code that you tried.

Art, CEO, AnalystFinder.com

Re: Help with do loop

novinosrin — Wed, 19 Apr 2017 04:54:25 GMT

It would help if you could post your sample source data and the output sample data that you want. Forgive me, personally, I prefer to think through the solution on looking at data samples rather than code. Thank you

Re: Help with do loop

PGStats — Wed, 19 Apr 2017 04:55:09 GMT

I guess you could build from this

%let n=24;

data amort;
amount = 1000;
rate = 0.2241502;
payment = mort(amount, ., rate/12, &n);
array balance{0:&n} balance0-balance&n;  
array capital{0:&n} capital0-capital&n;
array interest{&n}; 
balance0 = amount;
capital0 = 0;
do month = 1 to &n;
    interest{month} = rate * balance{month-1} / 12;
    capital{month} = capital{month-1} + payment - interest{month};
    balance{month} = amount - capital{month};
    end;
drop month;
run;

Re: Help with do loop

ArtC — Wed, 19 Apr 2017 05:24:17 GMT

If you do not need the arrays, the DO loop can do the stepping through the payment cycle.

data amortization;
int_rate=.2241502; * annual interest rate;
balance=100000;    * loan amount;
amort_period =24;  * number of monthly payments;
* Calculate hte monthly payment (AMORT);
Amort=mort(balance,.,int_rate/12,amort_period);
do month = 0 to amort_period;
   Interest= ((balance)*(int_rate/12));
   Principal= Amort-Interest;
   output amortization;
   Balance = balance-principal;
end;
run;

Re: Help with do loop

Reeza — Wed, 19 Apr 2017 05:24:38 GMT

@novinosrin wrote:

It would help if you could post your sample source data and the output sample data that you want. Forgive me, personally, I prefer to think through the solution on looking at data samples rather than code. Thank you

Code is because I don't feel like doing her homework. If she posts what she tried I'm happy to help, but an attempt is nice. And this is definitely homework since there are three or four such questions in the last two days from multiple users. Searching on here would produce an answer as well.

I mostly agree, it's easier to get logic from data. But I have an actuarial degree so mortgage/loan tables are like second nature to me 🙂

Re: Help with do loop

novinosrin — Wed, 19 Apr 2017 05:45:08 GMT

@Reeza I totally agree, an OP's effort is needed and makes it more of a true discussion. Perhaps, i should have added "include data samples too along with your effort". Your rightly pointed

Re: Help with do loop

JT99 — Wed, 19 Apr 2017 06:05:33 GMT

Thank you all for the replies! You guys have been very helpful.

This is what my data looks like:

CARDNUMBER	AMORT_PERIOD	PRINCIPAL_AMT	EffRate	Bal0
296	24	430431.69	0.2241502	430431.69
356	24	53766.79	0.2241502	53766.79
976	24	75818.01	0.2241502	75818.01
610	24	13308.06	0.2241502	13308.06
966	6	34327.47	0	34327.47

So basically, I have a list of accounts that are enrolled in 24,18,15,12,9,6,3 months installment payment plan. I have decided to make a code for each payment plan since I don't know how to make one code for all of them.

For the 24-month payment plan, I want the result to look like this:

the result includes all principal,interest,balance for the whole 24 mos.

CARDNUMBER	AMORT_PERIOD	PRINCIPAL_AMT	EffRate	Bal0	Month1	Amort1	Interest1	Prin1	Bal1	Month2	Amort2	Interest2	Prin2	Bal2
296	24	430431.69	0.2241502	430431.69	1	22418.31636	8040.11245	14378.20391	416053.4861	2	22418.31636	7771.539343	14646.77702	401406.7091
356	24	53766.79	0.2241502	53766.79	1	2800.353543	1004.31973	1796.033815	51970.75618	2	2800.353543	970.7712827	1829.58226	50141.17392

Re: Help with do loop

Reeza — Wed, 19 Apr 2017 13:21:32 GMT

You should take a look at PROC LOAN.