topic Re: how to calculate holding period of stocks? in SAS Programming

how to calculate holding period of stocks?

shivangi_naik — Wed, 14 Nov 2012 06:02:44 GMT

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,

Re: how to calculate holding period of stocks?

RichardinOz — Wed, 14 Nov 2012 06:46:29 GMT

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

Re: how to calculate holding period of stocks?

Ksharp — Wed, 14 Nov 2012 10:55:20 GMT

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

Re: how to calculate holding period of stocks?

art297 — Wed, 14 Nov 2012 15:35:22 GMT

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;

run;

Re: how to calculate holding period of stocks?

Ksharp — Thu, 15 Nov 2012 08:57:42 GMT

ArthurT,

Congratulations. Yours are better than mine.

Re: how to calculate holding period of stocks?

shivangi_naik — Thu, 15 Nov 2012 10:47:57 GMT

Thanks ArthurT & Ksharp for your valuable inputs.Those are very much helpful..

Regards,

Shivangi naik

Re: how to calculate holding period of stocks?

Haikuo — Fri, 16 Nov 2012 04:38:27 GMT

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;

drop _:;

run;

Haikuo

Re: how to calculate holding period of stocks?

Ksharp — Fri, 16 Nov 2012 05:07:51 GMT

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;

Re: how to calculate holding period of stocks?

Haikuo — Fri, 16 Nov 2012 15:09:58 GMT

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;

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;

drop _:;

run;

Thanks for all these intellectual exchange!

Haikuo

Re: how to calculate holding period of stocks?

Ksharp — Fri, 16 Nov 2012 15:26:06 GMT

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.

Re: how to calculate holding period of stocks?

Haikuo — Fri, 16 Nov 2012 16:17:08 GMT

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

Re: how to calculate holding period of stocks?

Ksharp — Mon, 19 Nov 2012 03:16:05 GMT

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.

Re: how to calculate holding period of stocks?

shivangi_naik — Mon, 19 Nov 2012 10:50:45 GMT

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

Re: how to calculate holding period of stocks?

shivangi_naik — Mon, 19 Nov 2012 12:27:26 GMT

Thanks for your input.

However I have SAS 4.3 in where hash tables with duplicate keys cannot be used.

Thanks& regards,

Shivangi

Re: how to calculate holding period of stocks?

Haikuo — Mon, 19 Nov 2012 14:19:18 GMT

So I suppose you have EG 4.3, and your BASE SAS version is 9.1. Anyway, the "multidata: 'y'" option is to put there for the sake of robustness. Given the nature of your data, it is highly unlikely that you will have duplicated keys "h.definekey('_stock','_Buy_Sell','_TRANS_DATE');", therefore you should be fine if you remove this option. However, there maybe other methods (such setcur()) that are not supported by 9.1, so Hash() is still may not good for your settings.

Haikuo

Re: how to calculate holding period of stocks?

shivangi_naik — Tue, 20 Nov 2012 11:36:57 GMT

I have modified 'by' clause and got the desired results.

Also increase in array size solved the issue large data.

Thanks..

Re: how to calculate holding period of stocks?

TammyGoSAS — Tue, 11 Feb 2014 04:03:51 GMT

Hello,

I am very new to SAS and have a research project which requires return calculation. This discussion matches my question. However, my data allows short sale. So SHRS in your data may be negative. I need to calculate RETURN at each point of time by following First In First Out (FIFO) rule. FIFO means where the oldest entry is processed first. This is exactly the rule your are using.

Original data:

Date Buy/Sell Price ShrsHeld

1 Buy 10 2

2 Buy 20 5

3 Sell 30 4

4 Sell 40 -2

5 Sell 50 -4

6 Buy 60 -1

Desired ouput:

Date Buy/Sell Price ShrsHeld Return

1 Buy 10 2

2 Buy 20 5

3 Sell 30 4 20

4 Sell 40 -2 90

5 Sell 50 -4

6 Buy 60 -1 -50

For example:

At time 3, return is 1*(30-10)=20.

At time 4, return is 1*(40-10)+3*(40-20)=90.

At time 6, return is -2*(60-40)-1*(60-50)=-50.

I have trouble to code it. Please help!! Thank you for your kind assistance.

Best,

Tammy

Re: how to calculate holding period of stocks?

art297 — Tue, 11 Feb 2014 04:50:27 GMT

Tammy,

Repost your question as a new question. More people are likely to respond to a new question than they would respond to one that is 2 years old and already marked as having been answered.

Re: how to calculate holding period of stocks?

TammyGoSAS — Tue, 11 Feb 2014 05:52:10 GMT

Will do. Thank you.

Re: how to calculate holding period of stocks?

jdmarino — Tue, 11 Feb 2014 14:21:58 GMT

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.