Implementing FIFO Logic

Reply
New Contributor
Posts: 3

Implementing FIFO Logic

Hi everyone,

I am trying to figure out the solution to the following problem using SAS:

               Given information     Desired Column
IDtrxn_typetrxn_datetrxn_dateIN date according to FIFO
ABCIN1-Jan-15150
ABCIN10-Jan-1550
ABCOUT20-Jan-151001-Jan-15
ABCOUT25-Jan-151001-Jan-15

Logic is that for each of the OUT transactions for a given ID, a suitable IN date needs to be assigned. This assignment needs to performed using the FIFO concept ("First In, First Out") on the transaction amount. Any residual amount is to be carried forward for the calculation of next OUT transaction. If more than one IN date is available for an OUT trxn (as is the case for 10th Jan IN trxn in the given example), then the earliest date is to be assigned.

Any leads on this would be much appreciated, thanks a lot

Grand Advisor
Posts: 9,584

Re: Implementing FIFO Logic

Do you have a big table ? If not try the following, otherwise split your table into lots of sub-tables according ID value , and process each ID one time.

Code: Program

data have;
infile cards expandtabs truncover;
input ID $ trxn_type $ trxn_date : date11. trxn;
format trxn_date  date11.;
cards;
ABC IN 1-Jan-15 150
ABC IN 10-Jan-15 50
ABC OUT 20-Jan-15 100
ABC OUT 25-Jan-15 100
;
run;
data in(drop=trxn_type trxn i);
set have(rename=(trxn_date=_trxn_date) where=(trxn_type='IN'));
retain _trxn 1;
do i=1 to trxn;
  output;
end;
run;
data out(drop=_trxn i);
set have(rename=(trxn=_trxn) where=(trxn_type='OUT'));
retain trxn 1;
n+1;
do i=1 to _trxn;
  output;
end;
run;
data temp;
merge out in;
by id;
if n ne lag(n) then group+1;
output;
call missing(trxn,_trxn);
run;
data want;
set temp;
by group;
retain sum FIFO;
format FIFO date11.;
if first.group then do;FIFO=_trxn_date;sum=0;end;
sum+trxn;
if last.group;
keep ID  trxn_type trxn_date sum FIFO;
run;

Xia Keshan

New Contributor
Posts: 3

Re: Implementing FIFO Logic

Thanks a lot! This helps. But I do have a large data set, with one ID repeating several times. Can I do a by ID, and proceed? Thanks

Grand Advisor
Posts: 9,584

Re: Implementing FIFO Logic

OK. There are some scenario  you need to consider about .

What if the data look like the following , what you gonna do ?

data have;

infile cards expandtabs truncover;

input ID $ trxn_type $ trxn_date : date11. trxn;

format trxn_date  date11.;

cards;

ABC IN 1-Jan-15 150

ABC IN 10-Jan-15 20

ABC IN 11-Jan-15 20

ABC IN 12-Jan-15 20

ABC IN 13-Jan-15 10

ABC IN 14-Jan-15 10

ABC OUT 20-Jan-15 100

ABC OUT 25-Jan-15 100

ABC OUT 26-Jan-15 20

;

run;

data have;
infile cards expandtabs truncover;
input ID $ trxn_type $ trxn_date : date11. trxn;
format trxn_date  date11.;
cards;
ABC IN 1-Jan-15 150 
ABC IN 10-Jan-15 50 
ABC OUT 20-Jan-15 100 
ABC OUT 25-Jan-15 100 
;
run;
data want;
 set have;
 by ID;
 array d{99999} _temporary_;
 array t{99999} _temporary_;
 retain count;
 if first.id then do;k=0;out=0;in=0;count=0;call missing(of d{*} t{*});end;
 if trxn_type='IN' then do;
  k+1;
  d{k}=trxn_date;
  t{k}=trxn;
 end;
 if trxn_type='OUT' then do;
  out+trxn;FIFO=d{ifn(count=0,1,count)};
  do i=count+1 to k;
   in+t{i};
   if out lt in then do;count=i;leave;end;
  end;
 end;
 format FIFO date11.;
 drop in out i k count ;
run;



Xia Keshan

New Contributor NM1
New Contributor
Posts: 2

Re: Implementing FIFO Logic

 

 

Thanks Ksharp. I have a similar situation and tried the solution. It is close but not solving the poblem completely. The third sell on the 26 Jan should have FIFO value as 1-Jan-15 since trxn was not exhausted or consumed but it is capturing the date from the second Buy.

 

data have;
infile cards expandtabs truncover;
input ID $ trxn_type $ trxn_date : date11. trxn;
format trxn_date  date11.;
cards;
ABC BUY 1-Jan-15 150 
ABC BUY 10-Jan-15 50 
ABC SELL 20-Jan-15 60 
ABC BUY  25-Jan-15 50 
ABC SELL 25-Jan-15 50 
ABC SELL 26-Jan-15 50
ABC SELL 27-Jan-15 70 
BAC BUY 12-Jan-15 50 
BAC BUY 15-Jan-15 50 
BAC SELL 26-Jan-15 50
BAC SELL 27-Jan-15 70
BAC SELL 27-Jan-15 20 
;
run;
DATA have;
SET have;
PRICE = TRXN;
RUN;
data want;
 set have;
 by ID;
 array d{99999} _temporary_;
 array t{99999} _temporary_;
 retain count;
 if first.id then do;k=-0;SELL=0;BUY=0;count=0;call missing(of d{*} t{*});end;
 if trxn_type='BUY' then do;
  k+1;
  d{k}=trxn_date;
  t{k}=trxn;
 end;
 if trxn_type='SELL' then do;
  SELL+trxn;FIFO=d{ifn(count=0,1,count)};
  do i=count+1 to k;
   BUY+t{i};
   if SELL lt BUY then do;count=i;leave;end;
  end;
 end;
 format FIFO date11.;
 REM_QTY = BUY - SELL;
 drop BUY SELL i k count REM_QTY;
run;
IDtrxn_typetrxn_datetrxnFIFO
ABCBUY1-Jan-15150.
ABCBUY10-Jan-1550.
ABCSELL20-Jan-15601-Jan-15
ABCBUY25-Jan-1550.
ABCSELL25-Jan-15501-Jan-15
ABCSELL26-Jan-155010-Jan-15
ABCSELL27-Jan-157025-Jan-15
BACBUY12-Jan-1550.
BACBUY15-Jan-1550.
BACSELL26-Jan-155012-Jan-15
BACSELL27-Jan-157015-Jan-15
BACSELL27-Jan-152015-Jan-15
Ask a Question
Discussion stats
  • 4 replies
  • 430 views
  • 3 likes
  • 3 in conversation