BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Madams1128
Fluorite | Level 6

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Madams1128
Fluorite | Level 6

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

8 REPLIES 8
Reeza
Super User

So what's the question here?

Madams1128
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6
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
Super User

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
Fluorite | Level 6

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
Super User

Post your code and log. 

 

Madams1128
Fluorite | Level 6

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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