working with dates to get weekly / monthly/ fortnightly rates

[Sapphire AT]

Dear Statalists,
I have a dataset (long format) which contains id_doctor, id_patients, date_clinic, date_intervention, testing_yn.
For each id_doctor, there are different patients.
For each id_patients, they might have 1 or more clinic visist dates (date_clinic)

Intervention was given on doctors. Each doctor serves as his/her own control, i.e., they are in intervention phase after the date_intervention (different intervention dates for the doctors) and they were in control phase before that. Study started on 1st Jan 2010 and study ended on 31st May 2011.

testing_yn means 0 for no testing and 1 for testing.

My question is how I am able to calculate the weekly and fortnightly rates of testing for each doctor.

P.S. The dates are not random dates between 1st Jan 2010 and 31st May 2011 (They are NOT consecutive in my dataset).

Many thanks,
Sapphire

[Nick Cox]

This is a little hard to follow without a data example.

What is your definition of week? Stata's weeks are idiosyncratic such that week 1 of a year always starts on 1 January and week 52 is always 8 or 9 days long.

The simplest criterion is to follow Stata's dow() function and have weeks starting on Sundays. in which case you can identify each week by the day that starts them

 clear

. set obs 14
Number of observations (_N) was 0, now 14.

. gen date = mdy(12, 31, 2020) + _n

. gen wdate = date - dow(date)

. format *date %td

. list, sepby(wdate)

     +-----------------------+
     |      date       wdate |
     |-----------------------|
  1. | 01jan2021   27dec2020 |
  2. | 02jan2021   27dec2020 |
     |-----------------------|
  3. | 03jan2021   03jan2021 |
  4. | 04jan2021   03jan2021 |
  5. | 05jan2021   03jan2021 |
  6. | 06jan2021   03jan2021 |
  7. | 07jan2021   03jan2021 |
  8. | 08jan2021   03jan2021 |
  9. | 09jan2021   03jan2021 |
     |-----------------------|
 10. | 10jan2021   10jan2021 |
 11. | 11jan2021   10jan2021 |
 12. | 12jan2021   10jan2021 |
 13. | 13jan2021   10jan2021 |
 14. | 14jan2021   10jan2021 |
     +-----------------------+

Then your calculations for weeks can use egen functions and by() or by: with identifier and weekly date.

There are many possible variations on that criterion.

For more discussion see for example

. search week, sj

Search of official help files, FAQs, Examples, and Stata Journals

SJ-22-1 dm0107  . . . . . . . . . Stata tip 145: Numbering weeks within months
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q1/22   SJ 22(1):224--230                                (no commands)
        tip on numbering weeks within months

SJ-19-3 dm0100  . . . . . . . . . .  Speaking Stata: The last day of the month
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q3/19   SJ 19(3):719--728                                (no commands)
        discusses three related problems about getting the last day
        of the month in a new variable

SJ-12-4 dm0065_1  . . . . . Stata tip 111: More on working with weeks, erratum
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q4/12   SJ 12(4):765                                     (no commands)
        lists previously omitted key reference

SJ-12-3 dm0065  . . . . . . . . . .  Stata tip 111: More on working with weeks
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q3/12   SJ 12(3):565--569                                (no commands)
        discusses how to convert data presented in yearly and weekly
        form to daily dates and how to aggregate such data to months
        or longer intervals

SJ-10-4 dm0052  . . . . . . . . . . . . . . . . Stata tip 68: Week assumptions
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q4/10   SJ 10(4):682--685                                (no commands)
        tip on Stata's solution for weeks and on how to set up
        your own alternatives given different definitions of the
        week

Months could be conventional calendar months. Otherwise you could aggregate weeks in twos or fours.

[Sapphire AT]

Thank you so much Nick. So far, I defined week as calendar week. I used the Stata's week function with this code:
gen visit_wk = week(visit_date).

Let me work out on my dataset with Stata's dow(). I'm also curious how that would be different.