Hi there,

I am currently struggling to merge two data files in the way that I want on Stata, so if anyone could help that would be great. I am trying to obtain two portfolios which have average return data for companies between 2005-2013. The companies selected for these portfolios are ranked based on their ESG score. So I have a file with 3000 companies and within each year they are ranked based on this ESG score. I also have a file with the same companies but with their return data for each month. What I ultimately need is two portfolios (one high ranked, one low) with average monthly return data for these companies.

the issue I am having is that each year these portfolios are rebalanced, therefore the companies within them change. So i need to be able to merge these two data files in a way that i can easily find the average monthly return for each portfolio each year based on the ESG rankings. So I think the best way is to somewhere merge the date horizontally whilst still having the companies ranked by ESG score and then manually selecting the top and bottom 800 companies for that month to take an average from. I have attached the stata file that i have done this for 2005 for.

I understand this probably doesnt make much sense but if anyone has any idea how i can make this easier for myself that would be great, thanks!