Hi everyone,
I am new to the SAS-EG, currently I'm trying to figure out a formula to calculate the transaction balance by day with FIFO logic, and anyone can help?
Date ID Credit Debit Balance Transactional Balance
01Jan2014 123 20 10 10 5
05Jan2014 123 0 5 5 0
15Jan2014 123 30 0 35 30
01Jan2014 456 10 0 10 10
09Jan2014 555 0 10 (10) (10)
01Jan2014 789 15 5 10 0
08Jan2014 789 35 15 30 30
Are you comfortable using the SAS Program window within Enterprise Guide? This would be very difficult to do using just EG tasks.
Tom
Hi Tom
Yeap, I am okay with SAS program window, but is only familiar to some of the codes.
Here's the code to get your balance.
What is the rule for calculating the Transactional Balance?
Tom
data have;
input Date date9. ID Credit Debit;
format Date date.;
cards;
01Jan2014 123 20 10
05Jan2014 123 0 5
15Jan2014 123 30 0
01Jan2014 456 10 0
09Jan2014 555 0 10
01Jan2014 789 15 5
08Jan2014 789 35 15
run;
proc sort data=have;
by ID Date;
run;
data want;
set have;
by ID;
retain Balance;
if first.ID then
Balance = 0;
Balance = Balance + Credit - Debit;
run;
Good morning Tom
Thanks for the code, i have my calculation on balance column, but I am still figuring out the logic of "transactional balance":smileyconfused:.
I try to apply the FIFO rule on "Transactional Balance" column, which the 1st debit amount have to minus off from the 1st credit amount (if any).
If the 1st debit amount is greather than 1st credit amount, the balance will deduct from 2nd credit transaction...
Could you walk through how you calculate it in your example, in some detail? I must admit, I can't follow it.
Tom
Good morning Tom
Sorry for my poor explanation.
What I am trying to do is forecasting the expiry credit balance. The transactional balance will only exist at all the credit transaction.
Assuming,
I need the 1st credit transactional balance appeared as 5, which
is (20-(10+5))
If I got another credit transaction of 30 on 15Jan2014, the transactional balance will appeared to be the same value with the credit value. This is because the 1st credit transaction still with balance of 5.
Hope the above can explain better…
Hi Tom
Or I should re-phrase as below:-
1st transaction:-
Credited 20 & debited 10 on 1Jan2014
transaction look alike:-
Date Credit Debit Balance Credit Balance
1Jan2014 20 10 10 10
2nd Transaction :-
Debited 5 on 5Jan2014
transaction look alike:-
Date Credit Debit Balance Credit Balance
1Jan2014 20 10 10 10 5 (Balance left only 5 due to second debit transaction)
5Jan2014 0 5 5 0
3rd Transaction :-
Credited 30 on 15Jan2014
transaction look alike:-
Date Credit Debit Balance Credit Balance
1Jan2014 20 10 10 5 (Balance remain as the 3rd transaction was not debit transaction)
5Jan2014 0 5 5 0
15Jan2014 30 0 35 30 (Balance for this new credit transaction)
Assuming the new transaction come in, 4th transaction:-
Debited 15 on 20Jan2014
transaction look alike:-
Date Credit Debit Balance Credit Balance
1Jan2014 20 10 10 5 0 (The new debit transaction of 15 take from the this balance 1st)
5Jan2014 0 5 5 0
15Jan2014 30 0 35 30 20 (The balance of the new debit transaction 10, (15-5) will deduct from this transaction as the previous transaction had already become 0)
20Jan2014 0 15 20 0
In summary, whenever there is a new debit transaction comes in, it will deduct from the what ever is left on previous credit balance 1st base on FIFO logic. by end of the day, the latest 'Balance' value, which is here is 20 have to be equal to sum of all 'Credit balance'
Not sure this will make things even complicated? :smileyconfused:
Well, not surprisingly this complicates matters a fair bit.
I think that the following implements your logic, but check it very carefully; in the ordinary course of events, I would test something like this with a large number of diverse test cases.
Incidentally, the bound of 30 on the array is arbitrary...just set it to what you expect the highest number of transactions per month to be.
Tom
data have;
input Date date9. ID Credit Debit;
format Date date.;
cards;
01Jan2014 123 20 10
05Jan2014 123 0 5
15Jan2014 123 30 0
20Jan2014 123 0 15
01Jan2014 456 10 0
09Jan2014 555 0 10
01Jan2014 789 15 5
08Jan2014 789 35 15
run;
data inter1;
set have;
MonthPart = mdy(month(Date), 1, year(Date));
CreditBalance = 0;
run;
proc sort data=inter1 out=inter2;
by ID MonthPart;
run;
data Transactions(keep=Date ID Credit Debit Balance) CreditBalances(keep=Date ID CreditBalance);
ARRAY Dates{30} Date1-Date30;
ARRAY CreditBalances{30} CreditBalance1-CreditBalance30;
retain Balance TransactionCount Date1-Date30 CreditBalance1-CreditBalance30;
set inter2;
by ID MonthPart;
if first.MonthPart then
do;
TransactionCount = 0;
Balance = 0;
end;
TransactionCount = TransactionCount + 1;
TransactionBalance = Credit - Debit;
Balance = Balance + TransactionBalance;
Dates{TransactionCount} = Date;
CreditBalances{TransactionCount} = 0;
if TransactionBalance > 0
then CreditBalances{TransactionCount} = TransactionBalance; /* We have an overall credit on this transaction */
else if TransactionBalance < 0 then
do;
/* Overall debit...we need to go through the previous transactions to offset it against a credit balance */
TempBalance = TransactionBalance * -1;
do i = 1 to TransactionCount;
if TempBalance > 0 & CreditBalances{i} > 0 then
do;
if CreditBalances{i} >= TempBalance then
do;
CreditBalances{i} = CreditBalances{i} - TempBalance;
TempBalance = 0;
end;
else
do;
TempBalance = TempBalance - CreditBalances{i};
CreditBalances{i} = 0;
end;
end;
end;
end;
else; /* TransactionBalance = 0, don't need to do anything */
output Transactions;
if last.MonthPart then
do i = 1 to TransactionCount;
Date = Dates{i};
CreditBalance = CreditBalances{i};
output CreditBalances;
end;
run;
proc sql;
create table want as select
T.Date,
T.ID,
Credit,
Debit,
Balance,
CreditBalance
from Transactions T join CreditBalances C
on (T.Date = C.Date and T.ID = C.ID)
order by ID, Date;
quit;
Hi Tom
First of all many thanks for the script. It has been a very busy for these few weeks, and I got no time to try out yet...sorry for that. I believe it's work. haha....
Anyway, I will try it out next week and update the status here.
Once again, thanks for your kind help:)
kelvin
I like it .It is a challenge.
data have; input Date date9. ID Credit Debit; format Date date.; cards; 01Jan2014 123 20 10 05Jan2014 123 0 5 15Jan2014 123 30 0 20Jan2014 123 0 15 01Jan2014 456 10 0 09Jan2014 555 0 10 01Jan2014 789 15 5 08Jan2014 789 35 15 run; data want; array x{99999} _temporary_; n=0; do until(last.id); set have; by id; n+1; x{n}=credit;d=debit; do i=1 to n; _d=x{i}-d; x{i}=ifn(( _d gt 0),_d,0); d=ifn((_d lt 0),abs(_d),0); end; end; n=0; do until(last.id); set have; by id; n+1; Credit_Balance=x{n}; output; end; drop d _d i; run;
Xia Keshan
Hi Ksharp,
I am trying to implement the same logic and found out your algorithm working. But in addition to FIFO logic for calculating balance I also need to calculate aging of dates.
For example:
Date ID Credit Debit
01Mar2018 123 100000 50000
05Mar2018 123 50000 0
15Mar2018 123 0 50000
20Mar2018 123 0 0
21Mar2018 123 0 50000
Would have aging calculated as 15thMarch - 1March = 14 days for first Credit.
Step 1:(100000-50000 = 50000)
Step 2:(50000 - 0 = 50000)
Step 3:(50000 - 50000 = 0) = Aging for 1stmarch credit = 15thMarch - 1March = 14
I tried by altering your algorithm and fetching end dates was possible using criteria (Where x[i]=0). But I am not getting a hold of how do I get start date.
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.