topic Calculating Rolling Balance in SAS Enterprise Guide

Calculating Rolling Balance

Madams1128 — Tue, 17 Oct 2017 21:11:50 GMT

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

Re: Calculating Rolling Balance

Reeza — Tue, 17 Oct 2017 21:19:12 GMT

So what's the question here?

Re: Calculating Rolling Balance

Madams1128 — Tue, 17 Oct 2017 21:20:31 GMT

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.

Re: Calculating Rolling Balance

Reeza — Tue, 17 Oct 2017 21:22:59 GMT

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.

Re: Calculating Rolling Balance

Madams1128 — Tue, 17 Oct 2017 21:58:35 GMT

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

Re: Calculating Rolling Balance

Reeza — Tue, 17 Oct 2017 22:03:13 GMT

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.

Re: Calculating Rolling Balance

Madams1128 — Wed, 18 Oct 2017 12:17:33 GMT

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.

Re: Calculating Rolling Balance

Reeza — Wed, 18 Oct 2017 12:42:36 GMT

Post your code and log.

Re: Calculating Rolling Balance

Madams1128 — Wed, 18 Oct 2017 13:31:23 GMT

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;