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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.