I have a data set of American companies over the period 1985-2015 with circa 300k observations of leverage-ratios. So every company has 30 observations.

For a figure I have to construct the average leverage ratios of four portfolios in 'event time' . So what I want to do is the following: each year, I sort firms into quartiles (creating four portfolios) according to their leverage-ratio. The portfolio is denoted event year 0 and I want to compute the average leverage for each portfolio in each of the following 20 years. I want to do this by creating portfolios for year=1985 and so on. How can I follow the different portfolios over the years and compute their average leverage-ratios?

To illustrate, from one year I can derive the 4 quartiles and their average leverage. The next step would be to isolate each quartile (each group of firms) and follow particular group and see how their average leverage develops over time. Can anyone help me with isolating a quartile and filtering the data in a way that you can observe the leverage of these firms in t+1, t+2, etc.

Please let me know if my issue is not clear.

Thanks in advance,

Koen van Hanegem