Generating a running week variable

[Scott Rick]

Hi. The data below spans 1 Jan, 2022 - 31 July, 2024. From the date variable below, I would like to generate a continuous/sequential week variable that groups together seven days (including the dates missing in between). I tried using the week function. However, I don't want the week variable to reset to 1 at 1 Jan, 2023, i.e. 1-7 Jan, 2023 would be week 53.

Any help in doing this would be much appreciated.

clear
input float appt_date
22646
22648
23559
22984
23468
22802
22678
23412
23437
23563
22828
23558
22707
23225
22833
23225
23229
22797
23226
22652
22802
22770
22986
22769
23561
22952
23047
22890
23321
22711
23500
23499
23562
22711
22648
22706
22830
23134
23470
22708
22798
23045
22737
23503
23289
22860
22742
23533
23104
22709
22827
22768
22681
23563

[Nick Cox]

https://www.statalist.org/forums/for...lable-from-ssc may help.

[Clyde Schechter]

sort appt_date
gen week_num = floor((appt_date-appt_date[1])/7) + 1

Added: Crossed with #2

[Scott Rick]

Nick Cox and Clyde Schechter: Thanks a lot!

[Nick Cox]

This worked example shows the correspondence between Clyde Schechter's code and mine.

I use ceil() not floor() and make explicit that 1 Jan 2022 is the start of numbering. Otherwise wanted1 is calculated in the same spirit.

With myweeks each week is indexed by the daily date that starts it. This is helpful for graphics.

clear
input float appt_date
22646
22648
23559
22984
23468
22802
22678
23412
23437
23563
22828
23558
22707
23225
22833
23225
23229
22797
23226
22652
22802
22770
22986
22769
23561
22952
23047
22890
23321
22711
23500
23499
23562
22711
22648
22706
22830
23134
23470
22708
22798
23045
22737
23503
23289
22860
22742
23533
23104
22709
22827
22768
22681
23563
end 

sort appt_date 

gen wanted1 = ceil((appt_date - mdy(12,31,2021))/7)

format appt_date %td 

myweeks appt_date, gen(wanted2) dowstart(`=dow(mdy(1,1,2022))') dailydate 

list, sepby(wanted1)

     +---------------------------------+
     | appt_date   wanted1     wanted2 |
     |---------------------------------|
  1. | 01jan2022         1   01jan2022 |
  2. | 03jan2022         1   01jan2022 |
  3. | 03jan2022         1   01jan2022 |
  4. | 07jan2022         1   01jan2022 |
     |---------------------------------|
  5. | 02feb2022         5   29jan2022 |
     |---------------------------------|
  6. | 05feb2022         6   05feb2022 |
     |---------------------------------|
  7. | 02mar2022         9   26feb2022 |
  8. | 03mar2022         9   26feb2022 |
  9. | 04mar2022         9   26feb2022 |
     |---------------------------------|
 10. | 05mar2022        10   05mar2022 |
 11. | 07mar2022        10   05mar2022 |
 12. | 07mar2022        10   05mar2022 |
     |---------------------------------|
 13. | 02apr2022        14   02apr2022 |
 14. | 07apr2022        14   02apr2022 |
     |---------------------------------|
 15. | 03may2022        18   30apr2022 |
 16. | 04may2022        18   30apr2022 |
 17. | 05may2022        18   30apr2022 |
     |---------------------------------|
 18. | 01jun2022        22   28may2022 |
 19. | 02jun2022        22   28may2022 |
     |---------------------------------|
 20. | 06jun2022        23   04jun2022 |
 21. | 06jun2022        23   04jun2022 |
     |---------------------------------|
 22. | 01jul2022        26   25jun2022 |
     |---------------------------------|
 23. | 02jul2022        27   02jul2022 |
 24. | 04jul2022        27   02jul2022 |
 25. | 07jul2022        27   02jul2022 |
     |---------------------------------|
 26. | 03aug2022        31   30jul2022 |
     |---------------------------------|
 27. | 02sep2022        35   27aug2022 |
     |---------------------------------|
 28. | 03nov2022        44   29oct2022 |
     |---------------------------------|
 29. | 05dec2022        49   03dec2022 |
 30. | 07dec2022        49   03dec2022 |
     |---------------------------------|
 31. | 04feb2023        58   04feb2023 |
 32. | 06feb2023        58   04feb2023 |
     |---------------------------------|
 33. | 04apr2023        66   01apr2023 |
     |---------------------------------|
 34. | 04may2023        70   29apr2023 |
     |---------------------------------|
 35. | 03aug2023        83   29jul2023 |
 36. | 03aug2023        83   29jul2023 |
 37. | 04aug2023        83   29jul2023 |
     |---------------------------------|
 38. | 07aug2023        84   05aug2023 |
     |---------------------------------|
 39. | 06oct2023        92   30sep2023 |
     |---------------------------------|
 40. | 07nov2023        97   04nov2023 |
     |---------------------------------|
 41. | 06feb2024       110   03feb2024 |
     |---------------------------------|
 42. | 02mar2024       114   02mar2024 |
     |---------------------------------|
 43. | 02apr2024       118   30mar2024 |
 44. | 04apr2024       118   30mar2024 |
     |---------------------------------|
 45. | 03may2024       122   27apr2024 |
     |---------------------------------|
 46. | 04may2024       123   04may2024 |
 47. | 07may2024       123   04may2024 |
     |---------------------------------|
 48. | 06jun2024       127   01jun2024 |
     |---------------------------------|
 49. | 01jul2024       131   29jun2024 |
 50. | 02jul2024       131   29jun2024 |
 51. | 04jul2024       131   29jun2024 |
 52. | 05jul2024       131   29jun2024 |
     |---------------------------------|
 53. | 06jul2024       132   06jul2024 |
 54. | 06jul2024       132   06jul2024 |
     +---------------------------------+

scatter wanted2 wanted1 

regress wanted2 wanted1

may help to explain. The relationship is deterministic!