Announcement

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

    Cumunlative sum between dates

    I would like to generate a cumulative sum of the variable DosesAdministered that occur between the administereddate variable and the createdon variable. This represents the queue of outstanding doses that have not yet been created on each date. Does anyone have a suggestion on some syntax that would work for this purpose? Provided some example data below. I've included weekstartdate variable, as I intend to collapse the data by this weekly start date for graphing purposes. Kind thanks in advance for any guidance.

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input float administereddate byte DosesAdministered float createdon int weekstartdate
23254 .     . 23249
23255 .     . 23249
23256 .     . 23256
23257 .     . 23256
23258 .     . 23256
23259 .     . 23256
23260 .     . 23256
23261 .     . 23256
23262 .     . 23256
23263 .     . 23263
23264 .     . 23263
23265 .     . 23263
23266 .     . 23263
23267 .     . 23263
23268 .     . 23263
23269 .     . 23263
23270 .     . 23270
23271 .     . 23270
23272 .     . 23270
23273 .     . 23270
23274 .     . 23270
23275 .     . 23270
23276 .     . 23270
23277 .     . 23277
23278 .     . 23277
23279 .     . 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23281 1 23285 23277
23281 1 23285 23277
23281 1 23285 23277
end
format %tdDD-Mon-YY administereddate
format %tdDD-Mon-YY createdon
format %tdnn/dd/CCYY weekstartdate
    Tags: None
  • #2
    Like this?

    Code:
    frame put administereddate createdon  DosesAdministered if !missing(createdon), into(count)
frame count: collapse (sum) DosesAdministered, by(administereddate createdon)
frame count:  bys administereddate (createdon): gen wanted= sum(DosesAdministered)
frlink m:1 administereddate createdon, frame(count)
frget wanted, from(count)
drop count
frame drop count
    Res.:

    Code:
    . l, sep(0)

     +-------------------------------------------------------+
     | adminis~e   DosesA~d   createdon   weeksta~e   wanted |
     |-------------------------------------------------------|
  1. | 01-Sep-23          .           .   8/27/2023        . |
  2. | 02-Sep-23          .           .   8/27/2023        . |
  3. | 03-Sep-23          .           .    9/3/2023        . |
  4. | 04-Sep-23          .           .    9/3/2023        . |
  5. | 05-Sep-23          .           .    9/3/2023        . |
  6. | 06-Sep-23          .           .    9/3/2023        . |
  7. | 07-Sep-23          .           .    9/3/2023        . |
  8. | 08-Sep-23          .           .    9/3/2023        . |
  9. | 09-Sep-23          .           .    9/3/2023        . |
 10. | 10-Sep-23          .           .   9/10/2023        . |
 11. | 11-Sep-23          .           .   9/10/2023        . |
 12. | 12-Sep-23          .           .   9/10/2023        . |
 13. | 13-Sep-23          .           .   9/10/2023        . |
 14. | 14-Sep-23          .           .   9/10/2023        . |
 15. | 15-Sep-23          .           .   9/10/2023        . |
 16. | 16-Sep-23          .           .   9/10/2023        . |
 17. | 17-Sep-23          .           .   9/17/2023        . |
 18. | 18-Sep-23          .           .   9/17/2023        . |
 19. | 19-Sep-23          .           .   9/17/2023        . |
 20. | 20-Sep-23          .           .   9/17/2023        . |
 21. | 21-Sep-23          .           .   9/17/2023        . |
 22. | 22-Sep-23          .           .   9/17/2023        . |
 23. | 23-Sep-23          .           .   9/17/2023        . |
 24. | 24-Sep-23          .           .   9/24/2023        . |
 25. | 25-Sep-23          .           .   9/24/2023        . |
 26. | 26-Sep-23          .           .   9/24/2023        . |
 27. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 28. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 29. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 30. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 31. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 32. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 33. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 34. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 35. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 36. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 37. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 38. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 39. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 40. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 41. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 42. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 43. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 44. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 45. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 46. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 47. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 48. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 49. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 50. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 51. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 52. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 53. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 54. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 55. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 56. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 57. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 58. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 59. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 60. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 61. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 62. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 63. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 64. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 65. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 66. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 67. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 68. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 69. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 70. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 71. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 72. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 73. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 74. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 75. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 76. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 77. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 78. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 79. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 80. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 81. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 82. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 83. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 84. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 85. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 86. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 87. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 88. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 89. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 90. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 91. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 92. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 93. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 94. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 95. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 96. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 97. | 27-Sep-23          1   28-Sep-23   9/24/2023       45 |
 98. | 28-Sep-23          1   02-Oct-23   9/24/2023        3 |
 99. | 28-Sep-23          1   02-Oct-23   9/24/2023        3 |
100. | 28-Sep-23          1   02-Oct-23   9/24/2023        3 |
     +-------------------------------------------------------+

    Comment

    • #3
      Hi Andrew Musau thank you for your reply. I think something like this would work, but it may need some more adjustments. Since there was a total sum of 71 doses administered on 27-Sep-23, but none had been entered on 27-Sep-23, I was expecting the wanted=71 for all on this date (since the 'created on' date had not occurred yet for any of these doses. Following this, the next day 28-Sept-23 would include the previous doses not entered (71), plus doses administered on 28-Sep-23, minus any doses that had a createdon date of 28-Sep-23.

      I was hoping to find a way to get a cumulative total of doses per day that had not yet been created. Let me know if you need any further clarification, and I can try to see what might work. I appreciate your assistance.

      Comment

      • #4
        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input float administereddate byte DosesAdministered float createdon int weekstartdate
23254 .     . 23249
23255 .     . 23249
23256 .     . 23256
23257 .     . 23256
23258 .     . 23256
23259 .     . 23256
23260 .     . 23256
23261 .     . 23256
23262 .     . 23256
23263 .     . 23263
23264 .     . 23263
23265 .     . 23263
23266 .     . 23263
23267 .     . 23263
23268 .     . 23263
23269 .     . 23263
23270 .     . 23270
23271 .     . 23270
23272 .     . 23270
23273 .     . 23270
23274 .     . 23270
23275 .     . 23270
23276 .     . 23270
23277 .     . 23277
23278 .     . 23277
23279 .     . 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23280 1 23285 23277
23280 1 23281 23277
23280 1 23281 23277
23281 1 23285 23277
23281 1 23285 23277
23281 1 23285 23277
end
format %tdDD-Mon-YY administereddate
format %tdDD-Mon-YY createdon
format %tdnn/dd/CCYY weekstartdate

frame put administereddate createdon  DosesAdministered if !missing(createdon), into(count)
frame count{
    rename (administereddate createdon) time=
    gen obsno=_n
    reshape long time, i(obsno) j(which) string
    sort time which
    replace DosesAdministered= -DosesAdministered if which=="createdon"
    collapse (sum) DosesAdministered, by(time)
    gen wanted= sum(Doses)
}
frlink m:1 administereddate, frame(count time)
frget wanted, from(count)
drop count
frame drop count
        Res.:

        Code:
        . l, sep(0)

     +-------------------------------------------------------+
     | adminis~e   DosesA~d   createdon   weeksta~e   wanted |
     |-------------------------------------------------------|
  1. | 01-Sep-23          .           .   8/27/2023        . |
  2. | 02-Sep-23          .           .   8/27/2023        . |
  3. | 03-Sep-23          .           .    9/3/2023        . |
  4. | 04-Sep-23          .           .    9/3/2023        . |
  5. | 05-Sep-23          .           .    9/3/2023        . |
  6. | 06-Sep-23          .           .    9/3/2023        . |
  7. | 07-Sep-23          .           .    9/3/2023        . |
  8. | 08-Sep-23          .           .    9/3/2023        . |
  9. | 09-Sep-23          .           .    9/3/2023        . |
 10. | 10-Sep-23          .           .   9/10/2023        . |
 11. | 11-Sep-23          .           .   9/10/2023        . |
 12. | 12-Sep-23          .           .   9/10/2023        . |
 13. | 13-Sep-23          .           .   9/10/2023        . |
 14. | 14-Sep-23          .           .   9/10/2023        . |
 15. | 15-Sep-23          .           .   9/10/2023        . |
 16. | 16-Sep-23          .           .   9/10/2023        . |
 17. | 17-Sep-23          .           .   9/17/2023        . |
 18. | 18-Sep-23          .           .   9/17/2023        . |
 19. | 19-Sep-23          .           .   9/17/2023        . |
 20. | 20-Sep-23          .           .   9/17/2023        . |
 21. | 21-Sep-23          .           .   9/17/2023        . |
 22. | 22-Sep-23          .           .   9/17/2023        . |
 23. | 23-Sep-23          .           .   9/17/2023        . |
 24. | 24-Sep-23          .           .   9/24/2023        . |
 25. | 25-Sep-23          .           .   9/24/2023        . |
 26. | 26-Sep-23          .           .   9/24/2023        . |
 27. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 28. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 29. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 30. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 31. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 32. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 33. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 34. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 35. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 36. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 37. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 38. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 39. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 40. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 41. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 42. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 43. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 44. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 45. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 46. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 47. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 48. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 49. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 50. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 51. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 52. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 53. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 54. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 55. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 56. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 57. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 58. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 59. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 60. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 61. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 62. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 63. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 64. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 65. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 66. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 67. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 68. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 69. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 70. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 71. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 72. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 73. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 74. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 75. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 76. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 77. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 78. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 79. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 80. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 81. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 82. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 83. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 84. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 85. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 86. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 87. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 88. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 89. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 90. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 91. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 92. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 93. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 94. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 95. | 27-Sep-23          1   02-Oct-23   9/24/2023       71 |
 96. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 97. | 27-Sep-23          1   28-Sep-23   9/24/2023       71 |
 98. | 28-Sep-23          1   02-Oct-23   9/24/2023       29 |
 99. | 28-Sep-23          1   02-Oct-23   9/24/2023       29 |
100. | 28-Sep-23          1   02-Oct-23   9/24/2023       29 |
     +-------------------------------------------------------+

.

        Comment

        • #5
          Thank you very much Andrew Musau! That worked like a charm

          Comment

          Previous Next
          Working...
          X