Ranking in descending order

[Kwadwo Boateng]

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(clientid partnerid year) byte indyear double industry_share_ep    byte    spec
1081    7 2014  7 .0048005590215325356 7
1324   15 2014  7  .005018132273107767 5
628   33 2014  7 .0036202515475451946 9
1103   41 2014  7  .012695363722741604 2
792   41 2014  7  .012695363722741604 2
504   46 2014  7  .006923461798578501 4
617   48 2014  7  .004891650751233101 6
1203   48 2014  7  .004891650751233101 6
1054   54 2014  7  .020323878154158592 1
487   58 2014  7  .004543224349617958 8
74   59 2014  7  .007345101330429316 3
1279    4 2015  8  .004082599189132452 .
1081    7 2015  8  .005235201679170132 .
628   33 2015  8  .003987567033618689 .
792   41 2015  8  .012957603670656681 .
1103   41 2015  8  .012957603670656681 .
1203   48 2015  8  .005978947039693594 .
617   48 2015  8  .005978947039693594 .
627   58 2015  8  .017269672825932503 .
1096   58 2015  8  .017269672825932503 .
504   58 2015  8  .017269672825932503 .
487   58 2015  8  .017269672825932503 .
599   58 2015  8  .017269672825932503 .
74   59 2015  8  .007718401029706001 .
1165   66 2015  8  .005468184128403664 .
190   86 2015  8   .03738570585846901 .
453   86 2015  8   .03738570585846901 .
526   88 2015  8  .005846200976520777 .
490 1089 2016  9   .02829659730195999 .
253 1089 2016  9   .02829659730195999 .
997 1093 2016  9  .008093434385955334 .
1490 1093 2016  9  .008093434385955334 .
1081 1100 2016  9 .0054756589233875275 .
948 1109 2016  9  .020321520045399666 .
893 1110 2016  9  .005612920969724655 .
1124 1148 2016  9                    0 .
1181 1153 2016  9 .0053636180236935616 .
1260 1164 2016  9                    0 .
1457 1192 2016  9   .01999679021537304 .
868 1205 2016  9  .020531661808490753 .
206 1210 2016  9  .005854192189872265 .
1230 1230 2016  9  .009276914410293102 .
806 1230 2016  9  .009276914410293102 .
724   19 2017 10  .005364460404962301 .
628   33 2017 10  .004450527019798756 .
436   43 2017 10  .004119019955396652 .
617   48 2017 10  .005194490309804678 .
1203   48 2017 10  .005194490309804678 .
490   52 2017 10  .030522581189870834 .
821   52 2017 10  .030522581189870834 .
285   54 2017 10  .024143129587173462 .
633   57 2017 10 .0068115307949483395 .
751   58 2017 10   .01648836024105549 .
1096   58 2017 10   .01648836024105549 .
627   58 2017 10   .01648836024105549 .
487   58 2017 10   .01648836024105549 .
1165   66 2017 10  .006617478094995022 .
190   86 2017 10   .03865887597203255 .
453   86 2017 10   .03865887597203255 .
994    5 2014 18  .006885720416903496 .
1112   11 2014 18  .016296733170747757 .
478   12 2014 18  .004438069649040699 .
918   22 2014 18  .005527980625629425 .
337   22 2014 18  .005527980625629425 .
49   59 2014 18  .007213207893073559 .
1301   66 2014 18  .005558209028095007 .
1384   84 2014 18  .012285162694752216 .
1133  171 2015 19  .017234930768609047 .
424  191 2015 19  .020662015303969383 .
1164  211 2015 19                    0 .
1162  235 2015 19  .016075843945145607 .
1449  266 2015 19  .016308801248669624 .
318  278 2015 19  .004750378429889679 .
105  289 2015 19   .01624026708304882 .
1561  293 2015 19  .007419430650770664 .
21  306 2015 19  .006908397190272808 .
113  312 2015 19  .017234930768609047 .
352  315 2015 19  .005803853273391724 .
1010  318 2015 19  .005676507484167814 .
279  319 2015 19  .005354285705834627 .
end

Hi All

I kindly request assistance for the above. I am looking to generate a code such that for each 'indyear' : the 'partnerid' with the highest 'industry_share_ep' will have a ranking of 1, and the next 2, in descending order as demonstrated in 'Spec'.

NB: The rankings in spec were done manually to give an idea of what i am seeking to achieve with the code.

[Nick Cox]

Rank on negative share, as explained, IIRC, in

help egen

[ericmelse]

Dear Kwadko,
Here is my suggestion.

* To inspect the desired sort order
gsort indyear -industry_share_ep
* Standard rank coding
bysort indyear: egen rank1 = rank(industry_share_ep), field
* Alternative rank coding (i.e. with sequential values)
* Code based on that from Nick Cox https://www.stata.com/statalist/archive/2008-12/msg00596.html
bysort indyear (industry_share_ep) : gen rank2 = industry_share_ep != industry_share_ep[_n-1]
by indyear : replace rank2 = sum(rank2)
* To invert rank2
gen rank3=.
egen newid = group(indyear)    // auxiliary variable for forvalue 
qui sum newid                  // to get the maximum for forvalue
forvalue i = 1(1)`r(max)'    {
    qui sum rank2 if newid==`i'
    local param=`r(max)'+1
    replace rank3=`param'-rank2 if newid==`i'
}

Most likely a more simple/smart solution is possible as to get the rank inversion.

[Nick Cox]

Here is #2 exemplified:

. sysuse auto, clear
(1978 Automobile Data)

. egen rank1 = rank(mpg)

. egen rank2 = rank(-mpg)

. sort mpg

. list mpg rank? in 1/5

     +---------------------+
     | mpg   rank1   rank2 |
     |---------------------|
  1. |  12     1.5    73.5 |
  2. |  12     1.5    73.5 |
  3. |  14     5.5    69.5 |
  4. |  14     5.5    69.5 |
  5. |  14     5.5    69.5 |
     +---------------------+

. list mpg rank? in -5/L

     +---------------------+
     | mpg   rank1   rank2 |
     |---------------------|
 70. |  31      70       5 |
 71. |  34      71       4 |
 72. |  35    72.5     2.5 |
 73. |  35    72.5     2.5 |
 74. |  41      74       1 |
     +---------------------+

Just add a by() option to get either ranking separately within a group.

[Kwadwo Boateng]

Thank you ericmelse and Nick Cox . Great coding. I lean more toward Eric's code as it aligns more with my objective.

[Nick Cox]

Looking more carefully at #1 I see that I was misled by focusing on the wording of ranking in descending order. Your example makes clear that you don't want ranking at all, but a grouping in which tied values get the same rank, yet the next lower group gets a value higher by 1, regardless. That doesn't correspond to any sense of ranking supported by egen.

That being so, the result can be achieved a little more directly than in #3.

clear
input int(clientid partnerid year) byte indyear double industry_share_ep    byte    spec
1081    7 2014  7 .0048005590215325356 7
1324   15 2014  7  .005018132273107767 5
628   33 2014  7 .0036202515475451946 9
1103   41 2014  7  .012695363722741604 2
792   41 2014  7  .012695363722741604 2
504   46 2014  7  .006923461798578501 4
617   48 2014  7  .004891650751233101 6
1203   48 2014  7  .004891650751233101 6
1054   54 2014  7  .020323878154158592 1
487   58 2014  7  .004543224349617958 8
74   59 2014  7  .007345101330429316 3
end


bysort indyear (industry_share) : gen wanted = sum(industry_share != industry_share[_n-1])
by indyear : replace wanted = wanted[_N] + 1 - wanted

sort spec 
list, sep(0)

     +------------------------------------------------------------------+
     | clientid   partne~d   year   indyear   industr~p   spec   wanted |
     |------------------------------------------------------------------|
  1. |     1054         54   2014         7   .02032388      1        1 |
  2. |      792         41   2014         7   .01269536      2        2 |
  3. |     1103         41   2014         7   .01269536      2        2 |
  4. |       74         59   2014         7    .0073451      3        3 |
  5. |      504         46   2014         7   .00692346      4        4 |
  6. |     1324         15   2014         7   .00501813      5        5 |
  7. |     1203         48   2014         7   .00489165      6        6 |
  8. |      617         48   2014         7   .00489165      6        6 |
  9. |     1081          7   2014         7   .00480056      7        7 |
 10. |      487         58   2014         7   .00454322      8        8 |
 11. |      628         33   2014         7   .00362025      9        9 |
     +------------------------------------------------------------------+

[Kwadwo Boateng]

Hi Nick Cox , thanks a million. This is even more straight forward. Great coding. Thank you once again for coming through for people like us when you are under no obligation to. Keep making this forum the best place.