Excluding stock (i) from portfolio of stocks

Reply
Contributor
Posts: 40

Excluding stock (i) from portfolio of stocks

Hi All

 

Thanks in advance for the help to get the right codes to solve the problem below; 

 

 I have the following table of dates and company stock prices:

date

permno

price

20/1/2010

10001

6

21/1/2010

10001

2

22/1/2010

10001

5

20/1/2010

10002

2

21/1/2010

10002

4

22/1/2010

10002

2

20/1/2010

10003

3

21/1/2010

10003

2

22/1/2010

10003

1

20/1/2010

10004

6

21/1/2010

10004

7

22/1/2010

10004

8

 

From the above table,  I would like to create a portfolio table which is the average price of all stocks for that specific date where i exclude from the portfolio the stock of interest as shown below; Thus in calculating the portfolio of stock price for instance for a firm with permno number 10001 for date 20/1/2010, i calculated the average of stock  price for firms 10002 , 10003 and 10004 thus 2+3+6/3=3.6667 as shown in the table below thus i excluded the price of firm with permno 10001 which is the firm of interest; I do same for the other dates etc and for the other firms. 

 

date

permno

price

Port-price

20/1/2010

10001

6

3.666667

21/1/2010

10001

2

4.333333

22/1/2010

10001

5

3.666667

20/1/2010

10002

2

1.333333

21/1/2010

10002

4

3.666667

22/1/2010

10002

2

4.666667

20/1/2010

10003

3

4.666667

21/1/2010

10003

2

4.333333

22/1/2010

10003

1

5

20/1/2010

10004

6

3.333333

21/1/2010

10004

7

2.666667

22/1/2010

10004

8

2.666667

 

Any help for me to enable me code run the above problem. Please  in my data I have thousands of dates and thousand of companies, so it will be very practical for me if the code can handle any number of dates and companies.

 

Also attached is a sample of my data set. 

 

Thank you

PROC Star
Posts: 1,833

Re: Excluding stock (i) from portfolio of stocks

proc sql;
create table want as
select * ,mean(price) as port_price
from have
group by dates;
quit;
Trusted Advisor
Posts: 1,345

Re: Excluding stock (i) from portfolio of stocks

What you can do is create a dataset of daily mean prices over ALL permno's for each date.  Then read your original data, get the corresponding mean price for that date, and adjust it for the given permno price.  I.e.

 

    complimentary_mean =      (all_permno_mean * N_permnos - price)/(N_permnos-1)

 

 

Generating the means by date can be most easily done by asking proc summary to write dataset MEANS_BY_DATE below.  Then there are multiple ways to link the all_permno_means by date with the data set.  Below uses SET ... KEY= statement:

 

data have;
  input date :ddmmyy8.  permno price;
  format date date9.;
datalines;
20/1/2010  10001 6
21/1/2010  10001 2
22/1/2010  10001 5
20/1/2010  10002 2
21/1/2010  10002 4
22/1/2010  10002 2
20/1/2010  10003 3  
21/1/2010  10003 2  
22/1/2010  10003 1
20/1/2010  10004 6
21/1/2010  10004 7
22/1/2010  10004 8
run;
proc summary data=have  nway;
  class date;
  var price;
  output out=means_by_date  (index=(date) keep=date all_permnos_mean n_permnos)  mean=all_permnos_mean n=n_permnos;
run;

data want;
  set have;
  set means_by_date key=date;
  complimentary_price=((n_permnos*all_permnos_mean) - price)/(n_permnos-1);
run;

 

Now if you are using a large universe (say 1,000 permnos per day) then you the SET ... KEY= statement loses efficiency, since it rereads each daily mean 1,000 times.  Instead you can put the data in a memory-resident hash object, keyed on DATE, as here:

 

proc summary data=have  nway;
  class date;
  var price;
  output out=means_by_date  (index=(date) keep=date all_permnos_mean n_permnos)  mean=all_permnos_mean n=n_permnos;
run;

data want;
  set have means_by_date (obs=0);
  if _n_=1 then do;
    declare hash h (dataset:'means_by_date');
      h.definekey('date');
      h.definedata(all:'Y');
      h.definedone();
  end;
  rc=h.find();
  complimentary_price=((n_permnos*all_permnos_mean) - price)/(n_permnos-1);
run;
Ask a Question
Discussion stats
  • 2 replies
  • 115 views
  • 0 likes
  • 3 in conversation