SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

creating portfolio from one column prices

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

creating portfolio from one column prices

Hi,

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

datestockprice
20/1/2010A6
21/1/2010A2
22/1/2010A5
20/1/2010B
21/1/2010B4
22/1/2010B2

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:

dateportfolio price
20/1/20106
21/1/20103
22/1/20103.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


Accepted Solutions
Solution
‎01-19-2015 07:51 PM
Trusted Advisor
Posts: 1,137

Re: creating portfolio from one column prices

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

Thanks,
Jag

View solution in original post


All Replies
Super User
Posts: 19,820

Re: creating portfolio from one column prices

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

Super Contributor
Posts: 441

Re: creating portfolio from one column prices

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

Solution
‎01-19-2015 07:51 PM
Trusted Advisor
Posts: 1,137

Re: creating portfolio from one column prices

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

Thanks,
Jag
Super Contributor
Posts: 441

Re: creating portfolio from one column prices

Posted in reply to Jagadishkatam

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

Super User
Posts: 19,820

Re: creating portfolio from one column prices

Look at Weight Statement in proc means.

Trusted Advisor
Posts: 1,137

Re: creating portfolio from one column prices

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

Thanks,
Jag
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 442 views
  • 6 likes
  • 3 in conversation