BookmarkSubscribeRSS Feed
klvn1818
Calcite | Level 5

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

11 REPLIES 11
TomKari
Onyx | Level 15

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

Tom

klvn1818
Calcite | Level 5

Hi Tom

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

TomKari
Onyx | Level 15

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;

klvn1818
Calcite | Level 5

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

TomKari
Onyx | Level 15

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

Tom

klvn1818
Calcite | Level 5

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

klvn1818
Calcite | Level 5

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:

TomKari
Onyx | Level 15

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;

klvn1818
Calcite | Level 5

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

Ksharp
Super User

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

mehta2626
Calcite | Level 5

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2964 views
  • 0 likes
  • 4 in conversation