Creating portfolios based on a number/category

[TonyChivers]

The first day of using SAS so please bear with me if I ask the question in the wrong way etc. 

I have a list of companies and I would like to create portfolios (equally weighted) for each month.

I currently have the data in excel and this is how the sheet is set up (in case this helps):

Company Name Trading Year Trading Month Category Total Market Value Return Div Return No Div
China Baoan Group Co., Ltd. 1991 6 1 1080144
China Baoan Group Co., Ltd. 1991 7 1 787605 -0.270833 -0.270833
Gintian Industry (Group) Co., Ltd. 1991 7 2 222124.92
China Baoan Group Co., Ltd. 1991 8 1 810108 0.028571 0.028571
Gintian Industry (Group) Co., Ltd. 1991 8 2 211445.83 -0.048077 -0.048077
China Baoan Group Co., Ltd. 1991 9 1 798856.5 -0.013889 -0.013889
Gintian Industry (Group) Co., Ltd. 1991 9 2 200766.75 -0.050505 -0.050505

And the list continues down from 1991 until 2017 with lots of companies.  

I need to create portfolios based on each of the categories, i.e. value-weighted returns for all of the companies in Category 1, 2, etc. (There are 6 categories in total).

Also, the portfolio is set up every year in June (trading month 6), if and only if, there is no missing return data in June. Then there is a buy and hold strategy, where I used the market weights based on June of each year and then the investment weights are based on the returns. Then every June the portfolios are rebalanced. 

In a similar way to Kenneth Frenches portfolios if anyone has experience with them. 

Thanks in advance if anyone can help. 

Tony

[Reeza]

So given what you've shown, what would you expect as output? It's helps if you can be more specific with your question.

[TonyChivers]

I would like the value weighted return for each category for each month.

[TonyChivers]

If I have 6 categories, I would like the value-weighted return for each category for each month of the year. 

To try and be more clear on the question. 

I want to define each portfolio in June of each year. 

Lets take 1991 as an example. 

If there are 10 stocks in June in 1991, and 4 of them have missing return data in June, then I would include them six stocks in the portfolio for one year. 

Then next June, I will create a new portfolio, which will again include all the stocks in June 1992 that dont have missing data. 

The amount I invest in each stock is based on its market capitalization for June and then it will change depending on the returns in the following months, until next June, when I will rely on the market cap again. 

So in effect the portfolios are created and rebalanced every June. 

If a stock appears later in the year, say it gets listed in December, and it wasn't around in June, I don't care for it and it will only be included in the following year, the June immediately after the December it was listed. 

Does this make it any clearer?

[Reeza]

Not really and unfortunately not something I have time to sort out today. Hopefully someone else can help. I do know that if you provide a clear starting point, here's what I have, here's what I want - output that matches your input and a description of the logic you'll likely get an answer very quickly. But trying to determine that and test the different requirements is something I cannot make the time for today. Hopefully someone else can help you out. Perhaps @novinosrin when he has some time.

[TonyChivers]

Thanks for your help anyway.