Excluding stock (i) from portfolio of stocks

[EJAA]

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

[novinosrin]

proc sql;
create table want as
select * ,mean(price) as port_price
from have
group by dates;
quit;

[mkeintz]

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;