ranking and creating a dummy variable based on quantiles

[Emad Noureldeen]

Hello, I have this panel data.

I want to rank the variable (REV_CH_mean5) within each industry-year ( Year IndustryCode). Then, firms (Stkcd) with values of the variable in the highest quintile are assigned a score of 5, those in the next quintile receive a score of 4, etc., and those in the lowest quintile are assigned a score of 1.

[Ken Chui]

Please adhere to the FAQ (http://www.statalist.org/forums/help) and use -dataex- to provide sample data in code format, not image. That way users can test their codes and you can directly apply the codes to your data. Back to your question,

I want to rank the variable (REV_CH_mean5) within each industry-year ( Year IndustryCode).

How? Every year x industry combination in the image is of one case, are you asking Stata to split, say, 0.34435677 of 2014 x J66 into quintiles? That does not make sense.

If you would revise the question, it may be helpful to provide that extra column you want, and clearly show other users what you meant by that.

[Emad Noureldeen]

Please adhere to the FAQ (http://www.statalist.org/forums/help) and use -dataex- to provide sample data in code format, not image. That way users can test their codes and you can directly apply the codes to your data. Back to your question,



How? Every year x industry combination in the image is of one case, are you asking Stata to split, say, 0.34435677 of 2014 x J66 into quintiles? That does not make sense.

If you would revise the question, it may be helpful to provide that extra column you want, and clearly show other users what you meant by that.

This is the data

I want to: (1) rank the variable REV_CH_mean5 for each IndustryCode for every Year, (2) for each firms (Stkcd) if the value of the REV_CH_mean5 in the highest quintile is assigned a score of 5, those in the next quintile receive a score of 4, etc., and those in the lowest quintile are assigned a score of 1.


Stkcd Year IndustryCode REV_CH_mean5
000001 2012 J66
000001 2013 J66
000001 2014 J66 .34435677
000001 2015 J66 .3363711
000001 2016 J66 .29436198
000001 2017 J66 .22768084
000001 2018 J66 .16659701
000001 2019 J66 .12997099
000001 2020 J66 .09339697
000002 2012 K70
000002 2013 K70
000002 2014 K70 .27692336
000002 2015 K70 .2916494
000002 2016 K70 .27927022
000002 2017 K70 .19398188
000002 2018 K70 .17643654
000002 2019 K70 .20741073
000002 2020 K70 .16808903
000004 2012 C27
000004 2013 C27
000004 2014 C27 .05395999
000004 2015 C27 .16404707
000004 2016 C27 .40887893
000004 2017 C27 .24387846
000004 2018 C27 .62373706
000004 2019 I65 .46124843
000004 2020 I65 .68070469
000005 2012 K70
000005 2013 K70
000005 2014 K70 .03407238
000005 2015 K70 .17405163
000005 2016 N77 1.0848173
000005 2017 N77 .99221341
000005 2018 N77 1.0706297
000005 2019 N77 1.0952083
000005 2020 N77 .92078776
000006 2012 K70
000006 2013 K70
000006 2014 K70 .06399458
000006 2015 K70 .19030313
000006 2016 .19030313
000006 2017 K70 .1910469
000006 2018 K70 -.02565459
000006 2019 K70 .30115888
000006 2020 K70 .04025306
000007 2012 H61
000007 2013 H61
000007 2014 H61
000007 2015 H61 -.29690075
000007 2016 H61 -.37450503
000007 2017 K70 -.29557107
000007 2018 K70 -.21855691
000007 2019 K70 -.20538729
000007 2020 K70 -.09042276
000008 2012 H61
000008 2013 H61
000008 2014 H61 7.5456902
000008 2015 C37 6.4632673
000008 2016 C37 5.2603276
000008 2017 C37 .76689809
000008 2018 C37 .80328344
000008 2019 C37 .85258093
000008 2020 C37 .12847643
000009 2012 S90
000009 2013 S90
000009 2014 S90 .02001998
000009 2015 S90 .04884327
000009 2016 S90 .10258287
000009 2017 S90 .12386034
000009 2018 S90 .25024243
000009 2019 S90 .24672767
000009 2020 S90 .19732541
000010 2012 C35
000010 2013 C35
000010 2014 C35 .19456465
000010 2015 E48 .93519901
000010 2016 E48 .76824531
000010 2017 E48 .7076551
000010 2018 E48 .41363555
000010 2019 E48 1.382662
000010 2020 E48 .70005185
000011 2012 K70
000011 2013 K70
000011 2014 K70 -.00842403
000011 2015 K70 -.04396885
000011 2016 K70 .14707269
000011 2017 K70 .16490755
000011 2018 K70 .18283167
000011 2019 K70 .31042981
000011 2020 K70 .34775474
000012 2012 C30
000012 2013 C30
000012 2014 C30 -.04591082
000012 2015 C30 -.02072077
000012 2016 C30 .02495797
000012 2017 C30 .09828547
000012 2018 C30 .07218846
000012 2019 C30 .08741385
000012 2020 C30 .08024888
000014 2012 K70
000014 2013 K70
000014 2014 K70 .09994019
000014 2015 K70 .02679245
000014 2016 K70 .1218873
000014 2017 K70 .07647446
000014 2018 K70 -.05173335
000014 2019 K70 .01157497
000014 2020 K70 .00835302
000016 2012 C39
000016 2013 C39
000016 2014 C39 .06419717
000016 2015 C39 .03491247
000016 2016 C39 .04863292
000016 2017 C39 .1301581
000016 2018 C39 .20737841
000016 2019 C39 .25219852
000016 2020 C39 .24548871
000017 2012 C37
000017 2013 C37
000017 2014 C37 -.12660727
000017 2015 C37 -.14338339
000017 2016 C37 -.14864963
000017 2017 C37 -.13738268
000017 2018 C37 -.14812915
000017 2019 C37 -.1777715
000017 2020 C37 -.02897045
000018 2012 C17
000018 2013 C17
000018 2014 C17 .32256416
000018 2015 C17 91.008998
000018 2016 E50 72.839846
000018 2017 E50 72.893112
000018 2018 E50 72.481163
000019 2012 C15
000019 2013 C15
000019 2014 C15 .07560178
000019 2015 C15 .03639764
000019 2016 C15 -.0092238
000019 2017 C15 .02645537
000019 2018 C15 6.5588612
000019 2019 F51 6.5964906
000019 2020 F51 6.627644
000020 2012 C39
000020 2013
000020 2014 C39
000020 2015 C39
000020 2016 C39 -.0716462
000020 2017 C39 .11476265
000020 2018 C39 .02168298
000020 2019 C39 .04387842
000020 2020 C39 .09185287
000021 2012 C39
000021 2013 C39
000021 2014 C39 -.03642522
000021 2015 C39 -.04377384
000021 2016 C39 -.03882909
000021 2017 C39 -.02628639
000021 2018 C39 .01635497
000021 2019 C39 -.03765437
000021 2020 C39 .00187738
000022 2012 G55
000022 2013 G55
000022 2014 G55 .01869112
000022 2015 G55 .02341601
000022 2016 G55 .02220374
000022 2017 G55 .07119544
000023 2012 C30
000023 2013 C30
000023 2014 C30 .11226334
000023 2015 C30 .07171276
000023 2016 C30 .05200431
000023 2017 C30 .0759707
000023 2018 C30 .0945035
000023 2019 C30 .11744519
000023 2020 C30 .12225934
000024 2012 K70
000024 2013 K70
000024 2014 K70 .43119915
000025 2012 F51
000025 2013 F51
000025 2014 F51 .05192183
000025 2015 F51 -.04776027
000025 2016 F51 -.02469999
000025 2017 F51 -.01862889
000025 2018 F51 -.01201092
000025 2019 F51 .0726445
000025 2020 F51 .09064506
000026 2012 F52
000026 2013 F52
000026 2014 F52 .08777471
000026 2015 F52 .05698864
000026 2016 F52 .03494441
000026 2017 F52 .02230573
000026 2018 F52 .02031169
000026 2019 F52 .02692279
000026 2020 F52 .0631111
000027 2012 D44
000027 2013 D44
000027 2014 D44 -.04433199
000027 2015 D44 -.06075688
000027 2016 D44 -.04522492
000027 2017 D44 .0511485
000027 2018 D44 .09695401
000027 2019 D44 .11915577
000027 2020 D44 .13767936
000028 2012 F51
000028 2013 F51
000028 2014 F51 .16578964
000028 2015 F51 .1456203
000028 2016 F51 .23387558
000028 2017 F51 .1958613
000028 2018 F51 .16947462
000028 2019 F51 .18487098
000028 2020 F51 .19706778
000029 2012 K70
000029 2013 K70
000029 2014 K70 .35522523
000029 2015 K70 .27005986
000029 2016 K70 .23348903
000029 2017 K70 .14720501
000029 2018 K70 .05952545
000029 2019 K70 .09237647
000029 2020 K70 .01619375
000030 2012 C41
000030 2013 C41
000030 2014 C36
000030 2015 C36 -.39236285
000030 2016 C36 -.20622991
000030 2017 C36 .10081038
000030 2018 C36 .0989786
000030 2019 C36 .16363054
000030 2020 C36 .21157558
000031 2012 K70
000031 2013 K70
000031 2014 K70 .20106322
000031 2015 K70 .27409106
000031 2016 K70 .28632177
000031 2017 K70 .15536633
000031 2018 K70 .10023502
000031 2019 K70 .40117022
000031 2020 K70 .33011225
000032 2012 C39
000032 2013 C39
000032 2014 C39 .28055322
000032 2015 F51 .12080006
000032 2016 F51 .09839095
000032 2017 F51 .09542427
000032 2018 F51 .06711947
000032 2019 F51 -.12316352
000032 2020 F51 -.04884979
000033 2012 H61
000033 2013 H61
000033 2014 H61 -.02516053
000033 2015 H61 .18612745
000033 2016 H61 .14477105
000034 2012 S90
000034 2013 S90
000034 2014 S90 .05836209
000034 2015 S90 .01872895
000034 2016 F51 17.511716
000034 2017 F51 17.607835
000034 2018 F51 17.637838
000034 2019 F51 17.658927
000034 2020 F51 17.691073
000035 2012 C39
000035 2013 C39
000035 2014 N77 9.4104647
000035 2015 N77 7.1824168
000035 2016 N77 5.7835154
000035 2017 N77 2.6437465
000035 2018 N77 2.7875652
000035 2019 N77 2.1079857
000035 2020 N77 2.0436287
000036 2012 K70
000036 2013 K70
000036 2014 K70 -.23132967
000036 2015 K70 .07466304
000036 2016 K70 .92163553
000036 2017 K70 1.1500024
000036 2018 K70 1.2072608
000036 2019 K70 1.1841605
000036 2020 K70 .95637292
000037 2012 D44
000037 2013 D44
000037 2014 D44 -.16243636
000037 2015 D44 -.09935815
000037 2016 D44 -.04542442
000037 2017 D44 .10974251
000037 2018 D44 .11851948
000037 2019 D44 .02596533
000037 2020 D44 -.03083352
000038 2012 K70
000038 2013 K70
000038 2014 K70 .38826551
000038 2015 K70 .39936105
000038 2016 L72 .79341963
000038 2017 L72 .6704515
000038 2018 L72 .85026664
000038 2019 L72 .73872834
000038 2020 L72 .68439743
000039 2012 C33
000039 2013 C33
000039 2014 C33 .04107094
000039 2015 C33 -.00981657
000039 2016 C33 -.03366581
000039 2017 C33 .09543316
000039 2018 C33 .12748055
000039 2019 C33 .06889949
000039 2020 C33 .12084112
000040 2012 K70
000040 2013 K70
000040 2014 K70 .09057191
000040 2015 K70 .21968345
000040 2016 K70 .42882183
000040 2017 E48 .64912757

[Joro Kolev]

Instal the egenmore user contributed package by typing within Stata

findit egenmore

and then following the instructions to install.

Then use the

egen quintiles = xtile(REV_CH_mean5), by(IndustryCode Year) nq(5)