topic Re: SAS_transaction balance by day with FIFO logic in SAS Enterprise Guide

SAS_transaction balance by day with FIFO logic

klvn1818 — Fri, 17 Oct 2014 03:58:49 GMT

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

Re: SAS_transaction balance by day with FIFO logic

TomKari — Fri, 17 Oct 2014 09:59:07 GMT

Are you comfortable using the SAS Program window within Enterprise Guide? This would be very difficult to do using just EG tasks.

Tom

Re: SAS_transaction balance by day with FIFO logic

klvn1818 — Fri, 17 Oct 2014 11:34:25 GMT

Hi Tom

Yeap, I am okay with SAS program window, but is only familiar to some of the codes.

Re: SAS_transaction balance by day with FIFO logic

TomKari — Fri, 17 Oct 2014 12:02:20 GMT

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;

Re: SAS_transaction balance by day with FIFO logic

klvn1818 — Mon, 20 Oct 2014 01:52:11 GMT

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...

Re: SAS_transaction balance by day with FIFO logic

TomKari — Tue, 21 Oct 2014 13:20:26 GMT

Could you walk through how you calculate it in your example, in some detail? I must admit, I can't follow it.

Tom

Re: SAS_transaction balance by day with FIFO logic

klvn1818 — Thu, 23 Oct 2014 02:49:16 GMT

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 have credited 20 on 01Jan2014
Debited 10 on 01Jan2014
Another debit transaction of 5 on 5Jan2014

I need the 1^st 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 1^st credit transaction still with balance of 5.

Hope the above can explain better…

Re: SAS_transaction balance by day with FIFO logic

klvn1818 — Fri, 24 Oct 2014 16:28:31 GMT

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:

Re: SAS_transaction balance by day with FIFO logic

TomKari — Sun, 26 Oct 2014 14:44:13 GMT

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;

Re: SAS_transaction balance by day with FIFO logic

Ksharp — Sun, 26 Oct 2014 15:58:53 GMT

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

Re: SAS_transaction balance by day with FIFO logic

klvn1818 — Thu, 06 Nov 2014 07:43:07 GMT

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

Re: SAS_transaction balance by day with FIFO logic

mehta2626 — Thu, 04 Oct 2018 14:09:12 GMT

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.