generate ratio of a variable over group for different percentile of the variable's distribution

[Pio Medolla]

Dear all,

I would like to create a ratio of grswage by sex for different percentile of the grswage distribution and different years(wave).
I have created the percentile for each wave and sex by:

summ grswage, detail
return list
bys wave sex: egen p25_grswage = pctile(grswage), p(5)
bys wave sex: egen p50_grswage = pctile(grswage), p(10)
.
.
.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long pidp float wave byte sex float(grswage p10_grswage p25_grswage p50_grswage p75_grswage p90_grswage)
 748802411 19 1  18.06866 7.531728 9.926337 14.07158 20.242 28.08492
1632819407 19 1  40.87979 7.531728 9.926337 14.07158 20.242 28.08492
 136456967 19 1  22.75313 7.531728 9.926337 14.07158 20.242 28.08492
 136459687 19 1 21.114985 7.531728 9.926337 14.07158 20.242 28.08492
 204004771 19 1 14.220705 7.531728 9.926337 14.07158 20.242 28.08492
 545825807 19 1  21.53421 7.531728 9.926337 14.07158 20.242 28.08492
 136501171 19 1 13.067676 7.531728 9.926337 14.07158 20.242 28.08492
 680020411 19 1  9.381696 7.531728 9.926337 14.07158 20.242 28.08492
 613290647 19 1  28.01674 7.531728 9.926337 14.07158 20.242 28.08492
 204141451 19 1 24.886234 7.531728 9.926337 14.07158 20.242 28.08492
 478726811 19 2  25.59727 6.952345 8.887941 12.189176 16.93674 22.69262
 816343411 19 2 18.987183 6.952345 8.887941 12.189176 16.93674 22.69262
 478728171 19 2  5.688282 6.952345 8.887941 12.189176 16.93674 22.69262
 478733623 19 2  6.110933 6.952345 8.887941 12.189176 16.93674 22.69262
 612578007 19 2 11.850614 6.952345 8.887941 12.189176 16.93674 22.69262
1564019047 19 2 11.145958 6.952345 8.887941 12.189176 16.93674 22.69262
 884348847 19 2  9.751341 6.952345 8.887941 12.189176 16.93674 22.69262
 273297447 19 2 16.389091 6.952345 8.887941 12.189176 16.93674 22.69262
 612276087 19 2   5.17517 6.952345 8.887941 12.189176 16.93674 22.69262
 884407327 19 2  9.090909 6.952345 8.887941 12.189176 16.93674 22.69262
 884403931 19 2  22.42006 6.952345 8.887941 12.189176 16.93674 22.69262
end
format %ty wave
label values sex a_sex
label def a_sex 1 "male", modify
label def a_sex 2 "female", modify

the ratio that I want to create looks like for example:
bys wave: gen p5_ratio = p5_grswage (sex==2) /p5_grswage (sex==1)
bys wave: gen p10_ratio = p10_grswage (sex==2) /p10_grswage (sex==1)
.
.
.


But then the code does not work since in a row I miss the counter sex percentile, is it possible that I am required to create the percentile of grswage differently?

Thanks

[Ken Chui]

This code is not beautiful by any stretch but should be able to get what you need:

foreach x in 10 25 50 75 90{
    gen p`x'_grswage_m = p`x'_grswage if sex == 1
    gsort p`x'_grswage_m
    replace p`x'_grswage_m = p`x'_grswage_m[_n-1] if p`x'_grswage_m == .
    gen p`x'_grswage_f = p`x'_grswage if sex == 2
    gsort p`x'_grswage_f
    replace p`x'_grswage_f = p`x'_grswage_f[_n-1] if p`x'_grswage_f == .
    gen ratio_`x' = p`x'_grswage_f / p`x'_grswage_m
    drop *_m *_f
}

[Carlo Lazzaro]

Pio:
if the first and the last observations of each -p-whatever- Always relate to male and female, respectively, the last line of your code can become (-display- calculation merely checks the result):

. bys wave: gen p10_ratio = p10_grswage[1]/p10_grswage[_N]
. di 7.531728/6.952345
1.0833363

. list

     +---------------------------------------------------------------------------------------------------------+
     |       pidp   wave      sex    grswage   p10_gr~e   p25_gr~e   p50_gr~e   p75_gr~e   p90_gr~e   p10_ra~o |
     |---------------------------------------------------------------------------------------------------------|
  1. |  748802411     19     male   18.06866   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
  2. | 1632819407     19     male   40.87979   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
  3. |  136456967     19     male   22.75313   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
  4. |  136459687     19     male   21.11498   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
  5. |  204004771     19     male   14.22071   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
     |---------------------------------------------------------------------------------------------------------|
  6. |  545825807     19     male   21.53421   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
  7. |  136501171     19     male   13.06768   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
  8. |  680020411     19     male   9.381696   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
  9. |  613290647     19     male   28.01674   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
 10. |  204141451     19     male   24.88623   7.531728   9.926337   14.07158     20.242   28.08492   1.083336 |
     |---------------------------------------------------------------------------------------------------------|
 11. |  478726811     19   female   25.59727   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
 12. |  816343411     19   female   18.98718   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
 13. |  478728171     19   female   5.688282   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
 14. |  478733623     19   female   6.110933   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
 15. |  612578007     19   female   11.85061   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
     |---------------------------------------------------------------------------------------------------------|
 16. | 1564019047     19   female   11.14596   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
 17. |  884348847     19   female   9.751341   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
 18. |  273297447     19   female   16.38909   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
 19. |  612276087     19   female    5.17517   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
 20. |  884407327     19   female   9.090909   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
     |---------------------------------------------------------------------------------------------------------|
 21. |  884403931     19   female   22.42006   6.952345   8.887941   12.18918   16.93674   22.69262   1.083336 |
     +---------------------------------------------------------------------------------------------------------+

.

[Nick Cox]

Here is another way to do it:

bys wave: egen p5_grswage_1 = pctile(cond(sex==1, grswage, .)), p(5)
bys wave: egen p5_grswage_2 = pctile(cond(sex==2, grswage, .)), p(5)
gen p5_grswage_ratio = p5_grswage_1 / p5_grswage_2

Here 5 means the percentile in question and 1 and 2 refer to sex. (In #1 25 and 50 are not good labels for 5 and 10 percentiles!)


That could be extended to a loop, more or less as in @Ken Chui's code.

[Pio Medolla]

That's great, thank everyone for the insights, I have obtained what I was looking for.