Hi All,
I want to calculate holding period of stocks to be held by a particular customer.
For example look data in this format:
TRANS_DATE | STOCK | SHRS | Buy/Sell |
1/30/2009 | X | 100 | B |
2/27/2009 | X | 100 | B |
3/31/2009 | X | 400 | B |
5/29/2009 | X | 100 | B |
11/16/2009 | X | 150 | S |
12/31/2009 | X | 190 | S |
1/11/2010 | X | 260 | S |
Now ,150 units of 'X' were sold on 11/16/2009;out of which holding period of 100 is (11/16/2009-1/30/2009) and holding period of remaining 50 is (11/16/2009-2/27/2009).
So I want output in this format:
TRANS_DATE | STOCK | SHRS | Buy/Sell | Purchase date |
1/30/2009 | X | 100 | B | 1/30/2009 |
2/27/2009 | X | 100 | B | 2/27/2009 |
3/31/2009 | X | 400 | B | 3/31/2009 |
5/29/2009 | X | 100 | B | 5/29/2009 |
11/16/2009 | X | 100 | S | 1/30/2009 |
11/16/2009 | X | 50 | S | 2/27/2009 |
12/31/2009 | X | 50 | S | 2/27/2009 |
12/31/2009 | X | 140 | S | 3/31/2009 |
1/11/2010 | X | 260 | S | 3/31/2009 |
Can anyone help me to build this logic in SAS?
I guess we need to use Hash tables to store the data.
Thanks & regards,
To answer this question we have to have some way of knowing which shares were sold. Shares do not have to be sold in the order they were bought. We need to have a parcel identifier in the data.
Richard in Oz
Hi.Stefaan Pauwels,
Is that you. I coded it for you before, but that code is not very efficient , Maybe you could try this again.
data have; infile cards expandtabs; input TRANS_DATE : mmddyy10. STOCK $ SHRS Buy_Sell $; format TRANS_DATE mmddyy10.; cards; 1/30/2009 X 100 B 2/27/2009 X 100 B 3/31/2009 X 400 B 5/29/2009 X 100 B 11/16/2009 X 150 S 12/31/2009 X 190 S 1/11/2010 X 260 S ; run; proc sort data=have;by Buy_Sell TRANS_DATE;run; data b; set have(where=(Buy_Sell='B')); do i=1 to SHRS; output; end; keep TRANS_DATE; run; data s; set have(where=(Buy_Sell='S')); do i=1 to SHRS; output; end; drop SHRS i; run; data temp; merge s b(rename=(TRANS_DATE=_TRANS_DATE)); retain s 1; run; data temp; set temp; if TRANS_DATE ne lag(TRANS_DATE) or _TRANS_DATE ne lag(_TRANS_DATE) then group+1; run; data temp1; set temp; by group; SHRS+s; if last.group then do;output;SHRS=0;end; drop s group; run; data want; set have(where=(Buy_Sell='B')) temp1; run;
Ksharp
Another way to do it could be as follows:
data have;
informat TRANS_DATE mmddyy10.;
format TRANS_DATE mmddyy10.;
input TRANS_DATE STOCK $ SHRS Buy_Sell $;
cards;
1/30/2009 X 100 B
2/27/2009 X 100 B
3/31/2009 X 400 B
5/29/2009 X 100 B
11/16/2009 X 150 S
12/31/2009 X 190 S
1/11/2010 X 260 S
;
proc sort data=have;
by stock trans_date buy_sell;
run;
data want (drop=keepit_: first: counter amount);
set have;
by stock;
format purchase_date mmddyy10.;
array keepit_d(999);
array keepit_s(999);
retain keepit_:;
if first.stock then do;
call missing(of keepit_d(*));
call missing(of keepit_s(*));
first_counter=1;
counter=0;
end;
if Buy_Sell eq 'B' then do;
counter+1;
keepit_d(counter)=TRANS_DATE;
keepit_s(counter)=SHRS;
output;
end;
else do;
amount=SHRS;
do until (amount eq 0);
purchase_date=keepit_d(first_counter);
shrs=min(amount,keepit_s(first_counter));
amount=amount-min(amount,keepit_s(first_counter));
keepit_s(first_counter)=keepit_s(first_counter)-
shrs;
output;
if keepit_s(first_counter) eq 0 then first_counter+1;
end;
end;
run;
ArthurT,
Congratulations. Yours are better than mine.
Thanks for your inputs.The logic is working perfectly for buy and sell of one customer for multiple stocks.
However,if more than one customers are buying/selling same stock on same date do we need to change the 'by' clause in code?
Also is length of array a problem while dealing with huge sets of data?
Thanks & Regards,
Shivangi
I have modified 'by' clause and got the desired results.
Also increase in array size solved the issue large data.
Thanks..
Thanks ArthurT & Ksharp for your valuable inputs.Those are very much helpful..
Regards,
Shivangi naik
Here is one-step Hash() approach. Although 1-step, but it is 1-really-mouthful-step. To get more general solution, raw data has been modified to reflect multiple stocks. Results are matching Art's, but not Ksharp's.
data have;
infile cards expandtabs;
input TRANS_DATE : mmddyy10. STOCK $ SHRS Buy_Sell $;
format TRANS_DATE mmddyy10.;
cards;
1/30/2009 X 100 B
2/27/2009 X 100 B
3/31/2009 X 400 B
5/29/2009 X 100 B
11/16/2009 X 150 S
12/31/2009 X 190 S
1/11/2010 X 260 S
1/30/2009 Y 100 B
2/27/2009 Y 100 B
3/31/2009 Y 400 B
5/29/2009 Y 100 B
11/16/2009 Y 150 S
12/31/2009 Y 190 S
;
data want;
retain _rc;
if _n_=1 then do;
if 0 then set have(rename=(TRANS_DATE=_TRANS_DATE SHRS=_SHRS stock=_stock buy_sell=_buy_sell));
declare hash h(dataset:'have(rename=(TRANS_DATE=_TRANS_DATE SHRS=_SHRS stock=_stock buy_sell=_buy_sell) where=(_Buy_Sell="B"))', multidata:'y', ordered:'a');
h.definekey('_stock','_Buy_Sell','_TRANS_DATE');
h.definedata(all:'y');
h.definedone();
declare hiter hi('h');
end;
do until (last.buy_sell);
set have;
by STOCK buy_sell notsorted;
format Purchase_date mmddyy10.;
if first.stock then _rc=hi.setcur(key:stock, key:buy_sell,key:TRANS_DATE);
if Buy_Sell='B' then do; Purchase_date=TRANS_DATE ;output;end;
else if Buy_Sell='S' then do;
_diff=shrs-_shrs;
do while (_diff>0 and _rc=0 and stock=_stock);
Purchase_date=_TRANS_DATE;
shrs=_shrs;
output;
_rc=hi.next();
if _diff <= _shrs then do; __shrs=_shrs; _shrs=_diff; _diff=_diff-__shrs; end;
else _diff=_diff-_shrs;
end;
Purchase_date=_TRANS_DATE;
shrs=_shrs;
output;
_shrs=-_diff;
end;
end;
drop _:;
run;
Haikuo
HaiKuo,
Your result is unlike Art's,But mine is. I tested it with the data below.
data have; infile cards expandtabs; input TRANS_DATE : mmddyy10. STOCK $ SHRS Buy_Sell $; format TRANS_DATE mmddyy10.; cards; 1/30/2009 X 100 B 2/27/2009 X 100 B 3/31/2009 X 40 B 5/29/2009 X 10 B 11/16/2009 X 150 S 12/31/2009 X 190 S 1/11/2010 X 260 S ; run;
Keshan,
No offense, but your dummy data is really dummy. OP is interested in the holding period of certain stocks, which I believe that first you need to have it (buy it) before even talking about holding it. So how could you sell what you don't have (selling quantity is more than buying quantity)? It should not occur in real life, and if it does, then an error should be flagged. That being said, if to mimic yours and art's results, a minor tweak to my code will do:
data have;
infile cards expandtabs;
input TRANS_DATE : mmddyy10. STOCK $ SHRS Buy_Sell $;
format TRANS_DATE mmddyy10.;
cards;
1/30/2009 X 100 B
2/27/2009 X 100 B
3/31/2009 X 40 B
5/29/2009 X 10 B
11/16/2009 X 150 S
12/31/2009 X 190 S
1/11/2010 X 260 S
;
run;
data want;
retain _rc;
if _n_=1 then do;
if 0 then set have(rename=(TRANS_DATE=_TRANS_DATE SHRS=_SHRS stock=_stock buy_sell=_buy_sell));
declare hash h(dataset:'have(rename=(TRANS_DATE=_TRANS_DATE SHRS=_SHRS stock=_stock buy_sell=_buy_sell) where=(_Buy_Sell="B"))', multidata:'y', ordered:'a');
h.definekey('_stock','_Buy_Sell','_TRANS_DATE');
h.definedata(all:'y');
h.definedone();
declare hiter hi('h');
end;
do until (last.buy_sell);
set have;
by STOCK buy_sell notsorted;
format Purchase_date mmddyy10.;
if first.stock then _rc=hi.setcur(key:stock, key:buy_sell,key:TRANS_DATE);
if Buy_Sell='B' then do; Purchase_date=. ;output;end;
else if Buy_Sell='S' then do;
_diff=shrs-_shrs;
do while (_diff>0 and _rc=0 and stock=_stock);
Purchase_date=_TRANS_DATE;
shrs=_shrs;
output;
_rc=hi.next();
if _rc=0 then do; if _diff <= _shrs then do; __shrs=_shrs; _shrs=_diff; _diff=_diff-__shrs; end;
else _diff=_diff-_shrs;
end;
end;
if _rc=0 then do;
Purchase_date=_TRANS_DATE;
shrs=_shrs;
output;
_shrs=-_diff;
end;
else do; _shrs=0;shrs=_diff;Purchase_date=.;output;end;
end;
end;
drop _:;
run;
Thanks for all these intellectual exchange!
Haikuo
HaiKuo,
Don't forget to notice the date.
1/30/2009 X 100 B
2/27/2009 X 100 B
3/31/2009 X 40 B
5/29/2009 X 10 B
11/16/2009 X 150 S
12/31/2009 X 190 S
1/11/2010 X 260 S
we sell 150 at 11/16/2009 , until then we already have 100+100+40+10 stocks, We surely can sell it now.
Hi Keshan,
I deleted my previous mumble jumble post. You are right about the date. I agree that even though there is still something left to desired (isn't always like this?), you got yourself reasonable approach. I hope that this wouldn't happen in real life, and if it(selling > buying) does,, the data owner should be warned.
Haikuo
Hi Bian Hai Kuo,
Agree. In the real life , it could not happen. but sometime,somewhere,someone maybe give us such messy data , You aren't able to prevent it occurring ,are you? We just make sure our code as strong as it is possible.
You can sell stock you don't own, it's called "short selling" and it's done all the time. You have to locate and borrow the shares first (your broker does this). The goal is always "buy low and sell high", but not necessarily in that order.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.