calculate stock cost based on FIFO

[andreas_lds]

Another try using a hash-object:

data want;
   set work.have;
   by Stock;
   
   format Date date9.;
   
   length _Amount 8;
   
   if _n_ = 1 then do;
      declare hash fifo(ordered: 'yes');
      fifo.defineKey('Date');
      fifo.defineData('Date', '_Amount', 'Price');
      fifo.defineDone();
      declare hiter fi('fifo');
   end;
   
   if Trade > 0 then do;
      _Amount = Trade;
      fifo.add();
      _Total = 0;
      rc = fi.first();
      
      do while (rc = 0);
         _Total = _Total + (_Amount * Price);
         rc = fi.next();
      end;
   end;
   else do;
      _Remaining = abs(Trade);
      _Date = Date;
      _Price = Price;
      
      rc = fi.first();
      _Total = 0;
      
      do while (rc = 0);
      
         if _Remaining > 0 then do;
            _Taken = min(_Amount, _Remaining);
            _Amount = _Amount - _Taken;
            _Remaining = _Remaining - _Taken;
             
            fifo.replace();
         end;
        
         _Total = _Total + _Amount * price;
                  
         rc = fi.next();
      end;
            
      Date = _Date;
      Price = _Price;
   end;
   
   RefPrice = round(_Total / Holding, 0.01);
  
   if last.Stock then do;
      rc = fifo.clear();
   end;

   drop rc _:;
run;

The paper "From Stocks to Flows: Using SAS® HASH objects for FIFO, LIFO, and other FO’s" by Mark Keintz (@mkeintz ??) was a very useful inspiration.

[Ksharp]

Hi. I just realize your code can't handle the case which has duplicated DATE .

data work.have;
  infile datalines dsd truncover;
  input Date:MMDDYY10. Stock:$1. Trade:BEST. Holding:BEST. Price:BEST.;
  format Date MMDDYY10.;
datalines4;
01/01/2020,A,100,100,20
02/01/2020,A,100,200,23.2
03/01/2020,A,-200,0,22
04/01/2020,A,90,90,23
05/01/2020,A,-60,30,24
05/02/2020,A,20,50,20
03/01/2020,B,200,200,10
04/01/2020,B,-100,100,15
05/01/2020,B,-100,0,12
05/02/2020,B,100,100,14
05/03/2020,B,100,200,11
05/04/2020,B,100,300,18
05/04/2020,B,50,350,28
05/04/2020,B,-50,300,8
;;;;

[andreas_lds]

I know, that was one of the restrictions described in the paper i mentioned. Unfortunately SAS has no easy to use language elements to create a fifo-list (aka stack), if you want to store multiple values. And, of course, i only test using the data provided by the author. Will check the code next year, if i can spare the time.

[Ksharp]

Hi, I think you need add an option MULTIDATA to contain duplicated date key . 

And change your code a little bit could make job done . Check FIND_NEXT() for Hash Iteration Object.

data want;
   
   if _n_ = 1 then do;
   if 0 then set sashelp.stocks;
      declare hash fifo(ordered: 'yes' ,multidata:'yes' );
      fifo.definekey('stock');
	  fifo.definedata('close');
	  fifo.definedone();
  end;
   set sashelp.stocks;
fifo.add();
run;

[Ksharp]

Or just make a DATE variable for you code ? like:
1,2,3,4,5,6,7,.............
I think it is most simple way for your problem.

[Xiyuan]

There is such an introduction! It's amazing.
I'm really a little strange to hash object although I've heard of it. I'll try to learn later.
thanks! happy new year!

[Ksharp]

I like this question.

data have;
input stock date trade holding price;
cards;
1 0 100 100 20
1 1 100 200 23.2
1 2 -120 80 22
1 3 20 100 23
1 4 -90 10 24
2 0 100 100 20
2 1 100 200 23.2
2 2 -120 80 22
2 3 20 100 23
2 4 -90 10 24
;
run ;

data want;
array positive{999999} _temporary_;
i=1;j=1;call missing(of positive{*});

do until(last.stock);
  set have;
  by stock;
/*hold the positive share*/
  if trade>0 then do; 
    do i=i to i+trade-1 ;
      positive{i}= price ; 
 end;
  end;
/*eliminate the sell share - FIFO*/
  if trade<0 then do;
    do j=j to j+abs(trade)-1 ;
      positive{j}= . ;
 end;
  end;    
/*calcualte cost*/
cost=sum(of positive{*})/n(of positive{*});

output;
end;
drop i j;
run;

[kelxxx]

It's awesome!!!!!!!

[Xiyuan]

Thanks Ksharp! this code works well and robust enough even I extend more
learned a lot, i'm going to try this on my large dateset

[Ksharp · Accepted solution]

If you have big table, try this one .

data work.have;
  infile datalines dsd truncover;
  input Date:MMDDYY10. Stock:$1. Trade:BEST. Holding:BEST. Price:BEST.;
  format Date MMDDYY10.;
datalines4;
01/01/2020,A,100,100,20
02/01/2020,A,100,200,23.2
03/01/2020,A,-200,80,22
04/01/2020,A,90,100,23
05/01/2020,A,-60,10,24
05/02/2020,A,20,30,20
03/01/2020,B,200,200,10
04/01/2020,B,-100,100,15
05/01/2020,B,-100,150,12
;;;;


data want2;
array _trade{9999} _temporary_;
array _price{9999} _temporary_;

i=0;j=1;call missing(of _trade{*} _price{*});

do until(last.stock);
  set have;
  by stock;
/*hold the positive share*/
  if trade>0 then do; 
    i+1;
 _trade{i}= trade ;
    _price{i}= price ; 
  end;
/*eliminate the sell share - FIFO*/
  if trade<0 then do;
    balance_trade=trade ;
    do j=j to i ;
      balance_trade=sum(balance_trade,_trade{j}) ;
   if balance_trade<=0 then do;_trade{j}=.;_price{j}=.; end;
    else do;_trade{j}=balance_trade;leave;end;
    end;
  end;    
/*calcualte cost*/ 
a=0;b=0;
do k=j to i;
 a+_trade{k}*_price{k};
 b+_trade{k};
end;
cost=divide(a,b);

output;
end;
drop i j k balance_trade a b;
run;

[Xiyuan]

This is really great!

I ran with your last version of code for a long time, which is beyond your imagination, but this one is so quick, thanks a lot.