topic Re: creating portfolio from one column prices in SAS Data Management

creating portfolio from one column prices

ilikesas — Mon, 19 Jan 2015 20:00:09 GMT

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

Re: creating portfolio from one column prices

Reeza — Mon, 19 Jan 2015 20:25:11 GMT

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

Re: creating portfolio from one column prices

ilikesas — Tue, 20 Jan 2015 00:33:01 GMT

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

Re: creating portfolio from one column prices

Jagadishkatam — Tue, 20 Jan 2015 00:51:58 GMT

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

Re: creating portfolio from one column prices

ilikesas — Tue, 20 Jan 2015 01:10:09 GMT

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

Re: creating portfolio from one column prices

Reeza — Tue, 20 Jan 2015 01:17:39 GMT

Look at Weight Statement in proc means.

Re: creating portfolio from one column prices

Jagadishkatam — Tue, 20 Jan 2015 03:04:13 GMT

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