Calculating Rolling Balance

[Madams1128]

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

[Reeza]

So what's the question here?

[Madams1128]

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.

[Reeza]

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.

[Madams1128]

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

[Reeza]

Add a condition then. 

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.

[Madams1128]

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.

[Reeza]

Post your code and log. 

[Madams1128 · Accepted solution]

This worked

Data add_data;

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;

PROC PRINT DATA=ADD_DATA;

RUN;

PROC SORT DATA=ADD_DATA OUT=SADD_DATA;

BY ACCT TRAN_ORDER;

RUN;

DATA NEW_BAL;

SET SADD_DATA;

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;