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.
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 ;;;;
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.
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;
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;
It's awesome!!!!!!!
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.