BookmarkSubscribeRSS Feed
shivangi_naik
Calcite | Level 5

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_DATESTOCKSHRSBuy/Sell
1/30/2009X100B
2/27/2009X100B
3/31/2009X400B
5/29/2009X100B
11/16/2009X150S
12/31/2009X190S
1/11/2010X260S

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_DATESTOCKSHRSBuy/SellPurchase date
1/30/2009X100B1/30/2009
2/27/2009X100B2/27/2009
3/31/2009X400B3/31/2009
5/29/2009X100B5/29/2009
11/16/2009X100S1/30/2009
11/16/2009X50S2/27/2009
12/31/2009X50S2/27/2009
12/31/2009X140S3/31/2009
1/11/2010X260S3/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,

19 REPLIES 19
RichardinOz
Quartz | Level 8

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

Ksharp
Super User

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;
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;

art297
Opal | Level 21

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;

Ksharp
Super User

ArthurT,

Congratulations. Yours are better than mine.

shivangi_naik
Calcite | Level 5

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

shivangi_naik
Calcite | Level 5

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

Also increase in array size solved the issue large data.

Thanks..

shivangi_naik
Calcite | Level 5

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

Regards,

Shivangi naik

Haikuo
Onyx | Level 15

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

Ksharp
Super User

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;

Haikuo
Onyx | Level 15

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

Ksharp
Super User

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.

Haikuo
Onyx | Level 15

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

Ksharp
Super User

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.

jdmarino
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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