BookmarkSubscribeRSS Feed
chiaihuang
Calcite | Level 5

Hi everyone,

 

I have the following variables:

CompanyID | Year | Month | EarnRank | MV | Return | EarnRank Quintile (0-4)

 

I wish to find the following:

1. The average monthly return from each earnrank quintile ie. for quintile 1, each month, buying the companies with EarnRank quintile=0

2. A portfolio of selling quintile 0 and buying quintile 4 each month

3. Additional limitation: only include months has more than 10 companies

4. I also need both equal weighted and value weighted portfolios

 

I'm not sure if this explanation is clear enough, please let me know if it is not. Thank you all in advance.

 

Henry

2 REPLIES 2
mkeintz
PROC Star
  1. Exactly how many portfolios are you asking to be constructed?  What are their attributes?
  2. Are you rebalancing every month?
  3. How many equities should be in the portfolio?  Do you intend to include all companies every month?  If a company is present in month K but no present in K+1, does that mean it equities are to be sold at the end of month K?
  4. What is the total portfolio starting value?
  5. What does the phrase "include only months with 10 companies" mean?  No rebalancing for any month with fewer than 10 companies?  No portfolio?  Carry forward the previous month's portfolio?
  6. When you refer to an "equal weighted" portfolio, do you mean that every month should include transactions to redivide current and new holdings into equal amounts?
  7. When you say "value weighted", what is the value measure?

 

This is far from a description which allows someone to operationalize the topic.  How about some actual data?  Not just a list of variables.

--------------------------
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

--------------------------
chiaihuang
Calcite | Level 5

Sorry for the unclarity.

Quintile portfolios mean five portfolios based on the quintile variable which has values of 0-4. I do not need rebalancing or compounding, just simple returns. I'll put it this way, I have five groups labeled 0-4, and each group needs to be further subgrouped by year and month, and each month's equal-weighted returns and value-weighted returns are the simple average returns and market value (MV) weighted average returns, respectively, of the individual stocks that month. There is no need to consider starting value, because everything will be in terms of percentage returns. Also, they are monthly portfolios, so literally it means holding for the month only. Number of stocks in each portfolio depends on the data, they are not the same for each portfolio, but I'd like to include only portfolios have more than 10 stocks. The following is what the actual dataset looks like.

 

CompanyYearQuarterEPSEarnRankDatedate_predyear_statedmonthMVReturnretpercentrank_er
X13194E20094-1.369.41848418849200985706.43-0.04531-4.531382
X13194E20101-4.548.618564189292010105819.210.0076020.7601842
X13194E201024.8611.61865519020201016811.63  2
X13194E201031115.81838018745201045864.32-0.05186-5.185712
X13194E201044.347.21884919214201087014.630.0161441.6144132
X13194E20111-5.647.618928192932011107014.630.0032760.3275622
X13194E201120.2711.41902219387201117691.280.0428084.2808032
X13194E2011310.14161874519110201147713.83-0.01756-1.755732
X13194E201147.948.81921419579201188819.04-0.03526-3.525562
X13194E201210.716.419296196612012109292.690.0619056.1905192
X13194E201227.9101938819753201219450.570.0368043.68042
X13194E2012313.3616.41911019475201249112.250.0977539.7753252
X13194E201243.2110.21957819943201289105.9-0.0115-1.149522
X13194E201316.3855.619659200242013109681.440.0104611.046072
X13194E2013213.5099.81975320118201319701.99-0.03024-3.024442
X13194E2013316.72515.61947419839201349044.58-0.01728-1.727882
X13194E201347.2131019942203072013810154.21-0.02736-2.736042
X13194E201416.535.6200232038820141010544.750.0506315.0631192
X13194E201429.96610.42011720482201419311.45-0.1122-11.21962
X13194E2014317.6515.419841202062014410298.090.0553095.5308612
X13194E201449.98982031020675201489229.23-0.0302-3.020092
X13194E201518.5364.820390207552015109969.210.0499794.9978792

 

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 880 views
  • 0 likes
  • 2 in conversation