How to rank value in each specific date of each firms

[Tan Nguyen]

Hi all,
I have data as follow (from 04Jan2012 to 31Dec2021)

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(date r_AAM r_ACC r_ADC r_BED)
18266            .           . .           .
18267   .023464635  .018019632 .  .031747144
18268   -.01460196  -.03636988 .  .024692865
18269    .04879335  -.07696043 . -.018462187
18270            0  -.04082457 .           .
18273  -.005621055           0 .           .
18274   -.04317229  -.08701079 .  -.06187596
18275            0    .0828399 .   .03484704
18276   .008784819           . .   .05981911
18277    .00581714           . .           .
18280   -.05053411           . .           .
18281    .03298979   .05406645 .           .
18282   -.02388494           . .   .05981911
18283   -.02445952           . .  .019170104
18284            0           . .           .
18287   .009245082           . .           .
18288    .03909938           . .           .
18289  -.002951047           . .           .
18290  -.011911284           . .  .066692024
18291  -.027315874           . . -.032790184
18294   .015270777           . .           .
18295   .015041084           . .           .
18296            0           . .           .
18297   .002977435           . .           .
18298   -.04255901           . .  -.09193846
18301    .02454049           . .  -.06108606
18302  -.027651504           . .           .
18303    .02461985           . .           .
18304  .0030316524           . .   .06453909
18305   .003022489           . .           .
18315   .012018595           . .           .
18316   -.03031186           . .  .064022414
18317            0  .015213837 .  .030534806
18318   .003078826  -.01905123 .           .
18319            0           . .           .
18322    .00610959           . .  .014388928
18323   .003050709   .01869027 . -.021661734
18324   .009077149  .007377322 .  .021661734
18325            0   .09129516 .   .06230445
18326            0   .09286636 .           .
18329    .03840625           . .           .
18330    .04810279           . .           .
18331   .019149406           . .  .006307384
18332  -.010896324           . .  .018692346
18333   .008185525           . .           .
18336  -.013683024   .09286636 .           .
18337  -.027931724           . . -.025157066
18338  -.008534839           . . -.065813765
18339   .014178528  -.09642832 .   .04000565
18340  -.014178528  -.10672422 . -.033225928
18343  -.002867404   .09437885 .           .
18344   -.02612636  -.10467136 .           .
18345    .01460196   -.1053605 .   .01980283
18346  -.029410666  -.10493392 .           .
18347 -.0029959874   .17520665 .           .
18350   .017804695   .09530683 . -.006644671
18351  -.014808708           . .  -.06899347
18352 -.0029959874           . .           .
18353            0           . .           .
18354    -.0029953   .10897366 .           .
18357            0           . .   .06021948
18358            0           . .    .0462122
18359   .008968723  -.07755852 .  .019169083
18360   .008888909           . .           .
18361   .008810681           . .           .
18364  -.005868317           0 .           0
18365  -.008862676   .04725223 . -.013423044
18366   -.03487077   .08829147 .           .
18367  -.009310748    .0863075 .           .
18368  .0031132565   .05529019 .   .06230335
18371   .015412525 -.073543854 .    .0522998
18372   .009130891           0 .    .0251587
18373    .01504165  .013074178 .  .024541244
18374 -.0029922444           0 .  -.05609065
18378            0   .01290214 .  -.05942295
18379            0  -.01290214 .   .02020378
18380            0    .0256432 .   .02631744
18381            0   .01257746 .    .0256408
18386   .026584873 -.010049932 . -.006348364
18387    .04838032   .02247124 .   -.0522998
18388    .04879016   .03637025 .    .0459091
18389  -.035002224   .05781673 .           0
18392   -.04482058 -.034288626 .  .006390703
18393    -.0144339   .09531045 .   .01265851
18394  -.035510033  -.08374777 .  -.05826838
18395            0  -.04706888 .           0
18396   .006011392  .035506207 .  -.02020378
18399  .0029922444 -.072319664 .  .006780733
18400  -.009003636   .08584152 .  -.05557024
18401  -.021309374  -.08655488 .           0
18402   .021309374 -.013794912 .           .
18403   -.04938954  -.08398309 .           .
18406   .012578953     .097778 .  .035090618
18407  -.025318153  -.04196582 .  .013698883
18408   .006390422  .028170906 .           0
18409            0   .08515901 . -.013698883
18410   .031354383     .094847 .   .03390266
18413  -.015556324   .09509932 .  -.06899328
18414  .0031296716    .0945423 .           .
18415    .00933266  -.10514814 .           .
end
format %td date

So I want to rank return value of each firm month by month.
Eg: I want to rank return value of AAM from 04Jan2010-31Jan2010, from 01Feb2010-28Feb2010,....., from 01Dec2021-31Dec2021; Rank value of ACC from 04Jan2010-31Jan2010, from 01Feb2010-28Feb2010,....., from 01Dec2021-31Dec2021, and return value of ADC and BED like that.
I have tried several ways but can't seem to make it work. Can anyone tell me how you would code this?
Many thanks and cheers.

[Carlo Lazzaro]

Tan:
welcome to this forum.
Provided that I got you right, a possible trick is:

. bysort date: g wanted=1

. drop wanted

. list in 1/10

     +-------------------------------------------------------+
     |      date       r_AAM       r_ACC   r_ADC       r_BED |
     |-------------------------------------------------------|
  1. | 04jan2010           .           .       .           . |
  2. | 05jan2010    .0234646    .0180196       .    .0317471 |
  3. | 06jan2010    -.014602   -.0363699       .    .0246929 |
  4. | 07jan2010    .0487933   -.0769604       .   -.0184622 |
  5. | 08jan2010           0   -.0408246       .           . |
     |-------------------------------------------------------|
  6. | 11jan2010   -.0056211           0       .           . |
  7. | 12jan2010   -.0431723   -.0870108       .    -.061876 |
  8. | 13jan2010           0    .0828399       .     .034847 |
  9. | 14jan2010    .0087848           .       .    .0598191 |
 10. | 15jan2010    .0058171           .       .           . |
     +-------------------------------------------------------+

.

[Øyvind Snilsberg]

alternatively, you may want to check out -egen rank()-,

gen month = mofd(date)

format %tm month

by month, sort: egen frank_r_AAM = rank(r_AAM), field

sort month frank_r_AAM

list month date r_AAM frank_r_AAM in 1/20, ab(20)

     +----------------------------------------------+
     |  month        date       r_AAM   frank_r_AAM |
     |----------------------------------------------|
  1. | 2010m1   07jan2010    .0487933             1 |
  2. | 2010m1   26jan2010    .0390994             2 |
  3. | 2010m1   19jan2010    .0329898             3 |
  4. | 2010m1   05jan2010    .0234646             4 |
  5. | 2010m1   25jan2010    .0092451             5 |
     |----------------------------------------------|
  6. | 2010m1   14jan2010    .0087848             6 |
  7. | 2010m1   15jan2010    .0058171             7 |
  8. | 2010m1   22jan2010           0             8 |
  9. | 2010m1   13jan2010           0             8 |
 10. | 2010m1   08jan2010           0             8 |
     |----------------------------------------------|
 11. | 2010m1   27jan2010    -.002951            11 |
 12. | 2010m1   11jan2010   -.0056211            12 |
 13. | 2010m1   28jan2010   -.0119113            13 |
 14. | 2010m1   06jan2010    -.014602            14 |
 15. | 2010m1   20jan2010   -.0238849            15 |
     |----------------------------------------------|
 16. | 2010m1   21jan2010   -.0244595            16 |
 17. | 2010m1   29jan2010   -.0273159            17 |
 18. | 2010m1   12jan2010   -.0431723            18 |
 19. | 2010m1   18jan2010   -.0505341            19 |
 20. | 2010m1   04jan2010           .             . |
     +----------------------------------------------+

you can use option unique if you want to (arbitrarily) break ties, although with this option the smallest value is ranked 1 so use gsort to reverse order,

by month (date), sort: egen x = rank(r_AAM), unique

gsort month -x

by month: gen urank_r_AAM = _n if r_AAM<.

sort month urank_r_AAM

list month date r_AAM urank_r_AAM in 1/20, ab(20)

     +----------------------------------------------+
     |  month        date       r_AAM   urank_r_AAM |
     |----------------------------------------------|
  1. | 2010m1   07jan2010    .0487933             1 |
  2. | 2010m1   26jan2010    .0390994             2 |
  3. | 2010m1   19jan2010    .0329898             3 |
  4. | 2010m1   05jan2010    .0234646             4 |
  5. | 2010m1   25jan2010    .0092451             5 |
     |----------------------------------------------|
  6. | 2010m1   14jan2010    .0087848             6 |
  7. | 2010m1   15jan2010    .0058171             7 |
  8. | 2010m1   08jan2010           0             8 |
  9. | 2010m1   22jan2010           0             9 |
 10. | 2010m1   13jan2010           0            10 |
     |----------------------------------------------|
 11. | 2010m1   27jan2010    -.002951            11 |
 12. | 2010m1   11jan2010   -.0056211            12 |
 13. | 2010m1   28jan2010   -.0119113            13 |
 14. | 2010m1   06jan2010    -.014602            14 |
 15. | 2010m1   20jan2010   -.0238849            15 |
     |----------------------------------------------|
 16. | 2010m1   21jan2010   -.0244595            16 |
 17. | 2010m1   29jan2010   -.0273159            17 |
 18. | 2010m1   12jan2010   -.0431723            18 |
 19. | 2010m1   18jan2010   -.0505341            19 |
 20. | 2010m1   04jan2010           .             . |
     +----------------------------------------------+

[Tan Nguyen]

It's really close to what I need, but I don't need to sort the rank of r_AAM, i just need the rank of return value in accordance with my date, based on the return value of each month.
Here is the result that I want. Thank you very much!

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date double(r_AAM rank)
date    r_AAM    rank
18266         .    .
18267  .0234646   16
18268  -.014602    6
18269  .0487933   19
18270         0   11
18273 -.0056211    8
18274 -.0431723    2
18275         0   11
18276  .0087848   14
18277  .0058171   13
18280 -.0505341    1
18281  .0329898   17
18282 -.0238849    5
18283 -.0244595    4
18284         0   11
18287  .0092451   15
18288  .0390994   18
18289  -.002951    9
18290 -.0119113    7
18291 -.0273159    3
18294  .0152708   13
18295  .0150411   12
18296         0    5
18297  .0029774    7
18298  -.042559    1
18301  .0245405   14
18302 -.0276515    3
18303  .0246199   15
18304  .0030317    9
18305  .0030225    8
18315  .0120186   11
18316 -.0303119    2
18317         0    5
18318  .0030788   10
18319         0    5
18322  .0061096   15
18323  .0030507   14
18324  .0090771   17
18325         0 12.5
18326         0 12.5
18329  .0384063   22
18330  .0481028   23
18331  .0191494   21
18332 -.0108963    7
18333  .0081855   16
18336  -.013683    6
18337 -.0279317    2
18338 -.0085348    8
18339  .0141785   18
18340 -.0141785    5
18343 -.0028674   11
18344 -.0261264    3
18345   .014602   19
18346 -.0294107    1
18347  -.002996  9.5
18350  .0178047   20
18351 -.0148087    4
18352  -.002996  9.5
18353         0   10
18354 -.0029953    5
18357         0   10
18358         0   10
18359  .0089687   17
18360  .0088889   16
18361  .0088107   15
18364 -.0058683    4
18365 -.0088627    3
18366 -.0348708    1
18367 -.0093107    2
18368  .0031133   14
18371  .0154125   20
18372  .0091309   18
18373  .0150416   19
18374 -.0029922    6
18378         0   10
18379         0   10
18380         0   10
18381         0   10
18386  .0265849   17
18387  .0483803   19
18388  .0487902   20
18389 -.0350022    4
18392 -.0448206    2
18393 -.0144339    8
18394   -.03551    3
18395         0 10.5
18396  .0060114   13
18399  .0029922   12
18400 -.0090036    9
18401 -.0213094    6
18402  .0213094   16
18403 -.0493895    1
18406   .012579   15
18407 -.0253182    5
18408  .0063904   14
18409         0 10.5
18410  .0313544   18
18413 -.0155563    7
18414  .0031297   14
18415  .0093327   19
end
format %tddd-Mon-YY date