Calculating top percentile for each year and industry

[Haavard Fidjeland]

I have a large data-set that contains financial and industry information for companies in 12 different industries, over 22 years. I want to use Stata to give me the 90% percentile value for profits in each of 12 different industries, for each of the 22 different years.

To clarify, i want to determine what profits would be needed to be within the top 10% profitable firms in a given industry.

My approach so far has been to divide the data into subsets of years, and then again by industry, and finally calculate the percentile in income. However, this process is very slow, as it has to be repeated 264 times, and i imagine there has to be a better approach.

Does anyone have any tips or ideas?

[Rich Goldstein]

I assume you have a variable which I will call "profit" that contains what you want; in that case:

collapse (p90) profit, by(industry year)

[Haavard Fidjeland]

Yes, this is perfect! Thank you so much!

[Haavard Fidjeland]

Another question;

I want to get an overview of all the percentiles from 1-99. My goal is to create a 100 by 100 matrix of percentiles for each industry, and map the movement between the different percetniles from year to year.

I tried to use the same method as described above, but i keep getting syntaxt errors when i try to enter more than one percentile cut-off.

I also tried to use the xtile option, but i was unable to use the by(Industry year) option.

[Oded Mcdossi]

You can use the egen pctile within a loop and then create data of unique industry and year, something like:

forval i=1/99 {
bys industry year: egen pc_`i'=pctile(profit), p(`i')
}

bys industry year: keep if _n==1