topic Re: Calculating projected balance of account in ODS and Base Reporting

Calculating projected balance of account

deleted_user — Thu, 09 Sep 2010 15:37:05 GMT

Hi,

I wish to create a report that shows the projected balance of a number of bank accounts.
I know the starting balance and I know that on certain days certains amounts will be transfered to these accounts.

Here's an example of how I want the finished report to look.

Projected balance
day1 day2 day3 day4 day5 etc.
Account1 0.00 0.00 1.00 1.00 3.00
Account2 10.00 10.00 10.00 10.00 10.00
Account3 5.00 25.00 35.00 35.00 55.00
etc.

The input data sets looks something like this:
Data set 1:
Account# balance
Account1 0.00
Account2 10.00
Account3 5.00

Data set 2:
Account# balance expirationdate account_to
Account_from1 1.00 day3 Account1
Account_from2 2.00 day5 Account1
Account_from3 20.00 day1 Account3
Account_from4 10.00 day3 Account3
Account_from5 20.00 day5 Acoount3

Sorry about the formatting, I hope it makes sense. Is there an easy way to do this?

Best regards,
Thomas

Re: Calculating projected balance of account

Cynthia_sas — Thu, 09 Sep 2010 15:55:40 GMT

Hi:
I don't understand your data. For example, you have two files -- SAS will not "automatically" join the 2 files together for you. You will have to do this with an SQL join or a DATA step MERGE. It looks to me like dataset 1 is a "starting balance"??? is this correct??

Then with dataset 2 are those withdrawals (subtract from balance) or deposits (add to balance)???

Why is Account # just Account1, Account2, Account3 in dataset1, but in dataset 2, the Account# has _from???? What is the meaning of the ACCOUNT_TO variable??? Do you want a match on Account # between the 2 files or between ACCOUNT # in dataset 1 and ACCOUNT_TO in dataset 2??? I also don't understand the logic behind the _FROMn in dataset 2:
[pre]
Account# balance expirationdate account_to
Account_from1 1.00 day3 Account1
Account_from2 2.00 day5 Account1
Account_from3 20.00 day1 Account3
Account_from4 10.00 day3 Account3
Account_from5 20.00 day5 Acoount3
[/pre]

... where you have _from1, _from2, _from3, _from4 and _from5??? What is the significance of the differing numbers appended to the _from??? Does that have any impact on the type of processing that you need to do???

What code have you started with or tried??? What is the logic of "projecting" the balance. It seems to me that you are not doing any forecasting or projecting, the logic of your output seems to show a certain number of days (how many days?) and if there was no deposit for that day, you just carry the previous balance over to that day. Can you explain a little bit more about the logic of how you want the "projecting" done?? And, what are the real names of your variables -- since you can't have a '#' in a SAS variable name -- is 'Account #' the label for your Account identifier variable???

cynthia

Re: Calculating projected balance of account

polingjw — Thu, 09 Sep 2010 17:31:47 GMT

There is probably a better way of doing this, but here is the initial code that I came up with. In the final "projected balance" dataset, shouldn't the balance on day1 be $25 ($5 inital plus $20 on the first day)?

data account_start;
input account_no $ balance;
datalines;
Account1 0.00
Account2 10.00
Account3 5.00
;

data Transactions;
length AccountFrom $ 13;
input AccountFrom $ Amount DayOfTransaction $ AccountTo $;
datalines;
Account_from1 1.00 day3 Account1
Account_from2 2.00 day5 Account1
Account_from3 20.00 day1 Account3
Account_from4 10.00 day3 Account3
Account_from5 20.00 day5 Account3
;
run;

data account_initial;
set account_start;
array day{5};
do I = 1 to 5;
day{I}=balance;
end;
drop balance i;
run;

proc sort data=transactions;
by AccountTo;
run;

data transactions;
set transactions;
by AccountTo;
array DayTransaction{5};
if first.AccountTo then do;
Do I = 1 to 5;
DayTransaction{I} = 0;
end;
end;
TransactionDay=input(substr(DayOfTransaction, 4, 1),8.);
Do I = TransactionDay to 5;
if I ge TransactionDay then DayTransaction{I} + amount;
end;
if last.AccountTo then output;
keep AccountTo DayTransaction1-DayTransaction5;
run;

proc sort data=account_initial;
by account_no;

data account_final;
merge account_initial transactions(rename=(accountTo = Account_no));
by account_no;
array transaction{5} DayTransaction1-DayTransaction5;
array day{5} Day1-Day5;
do I = 1 to 5;
day{I}=sum(day{I}, transaction{I});
end;
drop daytransaction1-daytransaction5 i;
run;

proc print data=account_final noobs;
format day1-day5 8.2;
run;

Re: Calculating projected balance of account

deleted_user — Fri, 10 Sep 2010 09:52:31 GMT

Hi Cynthia,

Let me begin with the logic behind this projection. I wish to calculate the balance of a number of current accounts over a period of time (from now till the end of October) based on the current accounts present balance and the expiration of deposits. If there is no deposit for that day the balance is carried over to the next day. In this scenario there are no withdrawels. Interest is also ignored.

If it makes understanding easier, polingjw's program provides the desired results but if there is an easier solution I would love to know about. I haven't worked with arrays before but maybe it's time to start? 🙂

In my example Data set 1 contains the current accounts while Data set 2 contains the deposits. In reality all the information about both the current accounts and the deposits are stored in the same DB2 table. I just thought that it would be easier understand the way I set up my example.

The real variable names probably wouldn't mean anything to you as they're in Danish but a more accurate example of the data would be something like this:
CustomerNo RegistrationNo AccountNo AccountType Balance ExpirationDate

In my example account_to is the account the deposit is transferred to when it expires. Account_to is not actually a variable in the DB2 table but deposits are always transferred to the current account on the expiration date. Customers can only have one current account. Therefor I can easily figure out which accounts the deposits will be transferred to.

So to summarize:
Current account = account1-5 (variable names account# and account_to)
Deposit = account_from1-5 (variable name account#)

Sorry for the confusion.

I haven't actually tried anything so far other than running polingjw's program as I simply had no idea how to achieve the desired result.

Best regards,
Thomas

Re: Calculating projected balance of account

deleted_user — Fri, 10 Sep 2010 09:56:22 GMT

Hi polingjw,

This does exactly what I intended! I'm new to arrays but thankfully it doesn't look too complicated. Your help is much appreciated.

Best regards,
Thomas

Re: Calculating projected balance of account

Cynthia_sas — Fri, 10 Sep 2010 14:05:23 GMT

Hi:
It's good you have a program that produces the desired results. Now you have to understand the program well enough to change the logic for your "real" data.

A couple of observations/questions:
It seems to me that you could potentially have an array bigger than 5 days???? What is the upper limit? Are you bound by a fiscal year? Could the number of days be more than 366?? You said you wanted to project a balance between now and the end of October -- so by that statement, the implication is that your DAYOFTRANSACTION variable is probably NOT DAY1, DAY2, DAY3, etc. My guess would be that DAYOFTRANSACTION is either a date value or a character string that represents a real date.

Is DAYOFTRANSACTION a character variable or a numeric variable??? Is it a full date: 09/10/2010 or is it a julian date: 10253 or is it a RDBMS date/time stamp: 09/10/2010:01:01:01??? Is the DB2 date a character string version of the date???

The program that was posted substrings the 1, 2, 3, 4, 5, etc out of the DAYOFTRANSACTION variable to use as an index for the array and the array is hard-coded to be only 5 members. It is possible to index an array by a day or date value, but then, knowing the upper bound or potential size of the array would be important. Will there always be a one to one correspondence between the rows in account_initial and the transactions???

I probably would have taken a slightly different approach (I wouldn't have made the DAY1-DAY5 array -- I would have made the BAL1-BAL5 array and made a variable in account_initial called START. Then I just would have added START to every memeber of the BAL1-BAL5 array.

But rather than tweaking this program, I would recommend that you focus more on the logic issues involved in adapting this program to your real data -- not your made up data. Some good papers on using arrays are:
http://support.sas.com/rnd/papers/sgf07/arrays1780.pdf
http://www2.sas.com/proceedings/sugi27/p066-27.pdf
http://www2.sas.com/proceedings/sugi30/242-30.pdf

cynthia

Re: Calculating projected balance of account

deleted_user — Fri, 10 Sep 2010 15:15:14 GMT

Hi Cynthia,

It is correct that the array is bigger than 5 days. From now till the end of October there are 52 days. For every day this changes of course.

You are also right that DayOfTransaction is a date value. It is read into SAS as a SAS date value.

As far as creating the index I subtract today's date from the expiration date. This gives me the number of days till the deposit expires.

There will always be a one to one correspondance in the sense that a deposit is transfered to a specific current account when it expires. However different deposits can be transfered to the same current account.

I agree that understanding what the program does is essential and I have already looked at some of the papers. It is also nice however to have something concrete to look at 🙂

I have succesfully tweaked the program to work with the following dummy data:

data CurrentAccount;
input CurrentAccountNo Balance
datalines;
99991111111 0.00
99992222222 10.00
99993333333 5.00
;
run;

data Deposit;
input DepositAccountNo Balance ExpirationDate date9. CurrentAccountNo;
datalines;
99994444444 1.00 13SEP2010 99991111111
99995555555 2.00 15SEP2010 99991111111
99996666666 20.00 12SEP2010 99993333333
99997777777 10.00 13SEP2010 99993333333
99998888888 20.00 15SEP2010 99993333333
;
run;

This data is very similar to the actual data. However when I run the program with the actual data I get ERROR: Array subscript out of range eventhough I have changed the size of the array from 5 to 52 (or more).

But I will read the papers that you have suggested and hopefully it all makes sense then 🙂 Thanks for your help.

Best regards,
Thomas