Desktop productivity for business analysts and programmers

SAS_transaction balance by day with FIFO logic

Reply
Occasional Contributor
Posts: 6

SAS_transaction balance by day with FIFO logic

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

PROC Star
Posts: 1,143

Re: SAS_transaction balance by day with FIFO logic

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

Tom

Occasional Contributor
Posts: 6

Re: SAS_transaction balance by day with FIFO logic

Hi Tom

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

PROC Star
Posts: 1,143

Re: SAS_transaction balance by day with FIFO logic

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;

Occasional Contributor
Posts: 6

Re: SAS_transaction balance by day with FIFO logic

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

PROC Star
Posts: 1,143

Re: SAS_transaction balance by day with FIFO logic

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

Tom

Occasional Contributor
Posts: 6

Re: SAS_transaction balance by day with FIFO logic

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…

Occasional Contributor
Posts: 6

Re: SAS_transaction balance by day with FIFO logic

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:

PROC Star
Posts: 1,143

Re: SAS_transaction balance by day with FIFO logic

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;

Occasional Contributor
Posts: 6

Re: SAS_transaction balance by day with FIFO logic

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 helpSmiley Happy

kelvin

Super User
Posts: 9,854

Re: SAS_transaction balance by day with FIFO logic

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

Ask a Question
Discussion stats
  • 10 replies
  • 928 views
  • 0 likes
  • 3 in conversation