how to get around _pctile may not be combined with by

[Laur Hous]

I need to get the 75th percentile for weighted prices for 560 zip codes. Is there a workaround for this code that will allow me to do this?

This is what i can do:

[CODE
_pctile rate_tod_hr if mps_typeofcare == 1 [pweight = finalcapacity], p(75)
gen r1 = (r(r1)) // create a variable for 75th percentile weighted price
rename r1 W75pct_smfamH // rename this variable - this is the 75th perentile weighted price for small family provider hourly child care[/CODE]
[/code]

and this is what i want to do but _pctile may not be combined with by :

[CODE
_pctile rate_tod_hr if mps_typeofcare == 1 [pweight = finalcapacity], p(75)
gen r1 = (r(r1)) // create a variable for 75th percentile weighted price
rename r1 W75pct_smfamH // rename this variable - this is the 75th perentile weighted price for small family provider hourly child care
[/CODE]

here is a short sample of my data:

fcco_zipcode finalcapacity rate_tod_hr slots_smfam_tod_hr 97002 30 97002 12 97003 8 3 28 97003 10 5 28 97003 10 5 28 97003 10 28 97003 10 28 97003 10 28 97003 6 28 97003 10 28 97003 10 28 97003 10 28 97003 30 28 97003 70 28 97003 64 28 97003 20 28 97003 39 28 97003 16 28 97003 13 28 97003 9 28 97003 10 28 97003 6 28 97003 10 28 97003 12 28 97003 16 28 97003 15 28 97003 10 28 97003 9 28 97003 9 28 97003 15 28 97003 14 28 97003 16 28 97004 30 97005 10 16 26 97005 3 26 97005 10 26 97005 10 26 97005 8 26 97005 10 26 97005 3 26 97005 10 26 97005 8 26 97005 91 26 97005 110 26 97005 95 26 97005 70 26 97005 61 26 97005 60 26 97005 148 26 97005 290 26 97005 16 7 26 97005 10 26 97005 12 26 97005 16 26 97005 16 26 97005 16 26 97005 12 26 97005 12 26 97005 16 26 97005 16 26 97006 10 3.5 46 97006 10 5 46 97006 3 46 97006 10 46 97006 10 46 97006 10 46 97006 7 46 97006 10 46 97006 3 46 97006 6 46 97006 10 46 97006 65 46 97006 38 46 97006 43 46 97006 69 46 97006 36 46 97006 101 46 97006 118 46 97006 45 46 97006 50 46 97006 28 46 97006 45 46 97006 14 6 46 97006 12 6 46 97006 4 46 97006 16 46 97006 12 46 97006 16 46 97006 16 46 97006 15 46 97006 8 46 97006 16 46 97006 10 46 97006 16 46 97006 12 46 97006 8 46

[Carlo Lazzaro]

Laur:
you may want to try something along the following lines:

. sysuse auto, clear
(1978 automobile data)

. forval num = 0(1)1 {
  2. pctile pct_`num'=weight if foreign==`num', nq(10)
  3. }

. list pct_0 pct_1 in 1/9

     +---------------+
     | pct_0   pct_1 |
     |---------------|
  1. |  2230    1930 |
  2. |  2690    1990 |
  3. |  3180    2040 |
  4. |  3260    2070 |
  5. |  3360    2180 |
     |---------------|
  6. |  3470    2280 |
  7. |  3690    2410 |
  8. |  3880    2670 |
  9. |  4080    2830 |
     +---------------+

.

[Clyde Schechter]

levelsof fcco_zipcode, local(zips)
gen W75pct_smfamH = .
foreach z of local zips {
    _pctile rate_tod_hr if /* mps_typeofcare == 1 */ fcco_zipcode == `z' ///
        [pweight = finalcapacity], p(75)
    replace W75pct_smfamH = r(r1) if fcco_zipcode == `z' // create a variable for 75th percentile weighted price
}

Note: I have commented out the mps_typeofcare==1 part of your if qualifier because your example data contains no such variable. Assuming it exists in your real data set and you still want to condition on that, then by all means remove the comment delimiters. In that case you might (or might not) also want to add it to the -if- qualifier in the -replace- command.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Laur Hous]

Thank you Clyde. I tried your code and got the message - invalid 'fcco_zipcode'. Is this because of the format of the fcco_zipcode variable? I used use the -dataex- command this time so you can see the format for each variable

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input double fcco_zipcode byte grp_tod_desired float finalcapacity double rate_tod_hr
97002  .  12   .
97002  .  30   .
97003  1   8   3
97003  2  10   5
97003  1  10   5
97003  1  10   .
97003  2  10   .
97003  2  10   .
97003  1  10   .
97003  1  10   .
97003  3  10   .
97003  1   6   .
97003 10  30   .
97003 13  70   .
97003  8  64   .
97003  .  20   .
97003  .  39   .
97003  4  16   .
97003  3  13   .
97003  2   9   .
97003  3  10   .
97003  6  12   .
97003  2  16   .
97003  2  10   .
97003  2   6   .
97003  5  15   .
97003  3  10   .
97003  3   9   .
97003  6  16   .
97003  4  14   .
97003  7  15   .
97003  2   9   .
97004  .  30   .
97005  2  10  16
97005  1   3   .
97005  1  10   .
97005  3   8   .
97005  2  10   .
97005  2  10   .
97005  1  10   .
97005  2   8   .
97005  1   3   .
97005 23  91   .
97005 20 110   .
97005 24  95   .
97005  .  70   .
97005 16 148   .
97005  .  60   .
97005  .  61   .
97005  . 290   .
97005  4  16   7
97005  4  10   .
97005  3  12   .
97005  5  16   .
97005  4  16   .
97005  3  12   .
97005  7  16   .
97005  .  16   .
97005  5  12   .
97005  .  16   .
97006  2  10 3.5
97006  1  10   5
97006  1   3   .
97006  2  10   .
97006  2   7   .
97006  2  10   .
97006  1  10   .
97006  2  10   .
97006  2   6   .
97006  2  10   .
97006  .   3   .
97006 12  65   .
97006  7  38   .
97006  5  43   .
97006 21  69   .
97006  8  36   .
97006 20 101   .
97006 30 118   .
97006  .  28   .
97006  .  50   .
97006  .  45   .
97006  .  45   .
97006  4  14   6
97006  3  12   6
97006  2   4   .
97006  5  16   .
97006  6  12   .
97006  6  16   .
97006  5  16   .
97006  5  15   .
97006  2   8   .
97006  4  16   .
97006  .  10   .
97006  .  12   .
97006  4  16   .
97006  2   8   .
97007  2  10 5.5
97007  2   5   .
97007  2  10   .
97007  .   1   .
end

[Clyde Schechter]

I cannot replicate the error you are getting. When I run the code from #3 with the example data in #4, it runs with no errors and produces correct results:

. * Example generated by -dataex-. For more info, type help dataex
. clear

. input double fcco_zipcode byte grp_tod_desired float finalcapacity double rate_tod_hr

     fcco_zip~e  grp_to~d  finalca~y  rate_tod~r
  1. 97002  .  12   .
  2. 97002  .  30   .
  3. 97003  1   8   3
  4. 97003  2  10   5
  5. 97003  1  10   5
  6. 97003  1  10   .
  7. 97003  2  10   .
  8. 97003  2  10   .
  9. 97003  1  10   .
 10. 97003  1  10   .
 11. 97003  3  10   .
 12. 97003  1   6   .
 13. 97003 10  30   .
 14. 97003 13  70   .
 15. 97003  8  64   .
 16. 97003  .  20   .
 17. 97003  .  39   .
 18. 97003  4  16   .
 19. 97003  3  13   .
 20. 97003  2   9   .
 21. 97003  3  10   .
 22. 97003  6  12   .
 23. 97003  2  16   .
 24. 97003  2  10   .
 25. 97003  2   6   .
 26. 97003  5  15   .
 27. 97003  3  10   .
 28. 97003  3   9   .
 29. 97003  6  16   .
 30. 97003  4  14   .
 31. 97003  7  15   .
 32. 97003  2   9   .
 33. 97004  .  30   .
 34. 97005  2  10  16
 35. 97005  1   3   .
 36. 97005  1  10   .
 37. 97005  3   8   .
 38. 97005  2  10   .
 39. 97005  2  10   .
 40. 97005  1  10   .
 41. 97005  2   8   .
 42. 97005  1   3   .
 43. 97005 23  91   .
 44. 97005 20 110   .
 45. 97005 24  95   .
 46. 97005  .  70   .
 47. 97005 16 148   .
 48. 97005  .  60   .
 49. 97005  .  61   .
 50. 97005  . 290   .
 51. 97005  4  16   7
 52. 97005  4  10   .
 53. 97005  3  12   .
 54. 97005  5  16   .
 55. 97005  4  16   .
 56. 97005  3  12   .
 57. 97005  7  16   .
 58. 97005  .  16   .
 59. 97005  5  12   .
 60. 97005  .  16   .
 61. 97006  2  10 3.5
 62. 97006  1  10   5
 63. 97006  1   3   .
 64. 97006  2  10   .
 65. 97006  2   7   .
 66. 97006  2  10   .
 67. 97006  1  10   .
 68. 97006  2  10   .
 69. 97006  2   6   .
 70. 97006  2  10   .
 71. 97006  .   3   .
 72. 97006 12  65   .
 73. 97006  7  38   .
 74. 97006  5  43   .
 75. 97006 21  69   .
 76. 97006  8  36   .
 77. 97006 20 101   .
 78. 97006 30 118   .
 79. 97006  .  28   .
 80. 97006  .  50   .
 81. 97006  .  45   .
 82. 97006  .  45   .
 83. 97006  4  14   6
 84. 97006  3  12   6
 85. 97006  2   4   .
 86. 97006  5  16   .
 87. 97006  6  12   .
 88. 97006  6  16   .
 89. 97006  5  16   .
 90. 97006  5  15   .
 91. 97006  2   8   .
 92. 97006  4  16   .
 93. 97006  .  10   .
 94. 97006  .  12   .
 95. 97006  4  16   .
 96. 97006  2   8   .
 97. 97007  2  10 5.5
 98. 97007  2   5   .
 99. 97007  2  10   .
100. 97007  .   1   .
101. end

.
. levelsof fcco_zipcode, local(zips)
97002 97003 97004 97005 97006 97007

. gen W75pct_smfamH = .
(100 missing values generated)

. foreach z of local zips {
  2.     _pctile rate_tod_hr if /* mps_typeofcare == 1 */ fcco_zipcode == `z' ///
>         [pweight = finalcapacity], p(75)
  3.     replace W75pct_smfamH = r(r1) if fcco_zipcode == `z' // create a variable for 75th percentile weighted price
  4. }
no observations
(0 real changes made)
(30 real changes made)
no observations
(0 real changes made)
(27 real changes made)
(36 real changes made)
(4 real changes made)

[Laur Hous]

hmmm. I have it running with no error message now. I wag getting an error because i included the mps_typeofcare==1, which you had blocked out but did not include the "&" I believe it should look like this, am i correct?.

foreach z of local zips {
_pctile rate_tod_hr if mps_typeofcare == 1 & fcco_zipcode == `z' ///
[pweight = finalcapacity], p(75)
replace W75pct_smfamH = r(r1) if fcco_zipcode == `z' // create a variable for 75th percentile weighted price
}


but W75pct_smfamH has no observations, so something is not working right.
I believe the 75th percentiles for the first 4 zipcodes should be:

fcco_zipcode 97003		W75pct_smfamH 5
97005		16
97006		5
97007		6

using the following data:

* Example generated by -dataex-. For more info, type help dataex
clear
input double fcco_zipcode float(mps_typeofcare finalcapacity) double rate_tod_hr
97002 2  12   .
97002 2  30   .
97003 1   8   3
97003 2  30   .
97003 1  10   5
97003 1  10   .
97003 3   9   .
97003 2  20   .
97003 3   9   .
97003 1  10   .
97003 3  16   .
97003 3  10   .
97003 3  10   .
97003 3   6   .
97003 3  10   .
97003 1  10   .
97003 1  10   .
97003 1   6   .
97003 3  15   .
97003 1  10   .
97003 3  15   .
97003 3  12   .
97003 3  13   .
97003 3  16   .
97003 1  10   5
97003 2  64   .
97003 2  70   .
97003 3  14   .
97003 1  10   .
97003 3  16   .
97003 2  39   .
97003 3   9   .
97004 2  30   .
97005 2  70   .
97005 1  10   .
97005 2  60   .
97005 1   3   .
97005 3  12   .
97005 3  12   .
97005 3  16   .
97005 2 110   .
97005 1   8   .
97005 1  10   .
97005 2 290   .
97005 3  16   .
97005 3  16   .
97005 3  16   7
97005 2  91   .
97005 1  10   .
97005 2  95   .
97005 3  10   .
97005 1  10  16
97005 3  16   .
97005 3  12   .
97005 2  61   .
97005 3  16   .
97005 1   8   .
97005 1   3   .
97005 1  10   .
97005 2 148   .
97006 2  38   .
97006 3  16   .
97006 1  10   5
97006 3  15   .
97006 3   8   .
97006 3  12   6
97006 1   7   .
97006 3  16   .
97006 1  10   .
97006 3  10   .
97006 1  10   .
97006 3  12   .
97006 2  28   .
97006 3  16   .
97006 3  12   .
97006 3   8   .
97006 3   4   .
97006 1  10   .
97006 3  16   .
97006 1  10 3.5
97006 1  10   .
97006 1   3   .
97006 3  14   6
97006 3  16   .
97006 2  65   .
97006 2 118   .
97006 1   6   .
97006 2  50   .
97006 1   3   .
97006 1  10   .
97006 2 101   .
97006 2  45   .
97006 2  43   .
97006 2  69   .
97006 2  36   .
97006 2  45   .
97007 2 103   .
97007 3  16   .
97007 3  11   .
97007 3  11   .
end
label values mps_typeofcare mpstype
label def mpstype 1 "1 Family", modify
label def mpstype 2 "2 Center", modify
label def mpstype 3 "3 Certified", modify

[Clyde Schechter]

Your revision to the code is correct. But, again, I cannot replicate the error you are getting.

. gen W75pct_smfamH = .
(100 missing values generated)

. levelsof fcco_zipcode, local(zips)
97002 97003 97004 97005 97006 97007

. foreach z of local zips {
  2.         _pctile rate_tod_hr if mps_typeofcare == 1 & fcco_zipcode == `z' ///
>                 [pweight = finalcapacity], p(75)
  3.         replace W75pct_smfamH = r(r1) if fcco_zipcode == `z' // create a variable for 75th percentile weighted price
  4. }
no observations
(0 real changes made)
(30 real changes made)
no observations
(0 real changes made)
(27 real changes made)
(36 real changes made)
no observations
(0 real changes made)

.
. //      CHECK RESULTS
. tabstat W75pct_smfamH, by(fcco_zipcode) statistics(min max)

Summary for variables: W75pct_smfamH
Group variable: fcco_zipcode

fcco_zipcode |       Min       Max
-------------+--------------------
       97002 |         .         .
       97003 |         5         5
       97004 |         .         .
       97005 |        16        16
       97006 |         5         5
       97007 |         .         .
-------------+--------------------
       Total |         5        16
----------------------------------

These results agree with yours, except for 97007. The reason 97007 gets missing values from the code is because 97707 doesn't have any observations with mps_typeofcare == 1.

Added: I think I know why you were getting no results: you omitted the -levelsof fcco_zipcode, local(zips)- command. Without that, local macro zips is undefined, so the loop -foreach z of local zips- just gets skipped. You have to execute that levelsof command along with the loop. If you are trying to run this one line at a time, it is guaranteed to fail.

More generally, in Stata, all code between the place where a local macro gets defined and the last place where that local macro is referred to must be run uninterrupted. Whenever you interrupt code where a local macro has been defined, at the interruption, the local macro "goes out of scope," that is, it disappears. Any subsequent code that references the local macro no longer has access to its contents and the results can be a variety of errors, depending on the specific way in which the code is attempting to use the local macro. In the case where the local macro governs the execution of the loop, it means that the loop gets skipped.

[Laur Hous]

Thank you so much Clyde!! You are correct, i neglected to run the "levelsof" section, and got all missing values. I now get values that are correct. Thanks again for your help. You saved me a lot of frustration.

[Laur Hous]

Carlo, thank you also for your help. I think Clyde's code was more in line with what I wanted to do. I'm curious though. I'm new to figuring out loops. Does the code you gave me below work if my values are not consecutive? I'm wondering because my zipcodes skip numbers sometimes. For example, the first zip is 97003 and the next is 97005.

[Laur Hous]

Clyde, thank you so much for your help last week. I have tried to apply this technique to another variable and i am getting an error message and am hoping you can tell me why? What is different about this variable? It is a float format rather than double format, but does that really matter? I get a message that A is not found.

Here is my code and my data below:

. * get all percentiles of weighted price rate areas A, B and C.            
.         levelsof ratearea2022, local(ratearea)
`"A"' `"B"' `"C"'

.     gen p10f_hour = .
(2,752 missing values generated)

.         gen p25f_hour = .
(2,752 missing values generated)

.         gen p50f_hour = .
(2,752 missing values generated)

.         gen p60f_hour = .
(2,752 missing values generated)

.         gen p75f_hour = .
(2,752 missing values generated)

.         gen p90f_hour = .
(2,752 missing values generated)

.        
.         foreach z of local ratearea {
  2.        _pctile rate_inf_hr if mps_typeofcare == 1 & ratearea2022 == `z' ///
>          [pweight = finalcapacity], p(10, 25, 50, 60, 75, 90)
  3.        replace p10f_hour = r(r1) if ratearea2022 == `z' // create a variable for 10th percentile weighted price
  4.            replace p25f_hour = r(r2) if ratearea2022 == `z' // create a variable for 25th percentile weighted price
  5.            replace p50f_hour = r(r3) if ratearea2022 == `z' // create a variable for 50th percentile weighted price
  6.            replace p60f_hour = r(r4) if ratearea2022 == `z' // create a variable for 60th percentile weighted price
  7.            replace p75f_hour = r(r5) if ratearea2022 == `z' // create a variable for 75th percentile weighted price
  8.            replace p90f_hour = r(r6) if ratearea2022 == `z' // create a variable for 90th percentile weighted price
  9.    }
A not found
r(111);

end of do-file

r(111);

.

error message:


[CODE]
----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input float mps_typeofcare str1 ratearea2022 double rate_inf_hr float finalcapacity double fcco_zipcode
1 "A"    2 10 97233
1 "A"    . 10 97230
1 "A"  2.5  8 97266
1 "A"    . 10 97030
1 "A"  3.5  8 97266
1 "A"    3 10 97216
1 "A"    .  8 97230
1 "A"    3 10 97236
1 "A"    3 10 97477
1 "A"  3.5  5 97024
1 "A"    3 10 97230
1 "A"    .  6 97230
1 "A"    . 10 97236
1 "A"    3  8 97003
1 "A"    4 10 97015
1 "A"    3 10 97266
1 "A"    3 10 97056
1 "A"  3.5 10 97051
1 "A"  3.5  6 97060
1 "A"  4.5 10 97080
1 "A"  3.5  8 97351
1 "A"    .  8 97062
1 "A"  3.5 10 97006
1 "A"    . 10 97233
1 "A"    . 10 97236
1 "A"  3.5 10 97266
1 "A"  3.5 10 97051
1 "A"    4 10 97030
1 "A"  3.5  8 97266
1 "A"  3.5  3 97351
1 "A"  3.5 10 97113
1 "A"  4.5 10 97078
1 "A"    4  5 97062
1 "A"    .  9 97236
1 "A"    4  5 97478
1 "A"    . 10 97080
1 "A"    . 10 97233
1 "A"    .  6 97216
1 "A"    5  8 97062
1 "A"    5 10 97060
1 "A"  4.5 10 97218
1 "A"  4.5 10 97123
1 "A"  4.5 10 97404
1 "A"    .  5 97220
1 "A"  4.5 10 97478
1 "A"    5 10 97233
1 "A"    . 10 97266
1 "A"    .  5 97216
1 "A"    .  2 97060
1 "A"    5 10 97230
1 "A"    .  3 97062
1 "A" 4.48 10 97216
1 "A"    .  3 97062
1 "A"  4.5 10 97361
1 "A"    5  7 97236
1 "A"    5 10 97402
1 "A"    5 10 97220
1 "A"  4.5 10 97013
1 "A"    5 10 97080
1 "A"  4.5 10 97233
1 "A"  4.5 10 97113
1 "A"  4.5 10 97013
1 "A"  4.5 10 97351
1 "A"    .  3 97478
1 "A"    .  3 97361
1 "A"    5 10 97124
1 "A"    5  7 97477
1 "A"    6  5 97031
1 "A"    5 10 97266
1 "A"    6 10 97003
1 "A"    5 10 97266
1 "A"    5 10 97351
1 "A"    5 10 97008
1 "A"    6 10 97224
1 "A"  5.5 10 97132
1 "A"    5  8 97056
1 "A"    5 10 97707
1 "A"    5 10 97351
1 "A"  5.5 10 97060
1 "A"    6  9 97216
1 "A"    6 10 97477
1 "A"    5  8 97477
1 "A"    6 10 97078
1 "A"    5  8 97031
1 "A"    6 10 97236
1 "A"    . 10 97216
1 "A"    6 10 97236
1 "A"    6  6 97035
1 "A"    5  5 97220
1 "A"    5 10 97216
1 "A"    .  3 97266
1 "A"    5 10 97233
1 "A"    6 10 97224
1 "A"    6 10 97003
1 "A"    5  7 97216
1 "A"    6 10 97230
1 "A"    7 10 97019
1 "A"    6  8 97062
1 "A"    . 10 97216
1 "A"    6 10 97225
end
label values mps_typeofcare mpstype
label def mpstype 1 "1 Family", modify

------------------ copy up to and including the previous line ------------------

Listed 100 out of 2752 observations
Use the count() option to list more

[Hemanshu Kumar]

You need to enclose the `z' in quotes:

... ratearea2022 == "`z'"

[Nick Cox]

First time round your loop,

 
 replace p10f_hour = r(r1) if ratearea2022 == `z'

is evaluated as

 
 replace p10f_hour = r(r1) if ratearea2022 == A

which would be legal if there were a variable (or scalar) with name A, which Stata is expecting from your code, exactly as it can work out that ratearea2022 is a variable in your data. But there is no such variable (or scalar), which is what Stata is telling you. Hence as Hemanshu Kumar pointed out, you need the double quotes to flag that you mean a literal string.