Cumulative sum/subtraction dependent on flag and account number

[manonlyn]

I have a data set that looks like the below:

data WORK.TEST;
infile datalines dsd truncover;
input TransAmount:BEST12. Credit_Debit:$1. TRA_RAC_ACCNO:BEST12. ABA_BALANCE:BEST12. NEW_BAL:32.;
format TransAmount BEST12. TRA_RAC_ACCNO BEST12. ABA_BALANCE BEST12.;
label TransAmount="TransAmount" Credit_Debit="Credit-Debit" TRA_RAC_ACCNO="TRA_RAC_ACCN" ABA_BALANCE="ABA_BALANCE";
datalines;
110 C 500000 130.34 -6128.74
200 C 500006 502.78 -6328.74
83.07 C 500009 -1079.28 -6411.81
113.49 C 500026 112.63 -6525.3
39.74 C 500041 179.2 -6565.04
39.74 C 500041 179.2 -6604.78
80 C 500051 -959.93 -6684.78
35 C 500061 -210.58 -6719.78
57.83 C 500071 57.85 -6777.61
260.9 C 500080 319.52 -7038.51
6.82 C 500113 6.82 -7045.33
0.62 D 500132 -103.9 -7149.23
267.69 C 500161 203.7 -7416.92
98.96 C 500165 98.57 -7515.88
500 C 500175 -310.66 -8015.88
;;;;

The New_Bal is being worked out wrong. If it's the first tra_rac_accno and the credit_debit is C then the New_Bal = ABA_BALANCE-TRANSAMOUNT

If the credit_debit is D then New_Bal = ABA_BALANCE+TRANSAMOUNT

For any other instance of tra_rac_accno then I want to use the previous New_Bal. So that it is a rolling balance with every transaction.

I tried the below:

DATA TEST;
SET test;
IF FIRST.TRA_RAC_ACCNO THEN NEW_BAL = ABA_BALANCE;
IF CREDIT_DEBIT = 'C' THEN NEW_BAL=(NEW_BAL-TRANSAMOUNT);
ELSE IF CREDIT_DEBIT = 'D' THEN NEW_BAL + ABA_BALANCE;
RUN;

This is my first time posting so please tell me if you need anymore information or if something isn't clear. 

[Kurt_Bremser]

So in the first observation of a TRA_RAC_ACCNO group new_bal will always be ABA_BALANCE-TRANSAMOUNT, and in any subsequent observations it should be kept from the first.

See this:

data have;
infile datalines truncover;
input TransAmount Credit_Debit:$1. TRA_RAC_ACCNO $ ABA_BALANCE NEW_BAL;
label TransAmount="TransAmount" Credit_Debit="Credit-Debit" TRA_RAC_ACCNO="TRA_RAC_ACCN" ABA_BALANCE="ABA_BALANCE";
datalines;
110 C 500000 130.34 -6128.74
200 C 500006 502.78 -6328.74
83.07 C 500009 -1079.28 -6411.81
113.49 C 500026 112.63 -6525.3
39.74 C 500041 179.2 -6565.04
39.74 C 500041 179.2 -6604.78
80 C 500051 -959.93 -6684.78
35 C 500061 -210.58 -6719.78
57.83 C 500071 57.85 -6777.61
260.9 C 500080 319.52 -7038.51
6.82 C 500113 6.82 -7045.33
0.62 D 500132 -103.9 -7149.23
267.69 C 500161 203.7 -7416.92
98.96 C 500165 98.57 -7515.88
500 C 500175 -310.66 -8015.88
;

data want;
set have;
by TRA_RAC_ACCNO;
retain _new_bal;
if first.TRA_RAC_ACCNO
then do;
  new_bal = ABA_BALANCE - TRANSAMOUNT;
  _new_bal = new_bal;
end;
else new_bal = _new_bal;
drop _new_bal;
run;

[manonlyn]

Thanks for the answer, it doesn't address the Credit/Debit issue though. So I want it to take away the transamount if it's a credit and add on the transamount if it's a debit. Are you able to change it so that is taken into account?

[Kurt_Bremser]

---

@manonlyn wrote:
Thanks for the answer, it doesn't address the Credit/Debit issue though. So I want it to take away the transamount if it's a credit and add on the transamount if it's a debit. Are you able to change it so that is taken into account?

---

That's not what you wrote. Quote from your original post:

If it's the first tra_rac_accno and the credit_debit is C then the New_Bal = ABA_BALANCE-TRANSAMOUNT

If the credit_debit is D then New_Bal = ABA_BALANCE-TRANSAMOUNT

Note that the formula is the same in both cases.

So the code needs a very simple extension:

data want;
set have;
by TRA_RAC_ACCNO;
retain _new_bal;
if first.TRA_RAC_ACCNO
then do;
  if credit_debit = 'C'
  then new_bal = ABA_BALANCE - TRANSAMOUNT;
  else new_bal = ABA_BALANCE + TRANSAMOUNT;
  _new_bal = new_bal;
end;
else new_bal = _new_bal;
drop _new_bal;
run;

[manonlyn]

Sorry about that, it was a typo. Thanks for your reply. The only issue I'm having now is that if there is an Account with multiple transactions the New_Bal is repeated rather than worked out again. 

 If you can see below where TRA_RAC_ACCNO = '500041' there are two transactions but the New_Bal is the same for both?

data WORK.TEST;
infile datalines dsd truncover;
input TransAmount:BEST12. Credit_Debit:$1. TRA_RAC_ACCNO:BEST12. ABA_BALANCE:BEST12. new_bal:32.;
format TransAmount BEST12. TRA_RAC_ACCNO BEST12. ABA_BALANCE BEST12.;
label TransAmount="TransAmount" Credit_Debit="Credit-Debit" TRA_RAC_ACCNO="TRA_RAC_ACCN" ABA_BALANCE="ABA_BALANCE";
datalines;
110 C 500000 130.34 20.34
200 C 500006 502.78 302.78
83.07 C 500009 -1079.28 -1162.35
113.49 C 500026 112.63 -0.86
39.74 C 500041 179.2 139.46
39.74 C 500041 179.2 139.46
80 C 500051 -959.93 -1039.93
35 C 500061 -210.58 -245.58
57.83 C 500071 57.85 0.02
260.9 C 500080 319.52 58.62
6.82 C 500113 6.82 0
0.62 D 500132 -103.9 -103.28
267.69 C 500161 203.7 -63.99
98.96 C 500165 98.57 -0.39
500 C 500175 -310.66 -810.66
;;;;

[Kurt_Bremser]

Once again, quote from your OP:

If it's the second tra_rac_accno then I want to use the New_Bal from the first.

Works as specified?

[manonlyn]

Sorry these are language issues as English is my second language. After discussing with a colleague what I meant was I want it to be a rolling balance, so using the one from before.

[Kurt_Bremser · Accepted solution]

That makes it even easier:

data want;
set have;
by TRA_RAC_ACCNO;
retain new_bal;
if first.TRA_RAC_ACCNO then new_bal = aba_balance;
new_bal + ifn(credit_debit = 'C',-TRANSAMOUNT,TRANSAMOUNT);
run;