creating portfolio from one column prices

[ilikesas]

Hi,

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

date	stock	price
20/1/2010	A	6
21/1/2010	A	2
22/1/2010	A	5
20/1/2010	B
21/1/2010	B	4
22/1/2010	B	2

From this I would like to get a portfolio table which is the average price of all stocks which have a price for that specific date:

date	portfolio price
20/1/2010	6
21/1/2010	3
22/1/2010	3.5

for the date 20/1/2010 there is only one company available and as such the portfolio price is the price of company A for that date.

Please note that in my data I have thousands of dates and hundreds of companies, so it will be very practical for me if the code can handle any number of dates and companies.

Thank you

[Reeza]

Try a proc means/univariate/summary with class statement of date.

[ilikesas]

Hi Reeza,

thank you for replying, it is simpler than I thought...

When I did the proc means with output = newtable, I obtained my means in a new table.

But the first observation of the new portfolio table gives me the value of the overall mean, so is it possible to drop it? And I would like as well to drop the _TYPE_ and _FREQ_ new columns as well

Thank you

[Jagadishkatam · Accepted solution]

You can try proc sql as well like below, else as you mentioned proc means

proc sql;

create table want as select date, avg(price) as avg_price from have group by date;

quit;

by proc means

proc sort data=have;

by date;

run;

proc means noprint data=have mean;

by date;

var price;

output out=want(drop=_type_ _freq_) mean=avg_price;

run;

Thanks,

Jag

[ilikesas]

Hi Jag,

thanks for the code, it does exactly what I wanted it to do.

But now suppose that instead of calculating the equal weighted average like above, I want to find the value weighted average. Suppose that in the additional table I have the market cap of the stocks:

date           stock       price       mkt_cap

20/1/2010     A             5             100

20/1/2010     B            3              200

So now the portfolio price for the date 20/1/2010 is : (100/300)*5 + (200/300)*3 = 3.67

Thank you

[Reeza]

Look at Weight Statement in proc means.

[Jagadishkatam]

i agree with

The below code generates teh desired output.

proc means noprint data=have mean;

by date;

var price;

weight mkt_cap;

output out=want(drop=_type_ _freq_) mean=avg_price;

run;

Thanks,

Jag