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.
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;
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 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;
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
;;;;
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?
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.