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
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;
So what's the question here?
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.
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.
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.
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.
Post your code and log.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.