Two ways of using week numbers, but neither give me the correct week number for all the variables

[Pernille Irgens]

I want to convert dates as weeks, but I end up getting the wrong week in some of the options. I need to use weeks as previous analyses and subgroups are based on week numbers. I am using Stata 17 for Windows. Below is a dummy example. As data are confidential, I cannot use -dataex-.

The substantive problem is this: Patients are recruited at "ddate". They receive treatments throughout the study period that are not scheduled at set dates (treat). I've identified the day of the week to make the problem easier to understand. Generating week number for the dday (dweek) and treat (tweek),some of them are incorrect (dweek should be 5, not six; only row 5 is correct in tweek).

clear

*ddate=date recruited
*treat= date of each treatment
input str9(ddate treat)
"2/5/2021" "2/5/2021"
"2/5/2021" "2/7/2021"
"2/5/2021" "2/10/2021"
"2/5/2021" "2/17/2021"
"2/5/2021" "3/23/2021"
end

*making date into day number
gen int dday = date(ddate, "MDY")
gen int treatday = date(treat, "MDY")

*identifying which day of week the day is
*day recruited:
gen byte dname = dow(dday)
forval i=1/7 {
label def dname `=`i'-1' `"`: word `i' of `c(Weekdays)''"', modify
}
label values dname dname

*days of treatment
gen byte tname = dow(treatday)
forval i=1/7 {
label def tname `=`i'-1' `"`: word `i' of `c(Weekdays)''"', modify
}
label values tname tname

*generate week number for recruitment day and treatment day
gen dweek = week(dday)
gen tweek = week(treatday)

list, sep(7)
+------------------------------------------------------------------------------+
| ddate treat dday treatday dname tname dweek tweek |
|------------------------------------------------------------------------------|
1. | 2/5/2021 2/5/2021 22316 22316 Friday Friday 6 6 |
2. | 2/5/2021 2/7/2021 22316 22318 Friday Sunday 6 6 |
3. | 2/5/2021 2/10/2021 22316 22321 Friday Wednesday 6 6 |
4. | 2/5/2021 2/17/2021 22316 22328 Friday Wednesday 6 7 |
5. | 2/5/2021 3/23/2021 22316 22362 Friday Tuesday 6 12 |
+------------------------------------------------------------------------------+

So I tried a different angle. ddate is week 1 for each respective patient. I have found a code in a different question here that could give me the treatment week number when using ddate as week 1, and each consecutive week number having 7 days.

*using ddate as basis for week number - i.e. ddate = first week

gen int alt_tweek = floor(( treatday - dday )/7) + 1

list, sep(7)

     +-----------------------------------------------------------------------------------------+
     |    ddate       treat    dday   treatday    dname       tname   dweek   tweek   alt_tw~k |
     |-----------------------------------------------------------------------------------------|
  1. | 2/5/2021    2/5/2021   22316      22316   Friday      Friday       6       6          1 |
  2. | 2/5/2021    2/7/2021   22316      22318   Friday      Sunday       6       6          1 |
  3. | 2/5/2021   2/10/2021   22316      22321   Friday   Wednesday       6       6          1 |
  4. | 2/5/2021   2/17/2021   22316      22328   Friday   Wednesday       6       7          2 |
  5. | 2/5/2021   3/23/2021   22316      22362   Friday     Tuesday       6      12          7 |
     +-----------------------------------------------------------------------------------------+

However, the problem is still present - I believe the reason this time is that it adds 7 days from ddate, and not from the last day in the week ddate is. I believe that making a new variable where ddate is the closest Monday (questionnaires were filled out on Mondays, so the week needs to start on a Monday). But my skills don't include that, unfortunately. So far I've not found anyone asking for what I need. Personally I consider this a rather complex issue and would greatly appreciate if someone could help me find a solution for my problem, a hint in the right direction, or a link where I myself can find the answer.

[Clyde Schechter]

Weeks are always problematic. There is no standard definition of a week, and the many definitions of week in common usage are often unsuitable for our particular purposes.

You have indicated that you want a week to begin on a date that is a Monday that is "closest" to ddate. Is that exactly what you mean? In other words, if ddate is Friday, Saturday, or Sunday then you want the week to begin on the immediately following Monday, but if ddate is Tuesday, Wednesday, or Thursday, then you want the week to begin on the immediately preceding Monday. If that's what you really want, I can code that, but I don't want to do it without confirmation that this is truly what you want. The fact that you have meandered through several different approaches already makes me worry that you are not yet entirely sure what you want.

[Pernille Irgens]

Hi Clyde
Thank you for the quick response. It's been clear what I want, but I'm not explaining it well, nor have the knowledge to find the correct solution.

If, for instance, if ddate is either of weekdays (Week 1: Mon 1st, Tue 2nd, Wed 3rd, Thur 4th, Fri 5th, Sat 6th, or Sun 7th), then I want the week to begin on that Monday (ie, Mon 1st). Then treatment date on Monday 7th, will be in week 2. I'll attach picture of using ddate as Monday instead of Friday in the above example. dweek and tweek are still incorrect, but alt_tweek is then correct.

Does that make it more clear?

[Nick Cox]

You have attached an image. Please post your example data using dataex code. FAQ Advice #12 explains.

There is quite a Stata literature on handling weeks.

 search week, sj

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

SJ-22-2 dm0107_1  . . .  Erratum: Stata tip 145: Numbering weeks within months
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q2/22   SJ 22(2):465--466                                (no commands)
        errata for tip on numbering weeks within months

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

A negative that underlies some of the problems in #1 is that Stata's definition of a week which underlies its week functionality has nothing to do with any definition of weeks that hinges on weeks starting on a certain day of the week (as all other definitions I've ever seen). Stata's weeks always run from week 1 starting 1 January to week 52 which has 8 or 9 days depending on whether the year is leap. So, the week() function is not part of any solution.

[Clyde Schechter]

clear
input str9(ddate treat)
"2/5/2021" "2/5/2021"
"2/5/2021" "2/7/2021"
"2/5/2021" "2/10/2021"
"2/5/2021" "2/17/2021"
"2/5/2021" "3/23/2021"
end

foreach v of varlist ddate treat {
    gen _`v' = daily(`v', "MDY"), after(`v')
    assert missing(_`v') == missing(`v')
    format _`v' %td
    drop `v'
    rename _`v' `v'
}

gen d_day_of_week = dow(ddate)
replace d_day_of_week = 7 if d_day_of_week == 0 // MAKE SUNDAY THE LAST DAY
gen index_monday = ddate - (d_day_of_week - 1)
format index_monday %td
gen treatment_week = floor((treat - index_monday)/7) + 1

[Pernille Irgens]

First, I apologise for having attached a picture rather than dataex. Unfortunately, I could only open STATA in our secure server on Friday due to IT trouble at my institution, meaning I could only use picture format here.

Thank you, Nick, for the guidance on where to find the writings on weeks - I've read them all, but good to know I haven't missed any. The reason I looked for an alternative method is exactly because I've understood that using weeks from dates in STATA has it's weaknesses. I'll keep that in mind when designing future studies!

Thank you, Clyde, the code you supplied was what I was looking for, and also taught me a lot - your assistance is very much appreciated!

Pernille