Generating a dummy equal to 1 for highest decile of a variable in the same industry during year of interest

[David Ye]

Hi everyone!

I recently started using Stata, and I'm finding it quite difficult to solve the following problem:
- I would like to generate a dummy variable that measures whether or not a firm falls in the highest decile of leverage for Compustat firms in the same industry during the year of interest.

Now I've downloaded the whole database of Compustat, so I think I included all firms, and I already created the first variables necessary (i.e., Lev = Leverage; and sic_n2 = industry code)
Next I tried creating the variable to divide Leverage into deciles with the following code: "xtile Lev_dec = Lev, nq(10)"
But this variable does not account for industry and year. I've also tried to use the 'by' function to do this per industry, but Stata gives an error code for that.

Below is an example of my sample:

* Example generated by -dataex-. For more info, type help dataex
clear
input str70 conm double fyear byte sic_n2 float Lev
"A.A. IMPORTING CO INC" 1987 57  1.098914
"A.A. IMPORTING CO INC" 1988 57 -84.91753
"A.A. IMPORTING CO INC" 1989 57 -25.30048
"AAR CORP"              1987 50  .8933799
"AAR CORP"              1988 50 1.0771127
"AAR CORP"              1989 50 1.0497235
"AAR CORP"              1990 50  .9607902
"AAR CORP"              1991 50 1.0095407
"AAR CORP"              1992 50  .9298104
"ABS INDUSTRIES INC"    1988 34  3.291347
"ABS INDUSTRIES INC"    1989 34  4.286525
"ABS INDUSTRIES INC"    1990 34  4.163686
"ABS INDUSTRIES INC"    1991 34 4.4388194
"ABS INDUSTRIES INC"    1992 34 4.5708027
"ABS INDUSTRIES INC"    1993 34  5.152375
"ACF INDUSTRIES INC"    1988 37 16.017115
"ACF INDUSTRIES INC"    1989 37   5.12465
"ACF INDUSTRIES INC"    1990 37  7.206721
"ACF INDUSTRIES INC"    1991 37  4.067188
"ACF INDUSTRIES INC"    1992 37 4.3741674
end

The whole sample exists of over 60,000 observations, where the industry code (sic_n2) varies from 1 to 99.
So my question is, how can I create a dummy that indicates whether the firm is among the highest decile of leverate, by year and industry as explained above.
I think I need a variable to account for leverage first, and then generate the dummy. But I'm not sure how.

I'm quite new to Stata and the code-language, so any help is appreciated.

[Nick Cox]

ssc install egenmore 

egen  Lev_dec = xtile(Lev), nq(10) by(sic_n2 year) 

gen highest10 = Lev_dec == 10

[David Ye]

I've found and tried the following code:
- To generate leverage per industry per year
bysort sic_n2 fyear (Lev) : gen Lev_d = ceil(10 * _n/_N)

- to generate dummy that's equal to 1 if it belongs to the highest decile on leverage per industry per year
generate High_Lev = (Lev_d==10) if !missing(Lev_d)

But as I'm really new to the code-language, can anyone explain what this code did? Did it do what I needed, especially the first one.

Thank you in advance!!

[David Ye]

ssc install egenmore

egen Lev_dec = xtile(Lev), nq(10) by(sic_n2 year)

gen highest10 = Lev_dec == 10

Thank you Nick!
I think this code worked better than mine that I just posted.