Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Total dose when prescriptiondate is <365 days apart

    Dear stata-community,
    I need to calculate the average dosage of med1 and med2 if there are less than 365 days between the prescriptions. (So in theory it may be a presc. in june 2012, jan 2013 and oct 2013, and it will still count as 1 "episode".) I would also like to do find the average dosage if the prescription is from "derm" or "gp" (two different specialties.

    * Example generated by -dataex-. For more info, type help dataex
    clear
    input long patID int date byte(dose med1 med2 derm gp)
    79837 19047 . 0 0 . 1
    364798 21062 . 0 0 1 .
    428874 19179 . 0 0 . 1
    442005 19712 . 0 0 . 1
    583497 19111 . 0 0 1 .
    626405 19681 . 0 0 . 1
    629475 21698 . 0 0 . .
    655246 20208 . 0 0 . 1
    714377 21105 . 0 0 1 .
    727445 21889 . 0 0 1 .
    761109 19571 . 0 0 . 1
    821441 19225 . 0 0 . 1
    1214372 19754 . 0 0 . .
    1411769 19375 . 0 0 1 .
    1455113 19965 66 0 1 . 1
    1455113 19908 66 0 1 . 1
    1533682 20997 . 0 0 . 1
    1556837 19440 50 0 1 1 .
    1556837 19486 50 1 0 . .
    1563456 19100 . 0 0 . 1
    1739675 21663 . 0 0 1 .
    1802380 21836 . 0 0 . 1
    1826862 19394 . 0 0 . 1
    1936883 19774 . 0 0 . .
    2166745 19016 . 0 0 . 1
    2197013 21538 . 0 0 1 .
    2251397 19172 . 0 0 1 .
    2384595 21854 . 0 0 1 .
    2392669 21565 . 0 0 1 .
    2612565 19317 100 0 1 . 1
    2612565 19262 50 1 0 . 1
    2612565 19316 . 0 0 . 1
    2612565 19246 . 0 0 . .
    2612565 19269 50 0 1 1 .
    2630090 21899 . 0 0 . 1
    2634367 21419 . 0 0 . 1
    2649134 19108 . 0 0 . 1
    2690304 21444 . 0 0 1 .
    2711458 19176 100 1 0 . 1
    2813649 20272 100 0 1 . 1
    2938509 21363 . 0 0 . .
    3066528 21257 . 0 0 . 1
    3073766 21094 . 0 0 . 1
    3143942 20766 . 0 0 . 1
    3175274 20259 . 0 0 . 1
    3334669 21577 . 0 0 1 .
    3370804 20776 100 1 0 1 .
    3395265 21176 . 0 0 1 .
    3397587 19206 . 0 0 . 1
    3428395 19206 . 0 0 . 1
    3594852 19073 . 0 0 . 1
    3673975 21532 . 0 0 . 1
    3681737 20937 50 1 0 1 .
    3681737 21004 50 0 1 . 1
    3681737 21444 50 1 0 1 .
    3770383 19004 . 0 0 1 .
    3877739 20947 . 0 0 . 1
    3908133 19093 . 0 0 . 1
    3915001 21529 . 0 0 . 1
    3945122 20061 . 0 0 . 1
    3962097 19136 . 0 0 . 1
    3985196 19514 . 0 0 . 1
    4007377 19415 100 0 1 . 1
    4078105 20921 . 0 0 1 .
    4106719 19967 . 0 0 . 1
    4140083 19505 . 0 0 . 1
    4210585 19474 . 0 0 1 .
    4214970 20305 . 0 0 1 .
    4301991 21698 . 0 0 . 1
    4303726 20598 50 1 0 1 .
    4303726 20342 100 1 0 . .
    4303726 20355 50 0 1 . 1
    4336014 21465 . 0 0 . 1
    4459597 19331 66 1 0 . 1
    4459597 19349 66 0 1 1 .
    4459597 19368 100 1 0 1 .
    4495250 21762 . 0 0 1 .
    4544744 21565 . 0 0 . 1
    4700534 19789 . 0 0 . 1
    4703316 19418 . 0 0 . 1
    4744878 19228 100 1 0 . 1
    4776810 19015 . 0 0 . 1
    4778609 20342 . 0 0 1 .
    4816331 19073 . 0 0 . 1
    4853029 20550 . 0 0 1 .
    4896584 20466 . 0 0 . 1
    4911393 19026 . 0 0 . 1
    4919757 19013 . 0 0 . 1
    4961412 19165 . 0 0 . 1
    5115704 19080 . 0 0 . 1
    5215923 19368 . 0 0 . 1
    5377345 19233 . 0 0 . 1
    6237863 20838 50 1 0 1 .
    6237863 21892 50 1 0 1 .
    6237863 20859 50 1 0 1 .
    8112089 19082 50 0 1 . 1
    8241805 20915 . 0 0 . 1
    10999479 20167 . 0 0 . 1
    11288159 19474 . 0 0 . 1
    20680212 21223 . 0 0 1 .
    end
    format %td date
    [/CODE]
    Tags: None
  • #2
    This may help. Note that missing counts as >365, so that the first spell starts with the first observation for each patient.

    See also https://www.stata-journal.com/articl...article=dm0029



    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input long patID int date byte(dose med1 med2 derm gp)
79837 19047 . 0 0 . 1
364798 21062 . 0 0 1 .
428874 19179 . 0 0 . 1
442005 19712 . 0 0 . 1
583497 19111 . 0 0 1 .
626405 19681 . 0 0 . 1
629475 21698 . 0 0 . .
655246 20208 . 0 0 . 1
714377 21105 . 0 0 1 .
727445 21889 . 0 0 1 .
761109 19571 . 0 0 . 1
821441 19225 . 0 0 . 1
1214372 19754 . 0 0 . .
1411769 19375 . 0 0 1 .
1455113 19965 66 0 1 . 1
1455113 19908 66 0 1 . 1
1533682 20997 . 0 0 . 1
1556837 19440 50 0 1 1 .
1556837 19486 50 1 0 . .
1563456 19100 . 0 0 . 1
1739675 21663 . 0 0 1 .
1802380 21836 . 0 0 . 1
1826862 19394 . 0 0 . 1
1936883 19774 . 0 0 . .
2166745 19016 . 0 0 . 1
2197013 21538 . 0 0 1 .
2251397 19172 . 0 0 1 .
2384595 21854 . 0 0 1 .
2392669 21565 . 0 0 1 .
2612565 19317 100 0 1 . 1
2612565 19262 50 1 0 . 1
2612565 19316 . 0 0 . 1
2612565 19246 . 0 0 . .
2612565 19269 50 0 1 1 .
2630090 21899 . 0 0 . 1
2634367 21419 . 0 0 . 1
2649134 19108 . 0 0 . 1
2690304 21444 . 0 0 1 .
2711458 19176 100 1 0 . 1
2813649 20272 100 0 1 . 1
2938509 21363 . 0 0 . .
3066528 21257 . 0 0 . 1
3073766 21094 . 0 0 . 1
3143942 20766 . 0 0 . 1
3175274 20259 . 0 0 . 1
3334669 21577 . 0 0 1 .
3370804 20776 100 1 0 1 .
3395265 21176 . 0 0 1 .
3397587 19206 . 0 0 . 1
3428395 19206 . 0 0 . 1
3594852 19073 . 0 0 . 1
3673975 21532 . 0 0 . 1
3681737 20937 50 1 0 1 .
3681737 21004 50 0 1 . 1
3681737 21444 50 1 0 1 .
3770383 19004 . 0 0 1 .
3877739 20947 . 0 0 . 1
3908133 19093 . 0 0 . 1
3915001 21529 . 0 0 . 1
3945122 20061 . 0 0 . 1
3962097 19136 . 0 0 . 1
3985196 19514 . 0 0 . 1
4007377 19415 100 0 1 . 1
4078105 20921 . 0 0 1 .
4106719 19967 . 0 0 . 1
4140083 19505 . 0 0 . 1
4210585 19474 . 0 0 1 .
4214970 20305 . 0 0 1 .
4301991 21698 . 0 0 . 1
4303726 20598 50 1 0 1 .
4303726 20342 100 1 0 . .
4303726 20355 50 0 1 . 1
4336014 21465 . 0 0 . 1
4459597 19331 66 1 0 . 1
4459597 19349 66 0 1 1 .
4459597 19368 100 1 0 1 .
4495250 21762 . 0 0 1 .
4544744 21565 . 0 0 . 1
4700534 19789 . 0 0 . 1
4703316 19418 . 0 0 . 1
4744878 19228 100 1 0 . 1
4776810 19015 . 0 0 . 1
4778609 20342 . 0 0 1 .
4816331 19073 . 0 0 . 1
4853029 20550 . 0 0 1 .
4896584 20466 . 0 0 . 1
4911393 19026 . 0 0 . 1
4919757 19013 . 0 0 . 1
4961412 19165 . 0 0 . 1
5115704 19080 . 0 0 . 1
5215923 19368 . 0 0 . 1
5377345 19233 . 0 0 . 1
6237863 20838 50 1 0 1 .
6237863 21892 50 1 0 1 .
6237863 20859 50 1 0 1 .
8112089 19082 50 0 1 . 1
8241805 20915 . 0 0 . 1
10999479 20167 . 0 0 . 1
11288159 19474 . 0 0 . 1
20680212 21223 . 0 0 1 .
end
format %td date

bysort patID (date): gen gap = date - date[_n-1]
by patID : gen spell = sum(gap > 365)  

egen mean1 = mean(med1), by(patID spell)
egen mean2 = mean(med2), by(patID spell)

list, sepby(patID spell)

     +--------------------------------------------------------------------------------------------+
     |    patID        date   dose   med1   med2   derm   gp    gap   spell      mean1      mean2 |
     |--------------------------------------------------------------------------------------------|
  1. |    79837   24feb2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
  2. |   364798   31aug2017      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
  3. |   428874   05jul2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
  4. |   442005   20dec2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
  5. |   583497   28apr2012      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
  6. |   626405   19nov2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
  7. |   629475   29may2019      .      0      0      .    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
  8. |   655246   30apr2015      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
  9. |   714377   13oct2017      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 10. |   727445   06dec2019      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 11. |   761109   01aug2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 12. |   821441   20aug2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 13. |  1214372   31jan2014      .      0      0      .    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 14. |  1411769   17jan2013      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 15. |  1455113   04jul2014     66      0      1      .    1      .       1          0          1 |
 16. |  1455113   30aug2014     66      0      1      .    1     57       1          0          1 |
     |--------------------------------------------------------------------------------------------|
 17. |  1533682   27jun2017      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 18. |  1556837   23mar2013     50      0      1      1    .      .       1         .5         .5 |
 19. |  1556837   08may2013     50      1      0      .    .     46       1         .5         .5 |
     |--------------------------------------------------------------------------------------------|
 20. |  1563456   17apr2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 21. |  1739675   24apr2019      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 22. |  1802380   14oct2019      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 23. |  1826862   05feb2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 24. |  1936883   20feb2014      .      0      0      .    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 25. |  2166745   24jan2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 26. |  2197013   20dec2018      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 27. |  2251397   28jun2012      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 28. |  2384595   01nov2019      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 29. |  2392669   16jan2019      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 30. |  2612565   10sep2012      .      0      0      .    .      .       1         .2         .4 |
 31. |  2612565   26sep2012     50      1      0      .    1     16       1         .2         .4 |
 32. |  2612565   03oct2012     50      0      1      1    .      7       1         .2         .4 |
 33. |  2612565   19nov2012      .      0      0      .    1     47       1         .2         .4 |
 34. |  2612565   20nov2012    100      0      1      .    1      1       1         .2         .4 |
     |--------------------------------------------------------------------------------------------|
 35. |  2630090   16dec2019      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 36. |  2634367   23aug2018      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 37. |  2649134   25apr2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 38. |  2690304   17sep2018      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 39. |  2711458   02jul2012    100      1      0      .    1      .       1          1          0 |
     |--------------------------------------------------------------------------------------------|
 40. |  2813649   03jul2015    100      0      1      .    1      .       1          0          1 |
     |--------------------------------------------------------------------------------------------|
 41. |  2938509   28jun2018      .      0      0      .    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 42. |  3066528   14mar2018      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 43. |  3073766   02oct2017      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 44. |  3143942   08nov2016      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 45. |  3175274   20jun2015      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 46. |  3334669   28jan2019      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 47. |  3370804   18nov2016    100      1      0      1    .      .       1          1          0 |
     |--------------------------------------------------------------------------------------------|
 48. |  3395265   23dec2017      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 49. |  3397587   01aug2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 50. |  3428395   01aug2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 51. |  3594852   21mar2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 52. |  3673975   14dec2018      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 53. |  3681737   28apr2017     50      1      0      1    .      .       1         .5         .5 |
 54. |  3681737   04jul2017     50      0      1      .    1     67       1         .5         .5 |
     |--------------------------------------------------------------------------------------------|
 55. |  3681737   17sep2018     50      1      0      1    .    440       2          1          0 |
     |--------------------------------------------------------------------------------------------|
 56. |  3770383   12jan2012      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 57. |  3877739   08may2017      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 58. |  3908133   10apr2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 59. |  3915001   11dec2018      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 60. |  3945122   04dec2014      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 61. |  3962097   23may2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 62. |  3985196   05jun2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 63. |  4007377   26feb2013    100      0      1      .    1      .       1          0          1 |
     |--------------------------------------------------------------------------------------------|
 64. |  4078105   12apr2017      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 65. |  4106719   01sep2014      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 66. |  4140083   27may2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 67. |  4210585   26apr2013      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 68. |  4214970   05aug2015      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 69. |  4301991   29may2019      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 70. |  4303726   11sep2015    100      1      0      .    .      .       1   .6666667   .3333333 |
 71. |  4303726   24sep2015     50      0      1      .    1     13       1   .6666667   .3333333 |
 72. |  4303726   24may2016     50      1      0      1    .    243       1   .6666667   .3333333 |
     |--------------------------------------------------------------------------------------------|
 73. |  4336014   08oct2018      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 74. |  4459597   04dec2012     66      1      0      .    1      .       1   .6666667   .3333333 |
 75. |  4459597   22dec2012     66      0      1      1    .     18       1   .6666667   .3333333 |
 76. |  4459597   10jan2013    100      1      0      1    .     19       1   .6666667   .3333333 |
     |--------------------------------------------------------------------------------------------|
 77. |  4495250   01aug2019      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 78. |  4544744   16jan2019      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 79. |  4700534   07mar2014      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 80. |  4703316   01mar2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 81. |  4744878   23aug2012    100      1      0      .    1      .       1          1          0 |
     |--------------------------------------------------------------------------------------------|
 82. |  4776810   23jan2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 83. |  4778609   11sep2015      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 84. |  4816331   21mar2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 85. |  4853029   06apr2016      .      0      0      1    .      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 86. |  4896584   13jan2016      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 87. |  4911393   03feb2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 88. |  4919757   21jan2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 89. |  4961412   21jun2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 90. |  5115704   28mar2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 91. |  5215923   10jan2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 92. |  5377345   28aug2012      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 93. |  6237863   19jan2017     50      1      0      1    .      .       1          1          0 |
 94. |  6237863   09feb2017     50      1      0      1    .     21       1          1          0 |
     |--------------------------------------------------------------------------------------------|
 95. |  6237863   09dec2019     50      1      0      1    .   1033       2          1          0 |
     |--------------------------------------------------------------------------------------------|
 96. |  8112089   30mar2012     50      0      1      .    1      .       1          0          1 |
     |--------------------------------------------------------------------------------------------|
 97. |  8241805   06apr2017      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 98. | 10999479   20mar2015      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
 99. | 11288159   26apr2013      .      0      0      .    1      .       1          0          0 |
     |--------------------------------------------------------------------------------------------|
100. | 20680212   08feb2018      .      0      0      1    .      .       1          0          0 |
     +--------------------------------------------------------------------------------------------+

. 
.

    Comment

    • #3
      Thank you, but that is not quite the result I'm looking for. I want "total(dose)" for each med, per patID. So for instance patID 4459597 in lines 74-76 I would want the result: dose_med1: 166, dose_med2: 66.
      And for patID 6237863 in lines 93-95: dose_med1: 100, and then again dose_med1: 50, for the second treatment-episode/gap

      Comment

      • #4
        Question title says total; question text says "average" (twice). So, I answered using the mean() function of egen. You really want totals, so that's fine: use the total() function instead.

        Comment

        • #5
          I'm sorry, I see that my question was not so clear. But yes, total is what I need, so thank you!

          Is there somehow I can calculate the gap only between dates where the patID got a medicine (med1 or med2)? And the dates where there is no medication would be skipped?
          For instance in line 34 (patID 2612565); the gap would be "48", and not "47" in line 33 and "1" in line 34.
          I think I can make a new dataset by "keep if med1==1 | med2==1", but can I do it without making a new dta.-file?

          Comment

          • #6
            Yes. Just separate observations you want to use from those you don't.

            Code:
             
* Example generated by -dataex-. For more info, type help dataex
clear
input long patID int date byte(dose med1 med2 derm gp)
1455113 19965  66 0 1 . 1
1455113 19908  66 0 1 . 1
1556837 19440  50 0 1 1 .
1556837 19486  50 1 0 . .
2612565 19317 100 0 1 . 1
2612565 19262  50 1 0 . 1
2612565 19316   . 0 0 . 1
2612565 19246   . 0 0 . .
2612565 19269  50 0 1 1 .
3681737 20937  50 1 0 1 .
3681737 21004  50 0 1 . 1
3681737 21444  50 1 0 1 .
4303726 20598  50 1 0 1 .
4303726 20342 100 1 0 . .
4303726 20355  50 0 1 . 1
4459597 19331  66 1 0 . 1
4459597 19349  66 0 1 1 .
4459597 19368 100 1 0 1 .
6237863 20838  50 1 0 1 .
6237863 21892  50 1 0 1 .
6237863 20859  50 1 0 1 .
end
format %td date

gen touse = med1 == 1 | med2 == 1 
bysort touse patID (date) : gen gap = date - date[_n-1] if touse 
by touse patID : gen spell = sum(gap > 365) if touse 

list, sepby(touse patID spell)


     +-----------------------------------------------------------------------------+
     |   patID        date   dose   med1   med2   derm   gp   touse    gap   spell |
     |-----------------------------------------------------------------------------|
  1. | 2612565   10sep2012      .      0      0      .    .       0      .       . |
  2. | 2612565   19nov2012      .      0      0      .    1       0      .       . |
     |-----------------------------------------------------------------------------|
  3. | 1455113   04jul2014     66      0      1      .    1       1      .       1 |
  4. | 1455113   30aug2014     66      0      1      .    1       1     57       1 |
     |-----------------------------------------------------------------------------|
  5. | 1556837   23mar2013     50      0      1      1    .       1      .       1 |
  6. | 1556837   08may2013     50      1      0      .    .       1     46       1 |
     |-----------------------------------------------------------------------------|
  7. | 2612565   26sep2012     50      1      0      .    1       1      .       1 |
  8. | 2612565   03oct2012     50      0      1      1    .       1      7       1 |
  9. | 2612565   20nov2012    100      0      1      .    1       1     48       1 |
     |-----------------------------------------------------------------------------|
 10. | 3681737   28apr2017     50      1      0      1    .       1      .       1 |
 11. | 3681737   04jul2017     50      0      1      .    1       1     67       1 |
     |-----------------------------------------------------------------------------|
 12. | 3681737   17sep2018     50      1      0      1    .       1    440       2 |
     |-----------------------------------------------------------------------------|
 13. | 4303726   11sep2015    100      1      0      .    .       1      .       1 |
 14. | 4303726   24sep2015     50      0      1      .    1       1     13       1 |
 15. | 4303726   24may2016     50      1      0      1    .       1    243       1 |
     |-----------------------------------------------------------------------------|
 16. | 4459597   04dec2012     66      1      0      .    1       1      .       1 |
 17. | 4459597   22dec2012     66      0      1      1    .       1     18       1 |
 18. | 4459597   10jan2013    100      1      0      1    .       1     19       1 |
     |-----------------------------------------------------------------------------|
 19. | 6237863   19jan2017     50      1      0      1    .       1      .       1 |
 20. | 6237863   09feb2017     50      1      0      1    .       1     21       1 |
     |-----------------------------------------------------------------------------|
 21. | 6237863   09dec2019     50      1      0      1    .       1   1033       2 |
     +-----------------------------------------------------------------------------+

            Comment

            • #7
              Thank you again!
              The "touse" is "langtetra". I created tot_spell:
              egen tot_spell= total(dose) if langtetra==1, by (patID spell)

              How can I find/"tag" the tot_spell only once per spell, per patID? The result I really want is to see the average dose per spell (episode).

              Code:
              * Example generated by -dataex-. To install: ssc install dataex
clear
input long patID float(date langtetra dose gap spell tot_spell)
76779 19058 1  50    . 1  50
76779 21626 1  50 2568 2  50
76796 19109 1  50    . 1  50
76804 21509 1  50    . 1  50
76806 20612 1  50    . 1 100
76806 20685 1  50   73 1 100
76812 19108 1  50    . 1 250
76812 19197 1  50   89 1 250
76812 19319 1  50  122 1 250
76812 19423 1  50  104 1 250
76812 19696 1  50  273 1 250
76837 19709 1  50    . 1  50
76911 20403 1  50    . 1 200
76911 20458 1  50   55 1 200
76911 20511 1  50   53 1 200
76911 20581 1  50   70 1 200
77072 20485 1  50    . 1 100
77072 20544 1  50   59 1 100
77072 21565 1  50 1021 2 200
77072 21595 1  50   30 2 200
77072 21862 1  50  267 2 200
77072 21875 1  50   13 2 200
77103 19355 1  50    . 1 100
77103 19425 1  50   70 1 100
77103 20381 1  50  956 2 200
77103 20441 1  50   60 2 200
77103 20597 1  50  156 2 200
77103 20780 1  50  183 2 200
77516 19086 1  60    . 1 120
77516 19107 1  60   21 1 120
77675 20170 1  50    . 1 100
77675 20251 1  50   81 1 100
77745 21265 1  50    . 1  50
77745 21803 1  50  538 2  50
77906 19149 1  50    . 1 100
77906 19286 1  50  137 1 100
77930 21346 1  50    . 1 250
77930 21428 1  50   82 1 250
77930 21503 1  50   75 1 250
77930 21566 1  50   63 1 250
77930 21632 1  50   66 1 250
77939 19976 1  50    . 1  50
78173 20801 1  60    . 1  60
78624 19607 1  60    . 1 110
78624 19767 1  50  160 1 110
78624 21909 1  50 2142 2  50
78663 20045 1  50    . 1  50
78663 21256 1  50 1211 2 100
78663 21365 1  50  109 2 100
end
format %td date

              Comment

              Previous Next
              Working...
              X