Problems creating dummy variables with two-digit sic code

[Qi vd Kolk]

Hi all,

I have a panel data set and want to create dummy variables from the variables CEO_compensation, CEO_tenure, CEO_Ownership.
The dummy variables should look like the following:
CEO_Ownership = the percentage of company stock held by the CEO related to industry median, with 1= value above the median and 0= value underneath the median
CEO_Tenure = the number of years the CEO has been in office related to industry median, with 1 = value above the median and 0 = value underneath the median
CEO_Compensation = the total annual compensation that the CEO receives related to the industry median, with 1 = value above the median and 0 = value underneath the median

In order to create the dummy variables I think I need to use the two-digit sic codes of the firms in my panel dataset.
However, I don't really know how I should make the described dummy variables with the two-digit sic codes of the firms in my panel dataset where 1 = value above the industry median and 0 = value underneath the industry median.

Can someone please help me with this?
I'm using Stata 17 on a macOs Catalina.

Below a sample of my panel dataset.

* Example generated by -dataex-. For more info, type help dataex
clear
input str28 CONAME float sic2 double(CEO_Tenure CEO_Ownership) float CEO_Compensation
"SKYWORKS SOLUTIONS INC"    36   .3   .045   2.1261234
"SKYWORKS SOLUTIONS INC"    36  1.3   .032    6.399716
"SKYWORKS SOLUTIONS INC"    36  2.3   .054    5.002587
"SKYWORKS SOLUTIONS INC"    36  3.3   .045    5.422685
"SKYWORKS SOLUTIONS INC"    36  4.4   .032    9.071857
"ENERPAC TOOL GROUP CORP"   34   .4   .068    1.459483
"ENERPAC TOOL GROUP CORP"   34  1.4   .067   2.3261087
"ENERPAC TOOL GROUP CORP"   34  2.4   .077    2.869191
"ENERPAC TOOL GROUP CORP"   34  3.4   .169    .9573826
"ENERPAC TOOL GROUP CORP"   34  4.4   .238     3.78773
"BRADY CORP"                35  1.9    .04   1.8297285
"BRADY CORP"                35  2.9   .052    2.261716
"BRADY CORP"                35  3.9   .071    4.919281
"BRADY CORP"                35  4.9   .216    4.129367
"BRADY CORP"                35  5.9   .075   2.7901645
"MATERION CORP"             32 10.6   .605   2.2802799
"MATERION CORP"             32   .8   .035    2.465549
"MATERION CORP"             32  1.8   .034    1.669286
"MATERION CORP"             32  2.8   .114    3.028718
"MATERION CORP"             32  3.8   .217   1.4571295
"CTS CORP"                  38  2.6    .62   2.7161105
"CTS CORP"                  38  3.6   .733    3.189365
"CTS CORP"                  38  4.6   .893   3.0202866
"CTS CORP"                  38  5.6  1.032   2.0765357
"CTS CORP"                  38  6.6  1.119    1.946734
"CABOT CORP"                28   .5   .065   1.3949307
"CABOT CORP"                28  1.5   .075    2.071881
"CABOT CORP"                28  2.5   .111   3.0512674
"CABOT CORP"                28  3.5   .172   2.0461328
"CABOT CORP"                28  4.5   .213    1.771463
"CARPENTER TECHNOLOGY CORP" 33   .9    .01   1.1385132
"CARPENTER TECHNOLOGY CORP" 33  1.9   .017   2.4910305
"CARPENTER TECHNOLOGY CORP" 33  2.9   .087   2.4929926
"CARPENTER TECHNOLOGY CORP" 33  3.9   .332    1.311708
"CARPENTER TECHNOLOGY CORP" 33  4.9   .444    1.472514
"CHURCH & DWIGHT INC"       28   .9    .04    1.466543
"CHURCH & DWIGHT INC"       28  1.9   .057   1.2702277
"CHURCH & DWIGHT INC"       28  2.9   .057   1.2011595
"CHURCH & DWIGHT INC"       28   .6    .06   1.4754583
"CHURCH & DWIGHT INC"       28  1.6    .05    9.527443
"CHURCHILL DOWNS INC"       79  2.3   .811    2.823925
"CHURCHILL DOWNS INC"       79  3.3   1146    3.969882
"CHURCHILL DOWNS INC"       79  4.3   1312   .01162713
"CHURCHILL DOWNS INC"       79  5.3   1375    5.845542
"CHURCHILL DOWNS INC"       79  6.3   1434    6.034752
"COHERENT INC"              38   14   .739   1.9130802
"COHERENT INC"              38 14.9   .507     3.60647
"COHERENT INC"              38 15.9   .659    3.498751
"COHERENT INC"              38 16.9    .77    1.897766
"COHERENT INC"              38   .5    .01    2.639609
"COHU INC"                  38    2   .532    2.067652
"COHU INC"                  38    3   .599   3.9334884
"COHU INC"                  38    4   .476     2.31893
"COHU INC"                  38    5   .585   1.7210625
"COHU INC"                  38    6   .491    3.060477
"SHUTTERSTOCK INC"          73  4.2 46.594 .0004598519
"SHUTTERSTOCK INC"          73  5.2 46.112 .0007237075
"SHUTTERSTOCK INC"          73  6.2 45.505 .0007305514
"SHUTTERSTOCK INC"          73  7.2    .01   3.0239005
"SHUTTERSTOCK INC"          73   .7   .114    5.847939
"COOPER COS INC (THE)"      38  8.6   .069    1.580538
"COOPER COS INC (THE)"      38  9.6   .072     .486073
"COOPER COS INC (THE)"      38   .4   .075   .58844715
"COOPER COS INC (THE)"      38  1.4   .079    .4936425
"COOPER COS INC (THE)"      38  2.4   .084    .4259431
"CORNING INC"               33    6   .084   2.8053675
"CORNING INC"               33    7    .09    4.896355
"CORNING INC"               33    8   .104    5.518189
"CORNING INC"               33    9   .113    2.978277
"CORNING INC"               33  4.3   .098   4.1963925
"CRANE CO"                  34  2.9   .206    2.412608
"CRANE CO"                  34  3.9   .219   2.8341014
"CRANE CO"                  34  4.9   .366    3.057879
"CRANE CO"                  34  5.9    .39   2.2673922
"CRANE CO"                  34  6.9   .398    2.391373
"CURTISS-WRIGHT CORP"       36  1.9   .321   3.3210266
"CURTISS-WRIGHT CORP"       36  2.9   .244    2.668044
"CURTISS-WRIGHT CORP"       36  3.9   .238   4.3744555
"CURTISS-WRIGHT CORP"       36  4.9   .181    3.430234
"CURTISS-WRIGHT CORP"       36  5.9   .193    3.033546
"DANA INC"                  37  1.3   .039    6.270573
"DANA INC"                  37  2.3   .058    6.541174
"DANA INC"                  37  3.3   .253    4.860137
"DANA INC"                  37   .6   .351    4.736923
"DANA INC"                  37    1   .418    4.886946
"DIEBOLD NIXDORF INC"       35   .3   .275   2.1920264
"DIEBOLD NIXDORF INC"       35    1   .272   2.5127764
"DIEBOLD NIXDORF INC"       35   .8   .075   2.2765641
"DIEBOLD NIXDORF INC"       35  1.8   .218   1.6850197
"DIEBOLD NIXDORF INC"       35  2.8   .207   1.5061302
"DONALDSON CO INC"          35  1.3   .058    .6653286
"DONALDSON CO INC"          35  2.3   .063   2.2807095
"DONALDSON CO INC"          35   .7   .089    2.433109
"DONALDSON CO INC"          35  1.7   .139    2.872298
"DONALDSON CO INC"          35  2.7   .147   1.7731953
"EMERSON ELECTRIC CO"       38  7.1   .356   1.1946176
"EMERSON ELECTRIC CO"       38  8.1   .384    7.995649
"EMERSON ELECTRIC CO"       38  9.1   .394     6.61639
"EMERSON ELECTRIC CO"       38 10.1    .42    6.365517
"EMERSON ELECTRIC CO"       38 11.1   .451    6.681705
end

Thanks in advance!

[Nick Cox]

You can use egen, median() to get the medians. The issue here quite what your groups are to be. You say you have panel data but there is no time variable evident in your data example yet numerous duplicates for each firm.

Here is a trivial panel dataset example for above and below median investment in a particular year.

webuse grunfeld, clear 
egen median = median(invest), by(year) 
gen byte abmedian = invest > median if invest < .

In your case the major issue is deciding on the argument for by().

[Qi vd Kolk]

Dear Nick,

I'm sorry I forgot to include my variable year in the sample I made with dataex.
Below I have my sample again.

* Example generated by -dataex-. For more info, type help dataex
clear
input str28 conm int year str9 cusip long siccode float CEO_Compensation double(CEO_Ownership CEO_Tenure)
"SKYWORKS SOLUTIONS INC"    2016 "83088M102" 3674   2.1261234   .045   .3
"SKYWORKS SOLUTIONS INC"    2017 "83088M102" 3674    6.399716   .032  1.3
"SKYWORKS SOLUTIONS INC"    2018 "83088M102" 3674    5.002587   .054  2.3
"SKYWORKS SOLUTIONS INC"    2019 "83088M102" 3674    5.422685   .045  3.3
"SKYWORKS SOLUTIONS INC"    2020 "83088M102" 3674    9.071857   .032  4.4
"ENERPAC TOOL GROUP CORP"   2016 "292765104" 3492    1.459483   .068   .4
"ENERPAC TOOL GROUP CORP"   2017 "292765104" 3492   2.3261087   .067  1.4
"ENERPAC TOOL GROUP CORP"   2018 "292765104" 3492    2.869191   .077  2.4
"ENERPAC TOOL GROUP CORP"   2019 "292765104" 3492    .9573826   .169  3.4
"ENERPAC TOOL GROUP CORP"   2020 "292765104" 3492     3.78773   .238  4.4
"BRADY CORP"                2016 "104674106" 3577   1.8297285    .04  1.9
"BRADY CORP"                2017 "104674106" 3577    2.261716   .052  2.9
"BRADY CORP"                2018 "104674106" 3577    4.919281   .071  3.9
"BRADY CORP"                2019 "104674106" 3577    4.129367   .216  4.9
"BRADY CORP"                2020 "104674106" 3577   2.7901645   .075  5.9
"MATERION CORP"             2016 "576690101" 3264   2.2802799   .605 10.6
"MATERION CORP"             2017 "576690101" 3264    2.465549   .035   .8
"MATERION CORP"             2018 "576690101" 3264    1.669286   .034  1.8
"MATERION CORP"             2019 "576690101" 3264    3.028718   .114  2.8
"MATERION CORP"             2020 "576690101" 3264   1.4571295   .217  3.8
"CTS CORP"                  2016 "126501105" 3823   2.7161105    .62  2.6
"CTS CORP"                  2017 "126501105" 3823    3.189365   .733  3.6
"CTS CORP"                  2018 "126501105" 3823   3.0202866   .893  4.6
"CTS CORP"                  2019 "126501105" 3823   2.0765357  1.032  5.6
"CTS CORP"                  2020 "126501105" 3823    1.946734  1.119  6.6
"CABOT CORP"                2016 "127055101" 2895   1.3949307   .065   .5
"CABOT CORP"                2017 "127055101" 2895    2.071881   .075  1.5
"CABOT CORP"                2018 "127055101" 2895   3.0512674   .111  2.5
"CABOT CORP"                2019 "127055101" 2895   2.0461328   .172  3.5
"CABOT CORP"                2020 "127055101" 2895    1.771463   .213  4.5
"CARPENTER TECHNOLOGY CORP" 2016 "144285103" 3312   1.1385132    .01   .9
"CARPENTER TECHNOLOGY CORP" 2017 "144285103" 3312   2.4910305   .017  1.9
"CARPENTER TECHNOLOGY CORP" 2018 "144285103" 3312   2.4929926   .087  2.9
"CARPENTER TECHNOLOGY CORP" 2019 "144285103" 3312    1.311708   .332  3.9
"CARPENTER TECHNOLOGY CORP" 2020 "144285103" 3312    1.472514   .444  4.9
"CHURCH & DWIGHT INC"       2016 "171340102" 2841    1.466543    .04   .9
"CHURCH & DWIGHT INC"       2017 "171340102" 2841   1.2702277   .057  1.9
"CHURCH & DWIGHT INC"       2018 "171340102" 2841   1.2011595   .057  2.9
"CHURCH & DWIGHT INC"       2019 "171340102" 2841   1.4754583    .06   .6
"CHURCH & DWIGHT INC"       2020 "171340102" 2841    9.527443    .05  1.6
"CHURCHILL DOWNS INC"       2016 "171484108" 7948    2.823925   .811  2.3
"CHURCHILL DOWNS INC"       2017 "171484108" 7948    3.969882   1146  3.3
"CHURCHILL DOWNS INC"       2018 "171484108" 7948   .01162713   1312  4.3
"CHURCHILL DOWNS INC"       2019 "171484108" 7948    5.845542   1375  5.3
"CHURCHILL DOWNS INC"       2020 "171484108" 7948    6.034752   1434  6.3
"COHERENT INC"              2016 "192479103" 3826   1.9130802   .739   14
"COHERENT INC"              2017 "192479103" 3826     3.60647   .507 14.9
"COHERENT INC"              2018 "192479103" 3826    3.498751   .659 15.9
"COHERENT INC"              2019 "192479103" 3826    1.897766    .77 16.9
"COHERENT INC"              2020 "192479103" 3826    2.639609    .01   .5
"COHU INC"                  2016 "192576106" 3825    2.067652   .532    2
"COHU INC"                  2017 "192576106" 3825   3.9334884   .599    3
"COHU INC"                  2018 "192576106" 3825     2.31893   .476    4
"COHU INC"                  2019 "192576106" 3825   1.7210625   .585    5
"COHU INC"                  2020 "192576106" 3825    3.060477   .491    6
"SHUTTERSTOCK INC"          2016 "825690100" 7374 .0004598519 46.594  4.2
"SHUTTERSTOCK INC"          2017 "825690100" 7374 .0007237075 46.112  5.2
"SHUTTERSTOCK INC"          2018 "825690100" 7374 .0007305514 45.505  6.2
"SHUTTERSTOCK INC"          2019 "825690100" 7374   3.0239005    .01  7.2
"SHUTTERSTOCK INC"          2020 "825690100" 7374    5.847939   .114   .7
"COOPER COS INC (THE)"      2016 "216648402" 3851    1.580538   .069  8.6
"COOPER COS INC (THE)"      2017 "216648402" 3851     .486073   .072  9.6
"COOPER COS INC (THE)"      2018 "216648402" 3851   .58844715   .075   .4
"COOPER COS INC (THE)"      2019 "216648402" 3851    .4936425   .079  1.4
"COOPER COS INC (THE)"      2020 "216648402" 3851    .4259431   .084  2.4
"CORNING INC"               2016 "219350105" 3357   2.8053675   .084    6
"CORNING INC"               2017 "219350105" 3357    4.896355    .09    7
"CORNING INC"               2018 "219350105" 3357    5.518189   .104    8
"CORNING INC"               2019 "219350105" 3357    2.978277   .113    9
"CORNING INC"               2020 "219350105" 3357   4.1963925   .098  4.3
"CRANE CO"                  2016 "224399105" 3492    2.412608   .206  2.9
"CRANE CO"                  2017 "224399105" 3492   2.8341014   .219  3.9
"CRANE CO"                  2018 "224399105" 3492    3.057879   .366  4.9
"CRANE CO"                  2019 "224399105" 3492   2.2673922    .39  5.9
"CRANE CO"                  2020 "224399105" 3492    2.391373   .398  6.9
"CURTISS-WRIGHT CORP"       2016 "231561101" 3694   3.3210266   .321  1.9
"CURTISS-WRIGHT CORP"       2017 "231561101" 3694    2.668044   .244  2.9
"CURTISS-WRIGHT CORP"       2018 "231561101" 3694   4.3744555   .238  3.9
"CURTISS-WRIGHT CORP"       2019 "231561101" 3694    3.430234   .181  4.9
"CURTISS-WRIGHT CORP"       2020 "231561101" 3694    3.033546   .193  5.9
"DANA INC"                  2016 "235825205" 3714    6.270573   .039  1.3
"DANA INC"                  2017 "235825205" 3714    6.541174   .058  2.3
"DANA INC"                  2018 "235825205" 3714    4.860137   .253  3.3
"DANA INC"                  2019 "235825205" 3714    4.736923   .351   .6
"DANA INC"                  2020 "235825205" 3714    4.886946   .418    1
"DIEBOLD NIXDORF INC"       2016 "253651103" 3578   2.1920264   .275   .3
"DIEBOLD NIXDORF INC"       2017 "253651103" 3578   2.5127764   .272    1
"DIEBOLD NIXDORF INC"       2018 "253651103" 3578   2.2765641   .075   .8
"DIEBOLD NIXDORF INC"       2019 "253651103" 3578   1.6850197   .218  1.8
"DIEBOLD NIXDORF INC"       2020 "253651103" 3578   1.5061302   .207  2.8
"DONALDSON CO INC"          2016 "257651109" 3564    .6653286   .058  1.3
"DONALDSON CO INC"          2017 "257651109" 3564   2.2807095   .063  2.3
"DONALDSON CO INC"          2018 "257651109" 3564    2.433109   .089   .7
"DONALDSON CO INC"          2019 "257651109" 3564    2.872298   .139  1.7
"DONALDSON CO INC"          2020 "257651109" 3564   1.7731953   .147  2.7
"EMERSON ELECTRIC CO"       2016 "291011104" 3823   1.1946176   .356  7.1
"EMERSON ELECTRIC CO"       2017 "291011104" 3823    7.995649   .384  8.1
"EMERSON ELECTRIC CO"       2018 "291011104" 3823     6.61639   .394  9.1
"EMERSON ELECTRIC CO"       2019 "291011104" 3823    6.365517    .42 10.1
"EMERSON ELECTRIC CO"       2020 "291011104" 3823    6.681705   .451 11.1
end

I tried your code:
egen median = median (CEO_Compensation), by (year)
gen byte abmedian = CEO_Compensation > median if CEO_Compensation < median

However, this does not give me the industry median, and I believe that I need to do something with the two digit-sic code that every firm has in my panel dataset, in order to create a dummy with 1 = the total annual compensation that the CEO of a firm receives is above to the industry median and the 0 = the total annual compensation that the CEO of a firm receives is below to the industry median. Can you please help me?

[Nick Cox]

The code you give starts out like mine but then becomes something utterly different.

gen byte abmedian = CEO_Compensation > median if CEO_Compensation < median 

is never going to be true but will return 0 for the included cases -- if the value is less than the median it can't be more than the median -- and missing otherwise.

Let's go through that again.

Values above the median: returned as missing, because that's what the if qualifier implies.

Values equal to the median: same story, if they exist and they will do sometimes.

Values below the median: by definition none of them is above the median, so 0.

Also, the more general point in #2 remains true. You have to decide what belongs in by(). I just gave an example of syntax, and with the Grunfeld data by(company) might be what you want and in your dataset

by(year) 

by(SIC year) 

by(SIC)

might all make sense depending on the problem. Naturally, use your own variable names if different.

There is more on indicator variables including logic for true and false (when missings are present) in https://www.stata-journal.com/articl...article=dm0099