## how to calculate holding period of stocks?

Occasional Contributor
Posts: 5

# how to calculate holding period of stocks?

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,

Super Contributor
Posts: 644

## Re: how to calculate holding period of stocks?

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

Super User
Posts: 10,766

## Re: how to calculate holding period of stocks?

Hi.

```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;
data b;
do i=1 to SHRS;
output;
end;
keep TRANS_DATE;
run;
data 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;
run;
```

PROC Star
Posts: 8,163

## Re: how to calculate holding period of stocks?

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;

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;

Super User
Posts: 10,766

## Re: how to calculate holding period of stocks?

ArthurT,

Congratulations. Yours are better than mine.

Occasional Contributor
Posts: 5

## Re: how to calculate holding period of stocks?

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

Occasional Contributor
Posts: 5

## Re: how to calculate holding period of stocks?

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

Also increase in array size solved the issue large data.

Thanks..

Occasional Contributor
Posts: 5

## Re: how to calculate holding period of stocks?

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

Regards,

Shivangi naik

Posts: 3,167

## Re: how to calculate holding period of stocks?

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;

h.definedata(all:'y');

h.definedone();

declare hiter hi('h');

end;

set have;

format Purchase_date mmddyy10.;

if Buy_Sell='B' then do; Purchase_date=TRANS_DATE ;output;end;

_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

Super User
Posts: 10,766

## Re: how to calculate holding period of stocks?

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;

```
Posts: 3,167

## Re: how to calculate holding period of stocks?

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;

h.definedata(all:'y');

h.definedone();

declare hiter hi('h');

end;

set have;

format Purchase_date mmddyy10.;

if Buy_Sell='B' then do; Purchase_date=. ;output;end;

_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

Super User
Posts: 10,766

## Re: how to calculate holding period of stocks?

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.

Posts: 3,167

## Re: how to calculate holding period of stocks?

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

Super User
Posts: 10,766

## Re: how to calculate holding period of stocks?

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.

Frequent Contributor
Posts: 78

## Re: how to calculate holding period of stocks?

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.

Discussion stats
• 19 replies
• 1155 views
• 7 likes
• 7 in conversation