Desktop productivity for business analysts and programmers

Calculating Rolling Balance

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Calculating Rolling Balance

I have the following data:

ACCTTRAN_ORDEREND_BAL_WITH_FEEEND_BAL_NO_FEETRANSACTIONRolling_Bal_w_FEERolling_Bal_wo_fee
x11455.271485.270  
x2  -1809.86  
x3  1000  
x4  -485  
x5  -59  
x6  50.32  
x7  -36  
x8  2300  
x9  -144.9  
x10  0.02  
x11  -13  
x12  250  
x13  -2200  
x14  -200  
y13023.243023.240  
y2  -22.75  
y3  -6  
y4  -55.5  
y5  -27.34  
y6  -17.14  
y7  -42.5  
y8  -10.47  
y9  -58.19  
y10  -15.49  
y11  -69.34  
y12  -2.08  
y13 50-371.77  
y14  -500  
y15  -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

 

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;

 

View solution in original post


All Replies
Super User
Posts: 22,850

Re: Calculating Rolling Balance

Posted in reply to Madams1128

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: 22,850

Re: Calculating Rolling Balance

Posted in reply to Madams1128

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: 22,850

Re: Calculating Rolling Balance

Posted in reply to Madams1128

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 Smiley Happy

 

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: 22,850

Re: Calculating Rolling Balance

Posted in reply to Madams1128

Post your code and log. 

 

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

Re: Calculating Rolling Balance

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 304 views
  • 0 likes
  • 2 in conversation