rangestat

[Vishal Sharma]

hi all,

I am trying to figure out how to use -rangestat- for the following dataset example using -dataex-

For each observation, I would like to count the number of different atc_codes that are associated with drugclass=0 in a rolling 30 day window using dayzero as the key variable. I am not interested in including observations where drugclass==1 in this count (drugclass== 1 should not be contributing to the count), so if there are only drugclass==1 in the 30 day window, then the count would be 0. However, I do want to have the count of different atc_codes for every observation (both drugclass==1 and drugclass==0)

the command would look similar to this: rangestat (count) xyz,interval(dayzero -30 0) by (patid)-

* Example generated by -dataex-. To install: ssc install dataex
clear
input double patid str8 atc_code float(dpd_match dpd_bzra_match) int dayzero float drugclass
400002181 "R05DA20" 1 0 1901 1
400002804 "N05CF01" 0 1 2556 0
400002804 "N05CF01" 0 1 2208 0
400002804 "N05CF01" 0 1 2067 0
400002804 "N05CF01" 0 1 2458 0
400002804 "N05CF01" 0 1 1843 0
400002804 "N05CF01" 0 1 1977 0
400002804 "N05CF01" 0 1 2303 0
400010442 "N02AJ13" 1 0 2175 1
400010442 "N02AJ13" 1 0 1908 1
400010442 "N02AJ13" 1 0 2183 1
400010442 "N02AJ17" 1 0 1908 1
400010442 "N05BA06" 0 1 2496 0
400040251 "N02AJ06" 1 0 2326 1
400040251 "N03AE01" 0 1 1857 0
400040251 "N03AE01" 0 1 2222 0
400040251 "N03AE01" 0 1 1994 0
400040251 "N03AE01" 0 1 1881 0
400040251 "N03AE01" 0 1 2029 0
400040251 "N03AE01" 0 1 1907 0
400040251 "N03AE01" 0 1 2446 0
400040251 "N03AE01" 0 1 2080 0
400040251 "N03AE01" 0 1 2412 0
400040251 "N03AE01" 0 1 2586 0
400040251 "N03AE01" 0 1 2493 0
400040251 "N03AE01" 0 1 1965 0
400040251 "N03AE01" 0 1 2164 0
400040251 "N03AE01" 0 1 2188 0
400040251 "N03AE01" 0 1 2327 0
400040251 "N03AE01" 0 1 2302 0
400040251 "N03AE01" 0 1 2041 0
400040251 "N03AE01" 0 1 2530 0
400040251 "N03AE01" 0 1 2266 0
400040251 "N03AE01" 0 1 2133 0
400040251 "N03AE01" 0 1 2109 0
400040251 "N03AE01" 0 1 2547 0
400040251 "N03AE01" 0 1 1928 0
400040251 "N03AE01" 0 1 2373 0
400040251 "N03AE01" 0 1 2352 0
400040251 "N03AE01" 0 1 2466 0
400040251 "N03AE01" 0 1 1836 0
400040251 "N03AE01" 0 1 2245 0
400040251 "N05CF01" 0 1 1944 0
400040251 "N05CF01" 0 1 2148 0
400040251 "N05CF01" 0 1 2448 0
400040251 "N05CF01" 0 1 2511 0
400040251 "N05CF01" 0 1 2091 0
400040251 "N05CF01" 0 1 2579 0
400040251 "N05CF01" 0 1 2312 0
400040251 "N05CF01" 0 1 1928 0
400040251 "N05CF01" 0 1 1857 0
400040251 "N05CF01" 0 1 2204 0
400040251 "N05CF01" 0 1 2008 0
400040251 "N05CF01" 0 1 2229 0
400040251 "N05CF01" 0 1 1836 0
400040251 "N05CF01" 0 1 1977 0
400040251 "N05CF01" 0 1 2398 0
400040251 "N05CF01" 0 1 2283 0
400040251 "N05CF01" 0 1 2118 0
400040251 "N05CF01" 0 1 2341 0
400040251 "N05CF01" 0 1 1907 0
400040251 "N05CF01" 0 1 2547 0
400040251 "N05CF01" 0 1 2062 0
400040251 "N05CF01" 0 1 2257 0
400040251 "N05CF01" 0 1 2039 0
400040251 "N05CF01" 0 1 2607 0
400040251 "N05CF01" 0 1 2478 0
400040251 "N05CF01" 0 1 2428 0
400040251 "N05CF01" 0 1 1892 0
400040251 "N05CF01" 0 1 2368 0
400040251 "N05CF01" 0 1 1881 0
400040251 "N05CF01" 0 1 2175 0
400041118 "N02AA03" 1 0 2252 1
400041118 "N02AJ06" 1 0 2294 1
400041118 "N05BA06" 0 1 2373 0
400041118 "N05BA06" 0 1 1921 0
400041118 "N05BA06" 0 1 2481 0
400041118 "N05BA06" 0 1 2350 0
400041118 "N05CF01" 0 1 2481 0
400041118 "N05CF01" 0 1 2593 0
400041118 "N05CF01" 0 1 2522 0
400041118 "N05CF01" 0 1 2308 0
400041118 "N05CF01" 0 1 2395 0
400041118 "N05CF01" 0 1 2472 0
400041118 "N05CF01" 0 1 2417 0
400041118 "N05CF01" 0 1 2571 0
400041118 "N05CF02" 0 1 2244 0
400041118 "N05CF02" 0 1 1970 0
400041118 "N05CF02" 0 1 2449 0
400041118 "N05CF02" 0 1 2003 0
400041118 "N05CF02" 0 1 2522 0
400041118 "N05CF02" 0 1 2287 0
400041118 "N05CF02" 0 1 2323 0
400041118 "N05CF02" 0 1 1945 0
end
label values drugclass drugclass1
label def drugclass1 0 "bzd", modify
label def drugclass1 1 "opioid", modify

any feedback would be greatly appreciated!

Thanks!

[Clyde Schechter]

This task is beyond the capabilities of -rangestat-, as far as I can see. But it can be done with -rangerun-, written by Robert Picard, and available from SSC.

encode atc_code, gen(atccode)

capture program drop one_observation
program define one_observation
    by patid drugclass atccode, sort: gen byte new_code = (_n == 1)
    by patid drugclass: replace new_code = sum(new_code)
    by patid drugclass: gen distinct = new_code[_N]
    by patid drugclass: keep if _n == _N
    keep patid drugclass distinct
    reshape wide distinct, i(patid) j(drugclass)
    exit
end

rangerun one_observation, by(patid) interval(dayzero, -30, 0) verbose
format patid %12.0f
mvencode distinct*, mv(0)
gen distinct_total = distinct0 + distinct1

At the end of this code, distinct0 will be the number of distinct drugs in drugclass 0 during the previous 30 day window, distinct1 is the number of distrinct drugs in drugclass 1 during the previous 30 day window, and distinct_total is the total number of distinct drugs, regardless of drugclass, in the previous 30 day window.

[Nick Cox]

Clyde gave an excellent answer, to which I have just two small footnotes.

rangestat allows you to specify Mata functions to extend its range. I don't especially recommend that in this case, but it is possible.

What I would note is that distinct (Stata Journal) is among various community-contributed commands that return the number of distinct values, so rangerun code could be written based on that.

[Vishal Sharma]

Thanks all!

[Vishal Sharma]

Hi Nick,

I dont think -distinct- works with -rangerun-. I get no output when I run the following (no error either)

program myprog3
distinct atccode
end
rangerun myprog3, interval(dayzero -30 0) by(patid)

* Example generated by -dataex-. To install: ssc install dataex
clear
input double patid long atccode int dayzero float drugclass
400002181 47 1901 1
400002804 30 1803 0
400002804 39 1843 0
400002804 39 1977 0
400002804 39 2067 0
400002804 39 2208 0
400002804 39 2303 0
400002804 39 2458 0
400002804 39 2556 0
400010442 30 2496 0
400010442 19 1908 1
400010442 20 1908 1
400010442 19 2175 1
400010442 19 2183 1
400040251 39 1813 0
400040251 25 1813 0
400040251 25 1814 0
400040251 25 1836 0
400040251 39 1836 0
400040251 39 1857 0
400040251 25 1857 0
400040251 39 1881 0
400040251 25 1881 0
400040251 39 1892 0
400040251 39 1907 0
400040251 25 1907 0
400040251 39 1928 0
400040251 25 1928 0
400040251 39 1944 0
400040251 25 1965 0
end
label values atccode atccode
label def atccode 19 "N02AJ13", modify
label def atccode 20 "N02AJ17", modify
label def atccode 25 "N03AE01", modify
label def atccode 30 "N05BA06", modify
label def atccode 39 "N05CF01", modify
label def atccode 47 "R05DA20", modify
label values drugclass drugclass1
label def drugclass1 0 "bzd", modify
label def drugclass1 1 "opioid", modify

[Nick Cox]

You say nothing in your code about where the results will go; you can’t expect anything except calculations to be made and then be overwritten by the next set. The help for rangerun gives examples. Note also how Clyde’s program explicitly generates new variables.

[Vishal Sharma]

I see what you mean Nick. I read the -help rangerun- and it doesnt explain how to store the output from -distinct- in a variable. any feedback?

thanks
Vishal

[Nick Cox]

You need to read the help for rangestat to see how it works; and then the help for distinct to see how it works. Expecting the latter inside the former is not what I meant.

This may help:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double patid str8 atc_code float(dpd_match dpd_bzra_match) int dayzero float drugclass
400002181 "R05DA20" 1 0 1901 1
400002804 "N05CF01" 0 1 2556 0
400002804 "N05CF01" 0 1 2208 0
400002804 "N05CF01" 0 1 2067 0
400002804 "N05CF01" 0 1 2458 0
400002804 "N05CF01" 0 1 1843 0
400002804 "N05CF01" 0 1 1977 0
400002804 "N05CF01" 0 1 2303 0
400010442 "N02AJ13" 1 0 2175 1
400010442 "N02AJ13" 1 0 1908 1
400010442 "N02AJ13" 1 0 2183 1
400010442 "N02AJ17" 1 0 1908 1
400010442 "N05BA06" 0 1 2496 0
400040251 "N02AJ06" 1 0 2326 1
400040251 "N03AE01" 0 1 1857 0
400040251 "N03AE01" 0 1 2222 0
400040251 "N03AE01" 0 1 1994 0
400040251 "N03AE01" 0 1 1881 0
400040251 "N03AE01" 0 1 2029 0
400040251 "N03AE01" 0 1 1907 0
400040251 "N03AE01" 0 1 2446 0
400040251 "N03AE01" 0 1 2080 0
400040251 "N03AE01" 0 1 2412 0
400040251 "N03AE01" 0 1 2586 0
400040251 "N03AE01" 0 1 2493 0
400040251 "N03AE01" 0 1 1965 0
400040251 "N03AE01" 0 1 2164 0
400040251 "N03AE01" 0 1 2188 0
400040251 "N03AE01" 0 1 2327 0
400040251 "N03AE01" 0 1 2302 0
400040251 "N03AE01" 0 1 2041 0
400040251 "N03AE01" 0 1 2530 0
400040251 "N03AE01" 0 1 2266 0
400040251 "N03AE01" 0 1 2133 0
400040251 "N03AE01" 0 1 2109 0
400040251 "N03AE01" 0 1 2547 0
400040251 "N03AE01" 0 1 1928 0
400040251 "N03AE01" 0 1 2373 0
400040251 "N03AE01" 0 1 2352 0
400040251 "N03AE01" 0 1 2466 0
400040251 "N03AE01" 0 1 1836 0
400040251 "N03AE01" 0 1 2245 0
400040251 "N05CF01" 0 1 1944 0
400040251 "N05CF01" 0 1 2148 0
400040251 "N05CF01" 0 1 2448 0
400040251 "N05CF01" 0 1 2511 0
400040251 "N05CF01" 0 1 2091 0
400040251 "N05CF01" 0 1 2579 0
400040251 "N05CF01" 0 1 2312 0
400040251 "N05CF01" 0 1 1928 0
400040251 "N05CF01" 0 1 1857 0
400040251 "N05CF01" 0 1 2204 0
400040251 "N05CF01" 0 1 2008 0
400040251 "N05CF01" 0 1 2229 0
400040251 "N05CF01" 0 1 1836 0
400040251 "N05CF01" 0 1 1977 0
400040251 "N05CF01" 0 1 2398 0
400040251 "N05CF01" 0 1 2283 0
400040251 "N05CF01" 0 1 2118 0
400040251 "N05CF01" 0 1 2341 0
400040251 "N05CF01" 0 1 1907 0
400040251 "N05CF01" 0 1 2547 0
400040251 "N05CF01" 0 1 2062 0
400040251 "N05CF01" 0 1 2257 0
400040251 "N05CF01" 0 1 2039 0
400040251 "N05CF01" 0 1 2607 0
400040251 "N05CF01" 0 1 2478 0
400040251 "N05CF01" 0 1 2428 0
400040251 "N05CF01" 0 1 1892 0
400040251 "N05CF01" 0 1 2368 0
400040251 "N05CF01" 0 1 1881 0
400040251 "N05CF01" 0 1 2175 0
400041118 "N02AA03" 1 0 2252 1
400041118 "N02AJ06" 1 0 2294 1
400041118 "N05BA06" 0 1 2373 0
400041118 "N05BA06" 0 1 1921 0
400041118 "N05BA06" 0 1 2481 0
400041118 "N05BA06" 0 1 2350 0
400041118 "N05CF01" 0 1 2481 0
400041118 "N05CF01" 0 1 2593 0
400041118 "N05CF01" 0 1 2522 0
400041118 "N05CF01" 0 1 2308 0
400041118 "N05CF01" 0 1 2395 0
400041118 "N05CF01" 0 1 2472 0
400041118 "N05CF01" 0 1 2417 0
400041118 "N05CF01" 0 1 2571 0
400041118 "N05CF02" 0 1 2244 0
400041118 "N05CF02" 0 1 1970 0
400041118 "N05CF02" 0 1 2449 0
400041118 "N05CF02" 0 1 2003 0
400041118 "N05CF02" 0 1 2522 0
400041118 "N05CF02" 0 1 2287 0
400041118 "N05CF02" 0 1 2323 0
400041118 "N05CF02" 0 1 1945 0
end
label values drugclass drugclass1
label def drugclass1 0 "bzd", modify
label def drugclass1 1 "opioid", modify

encode atc_code, gen(atccode)


program mydistinct 
    distinct atccode if drugclass == 0 
    gen wanted1 = r(ndistinct) 
    distinct atccode 
    gen wanted = r(ndistinct) 
end

format patid %12.0f 

sort patid dayzero 

rangerun mydistinct, by(patid) interval(dayzero -30 0) use(atccode drugclass) 

list patid dayzero drugclass atccode wanted*, sepby(patid) 

    +-------------------------------------------------------------+
     |     patid   dayzero   drugcl~s   atccode   wanted1   wanted |
     |-------------------------------------------------------------|
  1. | 400002181      1901     opioid   R05DA20         0        1 |
     |-------------------------------------------------------------|
  2. | 400002804      1843        bzd   N05CF01         1        1 |
  3. | 400002804      1977        bzd   N05CF01         1        1 |
  4. | 400002804      2067        bzd   N05CF01         1        1 |
  5. | 400002804      2208        bzd   N05CF01         1        1 |
  6. | 400002804      2303        bzd   N05CF01         1        1 |
  7. | 400002804      2458        bzd   N05CF01         1        1 |
  8. | 400002804      2556        bzd   N05CF01         1        1 |
     |-------------------------------------------------------------|
  9. | 400010442      1908     opioid   N02AJ17         0        2 |
 10. | 400010442      1908     opioid   N02AJ13         0        2 |
 11. | 400010442      2175     opioid   N02AJ13         0        1 |
 12. | 400010442      2183     opioid   N02AJ13         0        1 |
 13. | 400010442      2496        bzd   N05BA06         1        1 |
     |-------------------------------------------------------------|
 14. | 400040251      1836        bzd   N05CF01         2        2 |
 15. | 400040251      1836        bzd   N03AE01         2        2 |
 16. | 400040251      1857        bzd   N03AE01         2        2 |
 17. | 400040251      1857        bzd   N05CF01         2        2 |
 18. | 400040251      1881        bzd   N05CF01         2        2 |
 19. | 400040251      1881        bzd   N03AE01         2        2 |
 20. | 400040251      1892        bzd   N05CF01         2        2 |
 21. | 400040251      1907        bzd   N05CF01         2        2 |
 22. | 400040251      1907        bzd   N03AE01         2        2 |
 23. | 400040251      1928        bzd   N03AE01         2        2 |
 24. | 400040251      1928        bzd   N05CF01         2        2 |
 25. | 400040251      1944        bzd   N05CF01         2        2 |
 26. | 400040251      1965        bzd   N03AE01         2        2 |
 27. | 400040251      1977        bzd   N05CF01         2        2 |
 28. | 400040251      1994        bzd   N03AE01         2        2 |
 29. | 400040251      2008        bzd   N05CF01         2        2 |
 30. | 400040251      2029        bzd   N03AE01         2        2 |
 31. | 400040251      2039        bzd   N05CF01         2        2 |
 32. | 400040251      2041        bzd   N03AE01         2        2 |
 33. | 400040251      2062        bzd   N05CF01         2        2 |
 34. | 400040251      2080        bzd   N03AE01         2        2 |
 35. | 400040251      2091        bzd   N05CF01         2        2 |
 36. | 400040251      2109        bzd   N03AE01         2        2 |
 37. | 400040251      2118        bzd   N05CF01         2        2 |
 38. | 400040251      2133        bzd   N03AE01         2        2 |
 39. | 400040251      2148        bzd   N05CF01         2        2 |
 40. | 400040251      2164        bzd   N03AE01         2        2 |
 41. | 400040251      2175        bzd   N05CF01         2        2 |
 42. | 400040251      2188        bzd   N03AE01         2        2 |
 43. | 400040251      2204        bzd   N05CF01         2        2 |
 44. | 400040251      2222        bzd   N03AE01         2        2 |
 45. | 400040251      2229        bzd   N05CF01         2        2 |
 46. | 400040251      2245        bzd   N03AE01         2        2 |
 47. | 400040251      2257        bzd   N05CF01         2        2 |
 48. | 400040251      2266        bzd   N03AE01         2        2 |
 49. | 400040251      2283        bzd   N05CF01         2        2 |
 50. | 400040251      2302        bzd   N03AE01         2        2 |
 51. | 400040251      2312        bzd   N05CF01         2        2 |
 52. | 400040251      2326     opioid   N02AJ06         2        3 |
 53. | 400040251      2327        bzd   N03AE01         2        3 |
 54. | 400040251      2341        bzd   N05CF01         2        3 |
 55. | 400040251      2352        bzd   N03AE01         2        3 |
 56. | 400040251      2368        bzd   N05CF01         2        2 |
 57. | 400040251      2373        bzd   N03AE01         2        2 |
 58. | 400040251      2398        bzd   N05CF01         2        2 |
 59. | 400040251      2412        bzd   N03AE01         2        2 |
 60. | 400040251      2428        bzd   N05CF01         2        2 |
 61. | 400040251      2446        bzd   N03AE01         2        2 |
 62. | 400040251      2448        bzd   N05CF01         2        2 |
 63. | 400040251      2466        bzd   N03AE01         2        2 |
 64. | 400040251      2478        bzd   N05CF01         2        2 |
 65. | 400040251      2493        bzd   N03AE01         2        2 |
 66. | 400040251      2511        bzd   N05CF01         2        2 |
 67. | 400040251      2530        bzd   N03AE01         2        2 |
 68. | 400040251      2547        bzd   N05CF01         2        2 |
 69. | 400040251      2547        bzd   N03AE01         2        2 |
 70. | 400040251      2579        bzd   N05CF01         1        1 |
 71. | 400040251      2586        bzd   N03AE01         2        2 |
 72. | 400040251      2607        bzd   N05CF01         2        2 |
     |-------------------------------------------------------------|
 73. | 400041118      1921        bzd   N05BA06         1        1 |
 74. | 400041118      1945        bzd   N05CF02         2        2 |
 75. | 400041118      1970        bzd   N05CF02         1        1 |
 76. | 400041118      2003        bzd   N05CF02         1        1 |
 77. | 400041118      2244        bzd   N05CF02         1        1 |
 78. | 400041118      2252     opioid   N02AA03         1        2 |
 79. | 400041118      2287        bzd   N05CF02         1        1 |
 80. | 400041118      2294     opioid   N02AJ06         1        2 |
 81. | 400041118      2308        bzd   N05CF01         2        3 |
 82. | 400041118      2323        bzd   N05CF02         2        3 |
 83. | 400041118      2350        bzd   N05BA06         2        2 |
 84. | 400041118      2373        bzd   N05BA06         1        1 |
 85. | 400041118      2395        bzd   N05CF01         2        2 |
 86. | 400041118      2417        bzd   N05CF01         1        1 |
 87. | 400041118      2449        bzd   N05CF02         1        1 |
 88. | 400041118      2472        bzd   N05CF01         2        2 |
 89. | 400041118      2481        bzd   N05BA06         2        2 |
 90. | 400041118      2481        bzd   N05CF01         2        2 |
 91. | 400041118      2522        bzd   N05CF02         2        2 |
 92. | 400041118      2522        bzd   N05CF01         2        2 |
 93. | 400041118      2571        bzd   N05CF01         1        1 |
 94. | 400041118      2593        bzd   N05CF01         1        1 |
     +-------------------------------------------------------------+

[Vishal Sharma]

Thanks Nick! I understand the logic now. Much appreciated!

[Vishal Sharma]

Thanks Clyde for post #2, it worked great!

[James Voss]

Quick question on rangestat:

Can we use rangestat to get the statistics on a dataset by using an - interval - with two Key Variables?

For example, a data set for which each observation includes:
-- employer type (EMPSTRUCTURE)
-- geographic region (COUNTY)

I'd like to have the rangestat results by each employer type within each geographic region.

I don't think so because:
1) I cannot find any reference to this, and
2) I don't can't figure out how it would be written within the the phrase - interval( keyvar low high) -


But, I thought I'd ask before giving up.

Thanks!

[Clyde Schechter]

Well, your description is a bit vague and perhaps I'm missing something, but this does not sound like a job for -rangestat-. Both EMPSTRUCTURE and COUNTY sound like they are discrete, nominal variables. So it would not even make sense to define an "interval" on either of them as the ordering of the values used to encode particular types and regions are just arbitrary.

Rather it sounds like this is a job for either -collapse- (if you want to reduce the data set to one observation per combination of EMPSTRUCTURE and COUNTY) or -by EMPSTRCTURE COUNTY, sort: - with -gen- or -egen- and an appropriate function for the particular statistic you are looking for. For example:

by EMPSTRUCTURE COUNTY, sort: egen mean_X = mean(X)
by EMPSTRUCTURE COUNTY: egen sd_X = sd(X)
by EMPSTRUCTURE COUNTY: egen count_X = count(X) // # OF NON-MISSING VALUES OF X

All of the statistics, other than regression coefficients, that -rangestat- computes can be calculated with -gen- or -egen-, or with -collapse-. For groupwise regression outputs, use -runby-, written by Robert Picard and me, available from SSC. (Read -help runby- carefully before using it.)

[Nick Cox]

You can do that. Just specify e.g.

int(empstructure 0 0) by(county)

At worst empstructure is string and you need a numeric version first which you can get using encode or egen.

[Clyde Schechter]

Nick is, of course, correct that it can be done with -rangestat-. But I think it shouldn't. Conceptually it is wrong to treat empstructure as having meaningful numeric values when it doesn't. While that won't get you wrong answers here, sloppy thinking habits will lead to problems in most situations, and should be avoided. Moreover, if your data set is large, doing this with -rangestat- will be slower than with the -by- commands. (-collapse- would be slower still, but produces a different data set of results, so the comparison is not apt here.)

[Nick Cox]

I would say the trade-off here is hard to judge without more detail. rangestat was intended primarily for moving windows and its applicability to fixed windows is a side-effect. On the other hand it can be used to call up several summaries in one command line.