Calculating Rolling Balance

Solved
Occasional Contributor
Posts: 5

Calculating Rolling Balance

I have the following data:

 ACCT TRAN_ORDER END_BAL_WITH_FEE END_BAL_NO_FEE TRANSACTION Rolling_Bal_w_FEE Rolling_Bal_wo_fee x 1 1455.27 1485.27 0 x 2 -1809.86 x 3 1000 x 4 -485 x 5 -59 x 6 50.32 x 7 -36 x 8 2300 x 9 -144.9 x 10 0.02 x 11 -13 x 12 250 x 13 -2200 x 14 -200 y 1 3023.24 3023.24 0 y 2 -22.75 y 3 -6 y 4 -55.5 y 5 -27.34 y 6 -17.14 y 7 -42.5 y 8 -10.47 y 9 -58.19 y 10 -15.49 y 11 -69.34 y 12 -2.08 y 13 50 -371.77 y 14 -500 y 15 -14

I have over 1600 accts in a specific transaction order (TRAN_ORDER). Each at TRAN_ORDER = 1 there is an ending balance with a fee and without a fee. I need to step down through the data to create two columns for a rolling balance with a fee and rolling balance without a fee.

So in the above example acct x I need end_bal_w_fee + Transaction = rolling balance w fee or 1455.27 + (-1809.86) = -354.59

Then it needs to step down and do -354.59 + 1000 = 645.41 and so on until it is no longer acct x

The second issue showing in acct y is that TRAN_ORDER 13 has a credit present in the end bal without fee column.

while the program is stepping through the rolling balance 1 to 12 at 13 the column for rolling balance without fee needs the prior ending balance plus the credit plus the transaction so in this case 2696.44 + 50 + (-371.77) = 2374.67

Accepted Solutions
Solution
‎10-18-2017 09:31 AM
Occasional Contributor
Posts: 5

Re: Calculating Rolling Balance

This worked

Acct = 'X';

TRAN_ORDER = 1;

end_bal = 1485.27;

trans = 0;

output;

Acct = 'X';

TRAN_ORDER = 2;

end_bal = 0.00;

trans = -1809.86;

output;

Acct = 'X';

TRAN_ORDER = 3;

end_bal = 0.00;

trans = 1000.00;

output;

Acct = 'X';

TRAN_ORDER = 4;

end_bal = 0.00;

trans = -485.00;

output;

RUN;

RUN;

BY ACCT TRAN_ORDER;

RUN;

DATA NEW_BAL;

BY ACCT;

IF FIRST.ACCT THEN DO;

NEW_BAL = END_BAL;

RETAIN NEW_BAL;

END;

ELSE DO;

NEW_BAL +TRANS;

END;

RUN;

PROC PRINT DATA=NEW_BAL;

RUN;

All Replies
Super User
Posts: 23,980

Re: Calculating Rolling Balance

So what's the question here?

Occasional Contributor
Posts: 5

Re: Calculating Rolling Balance

The question is how do i step through each line for each account and calculate a rolling balance like i described in the original post body.

Super User
Posts: 23,980

Re: Calculating Rolling Balance

You need to use RETAIN and BY group processing.

BY group allows the identification of a new account and RETAIN keeps the previous value.

``````data want;
set have;

by account;

RETAIN balance;

if first.account then balance=0;
balance = sum(balance, transaction);
run;``````

I don't think this accounts for whatever exception you're trying to include, but I don't understand that portion either.

Occasional Contributor
Posts: 5

Re: Calculating Rolling Balance

The second part is if an amount shows up in the end balance column that is not at team order 1 to add that amount into the rolling balance
Super User
Posts: 23,980

Re: Calculating Rolling Balance

if tran_order ne 1 and not missing(end_balance) then rolling_total = sum(whatever you want to add);

Note that your terminology doesn't match your sample data so it's hard to follow a bit. I'm sure it makes sense to you but to someone who's never seen your data it doesn't

If you're having trouble implementing this, feel free to post back with more details.

Occasional Contributor
Posts: 5

Re: Calculating Rolling Balance

That just repeats the values for transaction and does not sum it. This is like balancing your check book but using sas to do it.

Super User
Posts: 23,980

Re: Calculating Rolling Balance

Solution
‎10-18-2017 09:31 AM
Occasional Contributor
Posts: 5

Re: Calculating Rolling Balance

This worked

Acct = 'X';

TRAN_ORDER = 1;

end_bal = 1485.27;

trans = 0;

output;

Acct = 'X';

TRAN_ORDER = 2;

end_bal = 0.00;

trans = -1809.86;

output;

Acct = 'X';

TRAN_ORDER = 3;

end_bal = 0.00;

trans = 1000.00;

output;

Acct = 'X';

TRAN_ORDER = 4;

end_bal = 0.00;

trans = -485.00;

output;

RUN;

RUN;

BY ACCT TRAN_ORDER;

RUN;

DATA NEW_BAL;

BY ACCT;

IF FIRST.ACCT THEN DO;

NEW_BAL = END_BAL;

RETAIN NEW_BAL;

END;

ELSE DO;

NEW_BAL +TRANS;

END;

RUN;

PROC PRINT DATA=NEW_BAL;

RUN;

☑ This topic is solved.