turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Help with do loop

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-18-2017 11:39 PM

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!

Accepted Solutions

Solution

04-19-2017
02:57 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JT99

04-19-2017 12:55 AM

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;
```

PG

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JT99

04-18-2017 11:46 PM

Post the array code you tried.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

04-19-2017 12:40 AM

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

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

04-19-2017 01:24 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JT99

04-19-2017 12:53 AM - edited 04-19-2017 12:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

04-19-2017 01:24 AM

novinosrin wrote:

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

04-19-2017 01:45 AM

@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

Solution

04-19-2017
02:57 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JT99

04-19-2017 12:55 AM

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;
```

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

04-19-2017 02:05 AM

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 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JT99

04-19-2017 09:21 AM

You should take a look at PROC LOAN.