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
- /
- Calculating Balance Run Down

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2017 01:45 PM

I have a dataset that contains multiple rows per account. The number of rows = the number of months since an account has been open, with the first row being zero.

The table shows how the balance has changed each row (month). I would like to calculation what the expected balance change should have been had the customer kept to their agreed schedule of payments.

For the first row (where AgeInMonths = 0), the ExpectedBalance should always be equal to the InitialLoanAmount.

For subsequent rows, I need to work out what the ExpectedBalance was for the row before and apply an Interest & Repayment calculation to that value to come up with that months ExpectedBalance.

I need to continue doing this until i get to a new AccountNumber where I should start again.

Does anyone know how I would do this?

Thanks

Jamie

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

Posted in reply to Jamie_H

02-28-2017 04:40 PM

Making some reasonable guesses about what your variable names are and what formulas should be applied:

data want;

set have;

by AccountNumber;

retain Expected_Balance;

if first.AccountNumber then Expected_Balance = InitialLoanAmount;

else do;

Expected_Change = Expected_Balance * (1 + Interest) - Repayment;

Expected_Balance = Expected_Balance + Expected_Change;

end;

run;

This program is obviously untested, and assumes that your data set is sorted in order by AccountNumber AgeinMonths.