Storing decile values correspong to decile rank in a separate variable

[rachita vig]

Hi everyone,

I am writing to understand how I can store the decile values for the following dummy dataset. In this dataset, I have already calculated for two years 1970 & 1971 the decile category in which the variable "pow (power)" belongs - this is stored in pow_dec. I now want to store the corresponding value for each decile rank/category in a separate variable to plot that v/s pow. How can I store the values separately in a new variable?

 egen pow_dec_pc = pctile(pow), p(10) by(year)

: this only spits out the value corresponding to the 1st decile.

input int year float(pow pow_dec)
1970 20 1
1970 23.5 1
1970 22 1
1970 13 1
1970 13 1
1970 22 1
1970 17.5 1
1970 20 1
1970 13 1
1970 23.5 1
1970 19 1
1970 16 1
1970 19 1
1970 22 1
1970 23.5 1
1970 17 1
1970 20.5 1
1970 13 1
1970 20.5 1
1970 20.5 1
1970 17.5 1
1970 17 1
1970 16 1
1970 20.5 1
1970 13 1
1970 22 1
1970 13 1
1970 20 1
1970 16 1
1970 19 1
1970 23.5 1
1970 25 2
1970 25 2
1970 25.5 2
1970 25 2
1970 29.5 2
1970 25.5 2
1970 25 2
1970 25 2
1970 29.5 2
1970 25 2
1970 29.5 2
1970 25 2
1970 29.5 2
1970 25 2
1970 27 2
1970 29.5 2
1970 25 2
1970 26 2
1970 25 2
1970 25 2
1970 28.5 2
1970 29.5 2
1970 25 2
1970 25 2
1970 29.5 2
1970 28 2
1970 29.5 2
1970 29.5 2
1970 25.5 2
1970 29.5 2
1970 33 3
1970 33 3
1970 31 3
1970 33 3
1970 32.5 3
1970 32.5 3
1970 32.5 3
1970 33 3
1970 32.5 3
1970 33 3
1970 31 3
1970 32.5 3
1970 32.5 3
1970 32.5 3
1970 31 3
1970 32.5 3
1970 33 3
1970 31.5 3
1970 31.5 3
1970 31.5 3
1970 32.5 3
1970 33 3
1970 31 3
1970 30.5 3
1970 33 3
1970 40.5 4
1970 40.5 4
1970 40.5 4
1970 37 4
1970 40.5 4
1970 37 4
1970 35.5 4
1970 36 4
1970 40.5 4
1970 39 4
1970 40.5 4
1970 40.5 4
1970 39.5 4
1970 35.5 4
1971 23.5 1
1971 13 1
1971 17.5 1
1971 22 1
1971 25 1
1971 25 1
1971 25 1
1971 19 1
1971 29.5 2
1971 31 2
1971 29.5 2
1971 30.5 2
1971 28.5 2

Thanks for your help!!

Rachita

[Nick Cox]

Thanks for your data example, almost but not quite ready to run. Here it is again with some code and results. Your example raises the usual generic questions about quantile binning -- and some unusual details about your case.

clear
input int year float(pow pow_dec)
1970 20 1
1970 23.5 1
1970 22 1
1970 13 1
1970 13 1
1970 22 1
1970 17.5 1
1970 20 1
1970 13 1
1970 23.5 1
1970 19 1
1970 16 1
1970 19 1
1970 22 1
1970 23.5 1
1970 17 1
1970 20.5 1
1970 13 1
1970 20.5 1
1970 20.5 1
1970 17.5 1
1970 17 1
1970 16 1
1970 20.5 1
1970 13 1
1970 22 1
1970 13 1
1970 20 1
1970 16 1
1970 19 1
1970 23.5 1
1970 25 2
1970 25 2
1970 25.5 2
1970 25 2
1970 29.5 2
1970 25.5 2
1970 25 2
1970 25 2
1970 29.5 2
1970 25 2
1970 29.5 2
1970 25 2
1970 29.5 2
1970 25 2
1970 27 2
1970 29.5 2
1970 25 2
1970 26 2
1970 25 2
1970 25 2
1970 28.5 2
1970 29.5 2
1970 25 2
1970 25 2
1970 29.5 2
1970 28 2
1970 29.5 2
1970 29.5 2
1970 25.5 2
1970 29.5 2
1970 33 3
1970 33 3
1970 31 3
1970 33 3
1970 32.5 3
1970 32.5 3
1970 32.5 3
1970 33 3
1970 32.5 3
1970 33 3
1970 31 3
1970 32.5 3
1970 32.5 3
1970 32.5 3
1970 31 3
1970 32.5 3
1970 33 3
1970 31.5 3
1970 31.5 3
1970 31.5 3
1970 32.5 3
1970 33 3
1970 31 3
1970 30.5 3
1970 33 3
1970 40.5 4
1970 40.5 4
1970 40.5 4
1970 37 4
1970 40.5 4
1970 37 4
1970 35.5 4
1970 36 4
1970 40.5 4
1970 39 4
1970 40.5 4
1970 40.5 4
1970 39.5 4
1970 35.5 4
1971 23.5 1
1971 13 1
1971 17.5 1
1971 22 1
1971 25 1
1971 25 1
1971 25 1
1971 19 1
1971 29.5 2
1971 31 2
1971 29.5 2
1971 30.5 2
1971 28.5 2
end

bysort year pow_dec (pow) : gen wanted = pow[_N]

list, sepby(year pow_dec)

tab year pow_dec



. bysort year pow_dec (pow) : gen wanted = pow[_N]

.
. list, sepby(year pow_dec)

     +--------------------------------+
     | year    pow   pow_dec   wanted |
     |--------------------------------|
  1. | 1970     13         1     23.5 |
  2. | 1970     13         1     23.5 |
  3. | 1970     13         1     23.5 |
  4. | 1970     13         1     23.5 |
  5. | 1970     13         1     23.5 |
  6. | 1970     13         1     23.5 |
  7. | 1970     16         1     23.5 |
  8. | 1970     16         1     23.5 |
  9. | 1970     16         1     23.5 |
 10. | 1970     17         1     23.5 |
 11. | 1970     17         1     23.5 |
 12. | 1970   17.5         1     23.5 |
 13. | 1970   17.5         1     23.5 |
 14. | 1970     19         1     23.5 |
 15. | 1970     19         1     23.5 |
 16. | 1970     19         1     23.5 |
 17. | 1970     20         1     23.5 |
 18. | 1970     20         1     23.5 |
 19. | 1970     20         1     23.5 |
 20. | 1970   20.5         1     23.5 |
 21. | 1970   20.5         1     23.5 |
 22. | 1970   20.5         1     23.5 |
 23. | 1970   20.5         1     23.5 |
 24. | 1970     22         1     23.5 |
 25. | 1970     22         1     23.5 |
 26. | 1970     22         1     23.5 |
 27. | 1970     22         1     23.5 |
 28. | 1970   23.5         1     23.5 |
 29. | 1970   23.5         1     23.5 |
 30. | 1970   23.5         1     23.5 |
 31. | 1970   23.5         1     23.5 |
     |--------------------------------|
 32. | 1970     25         2     29.5 |
 33. | 1970     25         2     29.5 |
 34. | 1970     25         2     29.5 |
 35. | 1970     25         2     29.5 |
 36. | 1970     25         2     29.5 |
 37. | 1970     25         2     29.5 |
 38. | 1970     25         2     29.5 |
 39. | 1970     25         2     29.5 |
 40. | 1970     25         2     29.5 |
 41. | 1970     25         2     29.5 |
 42. | 1970     25         2     29.5 |
 43. | 1970     25         2     29.5 |
 44. | 1970     25         2     29.5 |
 45. | 1970   25.5         2     29.5 |
 46. | 1970   25.5         2     29.5 |
 47. | 1970   25.5         2     29.5 |
 48. | 1970     26         2     29.5 |
 49. | 1970     27         2     29.5 |
 50. | 1970     28         2     29.5 |
 51. | 1970   28.5         2     29.5 |
 52. | 1970   29.5         2     29.5 |
 53. | 1970   29.5         2     29.5 |
 54. | 1970   29.5         2     29.5 |
 55. | 1970   29.5         2     29.5 |
 56. | 1970   29.5         2     29.5 |
 57. | 1970   29.5         2     29.5 |
 58. | 1970   29.5         2     29.5 |
 59. | 1970   29.5         2     29.5 |
 60. | 1970   29.5         2     29.5 |
 61. | 1970   29.5         2     29.5 |
     |--------------------------------|
 62. | 1970   30.5         3       33 |
 63. | 1970     31         3       33 |
 64. | 1970     31         3       33 |
 65. | 1970     31         3       33 |
 66. | 1970     31         3       33 |
 67. | 1970   31.5         3       33 |
 68. | 1970   31.5         3       33 |
 69. | 1970   31.5         3       33 |
 70. | 1970   32.5         3       33 |
 71. | 1970   32.5         3       33 |
 72. | 1970   32.5         3       33 |
 73. | 1970   32.5         3       33 |
 74. | 1970   32.5         3       33 |
 75. | 1970   32.5         3       33 |
 76. | 1970   32.5         3       33 |
 77. | 1970   32.5         3       33 |
 78. | 1970   32.5         3       33 |
 79. | 1970     33         3       33 |
 80. | 1970     33         3       33 |
 81. | 1970     33         3       33 |
 82. | 1970     33         3       33 |
 83. | 1970     33         3       33 |
 84. | 1970     33         3       33 |
 85. | 1970     33         3       33 |
 86. | 1970     33         3       33 |
     |--------------------------------|
 87. | 1970   35.5         4     40.5 |
 88. | 1970   35.5         4     40.5 |
 89. | 1970     36         4     40.5 |
 90. | 1970     37         4     40.5 |
 91. | 1970     37         4     40.5 |
 92. | 1970     39         4     40.5 |
 93. | 1970   39.5         4     40.5 |
 94. | 1970   40.5         4     40.5 |
 95. | 1970   40.5         4     40.5 |
 96. | 1970   40.5         4     40.5 |
 97. | 1970   40.5         4     40.5 |
 98. | 1970   40.5         4     40.5 |
 99. | 1970   40.5         4     40.5 |
100. | 1970   40.5         4     40.5 |
     |--------------------------------|
101. | 1971     13         1       25 |
102. | 1971   17.5         1       25 |
103. | 1971     19         1       25 |
104. | 1971     22         1       25 |
105. | 1971   23.5         1       25 |
106. | 1971     25         1       25 |
107. | 1971     25         1       25 |
108. | 1971     25         1       25 |
     |--------------------------------|
109. | 1971   28.5         2       31 |
110. | 1971   29.5         2       31 |
111. | 1971   29.5         2       31 |
112. | 1971   30.5         2       31 |
113. | 1971     31         2       31 |
     +--------------------------------+

. tab year pow_dec

           |                   pow_dec
      year |         1          2          3          4 |     Total
-----------+--------------------------------------------+----------
      1970 |        31         30         25         14 |       100
      1971 |         8          5          0          0 |        13
-----------+--------------------------------------------+----------
     Total |        39         35         25         14 |       113

Working backwards from unusual to usual:

1. Your code calculates the 10th percentile. conditional on the other details. It doesn't, and isn't intended to, calculate 10 deciles.

2. The data example is odd because no deciles other than 1 to 4 are shown.

3. Decile bins can be represented by the minimum values therein, or the maximum values, or otherwise. The code above produces the maximum values.

4. The example above hints at a common problem with quantile binning when ties are common in the data. The bins often fail to contain equal numbers of values given the need for the same values to be assigned to the same bin.

More discussion, including key references, within https://www.stata-journal.com/articl...article=pr0054 (Section 4) and https://www.stata-journal.com/articl...article=dm0095

[rachita vig]

Thanks a lot for the elaborate answer, Nick! Apologies for the odd data example - I only pasted some of observations (after calculating the deciles) for demonstration purposes.

[Joro Kolev]

I did not quite understand, but what Nick shows can also be done for all deciles, not just for the first one. After installing -egenmore-

findit egenmore

and follow the instructions. Then

. egen deciles = xtile(pow), nq(10)

. bysort year deciles (pow): gen decvals = pow[_N]

. list if year==1970, sepby(deciles)

     +-------------------------------------------+
     | year    pow   pow_dec   deciles   decvals |
     |-------------------------------------------|
  1. | 1970     13         1         1        17 |
  2. | 1970     13         1         1        17 |
  3. | 1970     13         1         1        17 |
  4. | 1970     13         1         1        17 |
  5. | 1970     13         1         1        17 |
  6. | 1970     13         1         1        17 |
  7. | 1970     16         1         1        17 |
  8. | 1970     16         1         1        17 |
  9. | 1970     16         1         1        17 |
 10. | 1970     17         1         1        17 |
 11. | 1970     17         1         1        17 |
     |-------------------------------------------|
 12. | 1970   17.5         1         2      20.5 |
 13. | 1970   17.5         1         2      20.5 |
 14. | 1970     19         1         2      20.5 |
 15. | 1970     19         1         2      20.5 |
 16. | 1970     19         1         2      20.5 |
 17. | 1970     20         1         2      20.5 |
 18. | 1970     20         1         2      20.5 |
 19. | 1970     20         1         2      20.5 |
 20. | 1970   20.5         1         2      20.5 |
 21. | 1970   20.5         1         2      20.5 |
 22. | 1970   20.5         1         2      20.5 |
 23. | 1970   20.5         1         2      20.5 |
     |-------------------------------------------|
 24. | 1970     22         1         3      23.5 |
 25. | 1970     22         1         3      23.5 |
 26. | 1970     22         1         3      23.5 |
 27. | 1970     22         1         3      23.5 |
 28. | 1970   23.5         1         3      23.5 |
 29. | 1970   23.5         1         3      23.5 |
 30. | 1970   23.5         1         3      23.5 |
 31. | 1970   23.5         1         3      23.5 |
     |-------------------------------------------|
 32. | 1970     25         2         4        25 |
 33. | 1970     25         2         4        25 |
 34. | 1970     25         2         4        25 |
 35. | 1970     25         2         4        25 |
 36. | 1970     25         2         4        25 |
 37. | 1970     25         2         4        25 |
 38. | 1970     25         2         4        25 |
 39. | 1970     25         2         4        25 |
 40. | 1970     25         2         4        25 |
 41. | 1970     25         2         4        25 |
 42. | 1970     25         2         4        25 |
 43. | 1970     25         2         4        25 |
 44. | 1970     25         2         4        25 |
     |-------------------------------------------|
 45. | 1970   25.5         2         5        27 |
 46. | 1970   25.5         2         5        27 |
 47. | 1970   25.5         2         5        27 |
 48. | 1970     26         2         5        27 |
 49. | 1970     27         2         5        27 |
     |-------------------------------------------|
 50. | 1970     28         2         6      29.5 |
 51. | 1970   28.5         2         6      29.5 |
 52. | 1970   29.5         2         6      29.5 |
 53. | 1970   29.5         2         6      29.5 |
 54. | 1970   29.5         2         6      29.5 |
 55. | 1970   29.5         2         6      29.5 |
 56. | 1970   29.5         2         6      29.5 |
 57. | 1970   29.5         2         6      29.5 |
 58. | 1970   29.5         2         6      29.5 |
 59. | 1970   29.5         2         6      29.5 |
 60. | 1970   29.5         2         6      29.5 |
 61. | 1970   29.5         2         6      29.5 |
     |-------------------------------------------|
 62. | 1970   30.5         3         7      31.5 |
 63. | 1970     31         3         7      31.5 |
 64. | 1970     31         3         7      31.5 |
 65. | 1970     31         3         7      31.5 |
 66. | 1970     31         3         7      31.5 |
 67. | 1970   31.5         3         7      31.5 |
 68. | 1970   31.5         3         7      31.5 |
 69. | 1970   31.5         3         7      31.5 |
     |-------------------------------------------|
 70. | 1970   32.5         3         8      32.5 |
 71. | 1970   32.5         3         8      32.5 |
 72. | 1970   32.5         3         8      32.5 |
 73. | 1970   32.5         3         8      32.5 |
 74. | 1970   32.5         3         8      32.5 |
 75. | 1970   32.5         3         8      32.5 |
 76. | 1970   32.5         3         8      32.5 |
 77. | 1970   32.5         3         8      32.5 |
 78. | 1970   32.5         3         8      32.5 |
     |-------------------------------------------|
 79. | 1970     33         3         9        36 |
 80. | 1970     33         3         9        36 |
 81. | 1970     33         3         9        36 |
 82. | 1970     33         3         9        36 |
 83. | 1970     33         3         9        36 |
 84. | 1970     33         3         9        36 |
 85. | 1970     33         3         9        36 |
 86. | 1970     33         3         9        36 |
 87. | 1970   35.5         4         9        36 |
 88. | 1970   35.5         4         9        36 |
 89. | 1970     36         4         9        36 |
     |-------------------------------------------|
 90. | 1970     37         4        10      40.5 |
 91. | 1970     37         4        10      40.5 |
 92. | 1970     39         4        10      40.5 |
 93. | 1970   39.5         4        10      40.5 |
 94. | 1970   40.5         4        10      40.5 |
 95. | 1970   40.5         4        10      40.5 |
 96. | 1970   40.5         4        10      40.5 |
 97. | 1970   40.5         4        10      40.5 |
 98. | 1970   40.5         4        10      40.5 |
 99. | 1970   40.5         4        10      40.5 |
100. | 1970   40.5         4        10      40.5 |
     +-------------------------------------------+

.