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;``````
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;
``````
Discussion stats
• 2 replies
• 115 views
• 0 likes
• 3 in conversation