BookmarkSubscribeRSS Feed
EJAA
Obsidian | Level 7

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

2 REPLIES 2
novinosrin
Tourmaline | Level 20
proc sql;
create table want as
select * ,mean(price) as port_price
from have
group by dates;
quit;
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 2 replies
  • 370 views
  • 0 likes
  • 3 in conversation