Announcement

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

    Week number based on surveillance calendar (Datetime functions)

    I am generating a date calendar file, and have Stata date variables for date (daily), and weekstartdate (date beginning on Sundays). I would like to add a variable for FluWatch week, that aligns with this surveillance calendar
    (https://www.canada.ca/en/public-heal...-calendar.html).

    The range of dates includes Jan 1 2013 to Dec 31, 2027, so I would like to generate some logic to infill the corresponding FluWatch week for all of these dates. The FluWatch week starts at one, if the week beginning on Sunday includes Jan 1st for that year.

    What datetime functions could be used here to start each year with FluWeek=1 where the first week contains Jan 1st, and count the number of weeks from there? Here is what I've tried so far, but the weeknum count does not make any replacements

    Code:
       
*** 1. H Generate FluWatchWeekNumber Variable
************************************
gen FluWatchWeekNumber=0
gen yearstart=mdy(1, 1, year)
format yearstart %tdCCYY-NN-DD
replace FluWatchWeekNumber=1 if datediff(yearstart, date, "day")<7
gen weeknum=0
replace weeknum=datediff(yearstart, date, "week")

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input float(date weekstartdate year monthyear)
19359 19357 2013 19359
19360 19357 2013 19359
19361 19357 2013 19359
19362 19357 2013 19359
19363 19357 2013 19359
19364 19364 2013 19359
19365 19364 2013 19359
19366 19364 2013 19359
19367 19364 2013 19359
19368 19364 2013 19359
19369 19364 2013 19359
19370 19364 2013 19359
19371 19371 2013 19359
19372 19371 2013 19359
19373 19371 2013 19359
19374 19371 2013 19359
19375 19371 2013 19359
19376 19371 2013 19359
19377 19371 2013 19359
19378 19378 2013 19359
19379 19378 2013 19359
19380 19378 2013 19359
19381 19378 2013 19359
19382 19378 2013 19359
19383 19378 2013 19359
19384 19378 2013 19359
19385 19385 2013 19359
19386 19385 2013 19359
19387 19385 2013 19359
19388 19385 2013 19359
19389 19385 2013 19359
19390 19385 2013 19390
19391 19385 2013 19390
19392 19392 2013 19390
19393 19392 2013 19390
19394 19392 2013 19390
19395 19392 2013 19390
19396 19392 2013 19390
19397 19392 2013 19390
19398 19392 2013 19390
19399 19399 2013 19390
19400 19399 2013 19390
19401 19399 2013 19390
19402 19399 2013 19390
19403 19399 2013 19390
19404 19399 2013 19390
19405 19399 2013 19390
19406 19406 2013 19390
19407 19406 2013 19390
19408 19406 2013 19390
19409 19406 2013 19390
19410 19406 2013 19390
19411 19406 2013 19390
19412 19406 2013 19390
19413 19413 2013 19390
19414 19413 2013 19390
19415 19413 2013 19390
19416 19413 2013 19390
19417 19413 2013 19390
19418 19413 2013 19418
19419 19413 2013 19418
19420 19420 2013 19418
19421 19420 2013 19418
19422 19420 2013 19418
19423 19420 2013 19418
19424 19420 2013 19418
19425 19420 2013 19418
19426 19420 2013 19418
19427 19427 2013 19418
19428 19427 2013 19418
19429 19427 2013 19418
19430 19427 2013 19418
19431 19427 2013 19418
19432 19427 2013 19418
19433 19427 2013 19418
19434 19434 2013 19418
19435 19434 2013 19418
19436 19434 2013 19418
19437 19434 2013 19418
19438 19434 2013 19418
19439 19434 2013 19418
19440 19434 2013 19418
19441 19441 2013 19418
19442 19441 2013 19418
19443 19441 2013 19418
19444 19441 2013 19418
19445 19441 2013 19418
19446 19441 2013 19418
19447 19441 2013 19418
19448 19448 2013 19418
19449 19448 2013 19449
19450 19448 2013 19449
19451 19448 2013 19449
19452 19448 2013 19449
19453 19448 2013 19449
19454 19448 2013 19449
19455 19455 2013 19449
19456 19455 2013 19449
19457 19455 2013 19449
19458 19455 2013 19449
end
format %tdCCYY-NN-DD date
format %tdCCYY-NN-DD weekstartdate
format %tdCCYY-NN-DD monthyear
    Last edited by Shannon Meadows; 18 Feb 2025, 15:07.
    Tags: None
  • #2
    I'm not sure I fully understand what you want to do. But if I have it right, all you need is:
    Code:
    by year (date), sort: gen flu_watch_week = sum(weekstartdate != weekstartdate[_n-1])
    No datetime functions needed, nor anything else fancy. You actually already did the hard part by creating the weekstartdate variable.

    As an aside, I would change the name of the variable monthyear, or else change the variable itself. The name is misleading because although you call it month year, it's actually the date of the first of each month. It's always better to use names that accurately describe the contents of a variable. With the current situation, you could easily be tempted to apply functions that take monthly date variables to monthyear--and that would give you garbage. So either I would rename it something like date_first_of_month_year, or keep the name month year and actually make it a variable showing the month and year with -replace monthyear = mofd(monthyear)- and -format monthyear %tmCCYY-NN-.
    • 1 like

    Comment

    • #3
      Clyde Schechter's code will work as long as the year variable represents the year at the end of the current week, but I don't know if that is the case here. If year is the year corresponding to either the current date or the start of the week, some days in the week straddling the change of year will get misclassified. Note that OP says:

      The FluWatch week starts at one, if the week beginning on Sunday includes Jan 1st for that year.
      which means that it is key to check the year corresponding to the end of the week.

      To illustrate:

      Code:
      clear
set obs 400
gen date = 19359 + _n - 1
gen weekstartdate = date - dow(date)
gen weekenddate = weekstartdate + 6
format %tdDay_CCYY-NN-DD date weekstartdate weekenddate
gen year = yofd(date)
by year (date), sort: gen flu_watch_week = sum(weekstartdate != weekstartdate[_n-1])

gen year_endweek = yofd(weekenddate)
by year_endweek (date), sort: gen wanted = sum(weekstartdate != weekstartdate[_n-1])
      and so, to look at the end of 2013 and start of 2014:

      Code:
      . list in 360/369, abbrev(15) sep(0) noobs

  +--------------------------------------------------------------------------------------------------+
  |           date    weekstartdate      weekenddate   year   flu_watch_week   year_endweek   wanted |
  |--------------------------------------------------------------------------------------------------|
  | Thu 2013-12-26   Sun 2013-12-22   Sat 2013-12-28   2013               52           2013       52 |
  | Fri 2013-12-27   Sun 2013-12-22   Sat 2013-12-28   2013               52           2013       52 |
  | Sat 2013-12-28   Sun 2013-12-22   Sat 2013-12-28   2013               52           2013       52 |
  | Sun 2013-12-29   Sun 2013-12-29   Sat 2014-01-04   2013               53           2014        1 |
  | Mon 2013-12-30   Sun 2013-12-29   Sat 2014-01-04   2013               53           2014        1 |
  | Tue 2013-12-31   Sun 2013-12-29   Sat 2014-01-04   2013               53           2014        1 |
  | Wed 2014-01-01   Sun 2013-12-29   Sat 2014-01-04   2014                1           2014        1 |
  | Thu 2014-01-02   Sun 2013-12-29   Sat 2014-01-04   2014                1           2014        1 |
  | Fri 2014-01-03   Sun 2013-12-29   Sat 2014-01-04   2014                1           2014        1 |
  | Sat 2014-01-04   Sun 2013-12-29   Sat 2014-01-04   2014                1           2014        1 |
  +--------------------------------------------------------------------------------------------------+
      Last edited by Hemanshu Kumar; 19 Feb 2025, 03:58.
      • 2 likes

      Comment

      • #4
        Thank you Hemanshu Kumar and Clyde Schechter for your replies. The yearend_week method worked to correctly assign the weeks that straddle between two years.

        I made an error in my understanding of the start of week 1. If Jan 1st of each year occurred on either a Thursday, Friday, or Saturday then week 1 began on the following Sunday. Do you have any ideas on how to correct the code to accommodate this edit?

        Comment

        • #5
          So this is a bit confusing. If Jan 1st occurs on a Thursday, Friday, or Saturday, what happens to the days between Jan 1st and the subsequent Sunday? In the following code, they are designated as flu watch week 0. You might want to replace that with missing value. Or perhaps they are in "week 53" of the preceding year? Also, since a year is 365 or more days, and 365 leaves a remainder of 1 when divided by 7, Dec 31 (and perhaps Dec 30 in a leap year) will be "week 53" if it is a year where Jan 1st does not fall on a Thursday, Friday, or Saturday.

          Anyway:
          Code:
          gen year_of_date = year(date)
gen weekday_jan_1 = dow(mdy(1, 1, year))
gen start_flu_watch_week_1 = cond(weekday_jan_1 < 4, mdy(1,1,year), ///
    mdy(1,1,year) + 7 - weekday_jan_1)
format start_flu_watch_week_1 %tdCCYY-NN-DD

gen int flu_watch_week = floor((date - start_flu_watch_week_1)/7) + 1

          Comment

          • #6
            Consider:

            Code:
            clear
set obs 1000
gen date = date("2013-01-01", "YMD") + _n - 1
gen weekstartdate = date - dow(date)
gen weekenddate = weekstartdate + 6
format %tdDay_CCYY-NN-DD date weekstartdate weekenddate
gen year_weekend = yofd(weekenddate)
gen year = year_weekend
replace year = year - 1 if yofd(weekenddate) != yofd(weekstartdate) & inrange(day(weekenddate), 1, 3)
by year (date), sort: gen flu_watch_week = sum(weekstartdate != weekstartdate[_n-1])
            where the inrange expression exploits the fact that if Jan 1st is Thu/Fri/Sat, then the Saturday of that week must be Jan 3/2/1, respectively.

            This produces:

            Code:
            . list if yofd(weekenddate) != yofd(weekstartdate), abbrev(15) sepby(year_weekend) noobs

  +-----------------------------------------------------------------------------------------+
  |           date    weekstartdate      weekenddate   year_weekend   year   flu_watch_week |
  |-----------------------------------------------------------------------------------------|
  | Tue 2013-01-01   Sun 2012-12-30   Sat 2013-01-05           2013   2013                1 |
  | Wed 2013-01-02   Sun 2012-12-30   Sat 2013-01-05           2013   2013                1 |
  | Thu 2013-01-03   Sun 2012-12-30   Sat 2013-01-05           2013   2013                1 |
  | Fri 2013-01-04   Sun 2012-12-30   Sat 2013-01-05           2013   2013                1 |
  | Sat 2013-01-05   Sun 2012-12-30   Sat 2013-01-05           2013   2013                1 |
  |-----------------------------------------------------------------------------------------|
  | Sun 2013-12-29   Sun 2013-12-29   Sat 2014-01-04           2014   2014                1 |
  | Mon 2013-12-30   Sun 2013-12-29   Sat 2014-01-04           2014   2014                1 |
  | Tue 2013-12-31   Sun 2013-12-29   Sat 2014-01-04           2014   2014                1 |
  | Wed 2014-01-01   Sun 2013-12-29   Sat 2014-01-04           2014   2014                1 |
  | Thu 2014-01-02   Sun 2013-12-29   Sat 2014-01-04           2014   2014                1 |
  | Fri 2014-01-03   Sun 2013-12-29   Sat 2014-01-04           2014   2014                1 |
  | Sat 2014-01-04   Sun 2013-12-29   Sat 2014-01-04           2014   2014                1 |
  |-----------------------------------------------------------------------------------------|
  | Sun 2014-12-28   Sun 2014-12-28   Sat 2015-01-03           2015   2014               53 |
  | Mon 2014-12-29   Sun 2014-12-28   Sat 2015-01-03           2015   2014               53 |
  | Tue 2014-12-30   Sun 2014-12-28   Sat 2015-01-03           2015   2014               53 |
  | Wed 2014-12-31   Sun 2014-12-28   Sat 2015-01-03           2015   2014               53 |
  | Thu 2015-01-01   Sun 2014-12-28   Sat 2015-01-03           2015   2014               53 |
  | Fri 2015-01-02   Sun 2014-12-28   Sat 2015-01-03           2015   2014               53 |
  | Sat 2015-01-03   Sun 2014-12-28   Sat 2015-01-03           2015   2014               53 |
  +-----------------------------------------------------------------------------------------+
            Last edited by Hemanshu Kumar; 19 Feb 2025, 13:31.

            Comment

            • #7
              Thank you again Clyde Schechter and Hemanshu Kumar. If Jan 1st occurs on a Thursday, Friday, or Saturday, then Jan1st should be assigned week 53 of the previous year, with week 1 beginning on the subsequent Sunday. Flu_watch numbers should change every Sunday, and be the same number for the 7 days that week. It's a bit confusing, but am needing to match this external pattern.

              To add some clarification, I've manually coded some examples under the variable 'wanted', as the intended pattern for counting Flu_watch_numbers. The Flu_watch_numbers below are using Clyde's suggested code above. Let me know if additional clarification is needed.

              Code:
               list date weekstartdate year_weekend FluWatchWeekNumber wanted if wanted!=.

      +----------------------------------------------------------------+
      |           date    weekstartdate   year_w~d   FluWat~r   wanted |
      |----------------------------------------------------------------|
 363. | Sun 2013-12-29   Sun 2013-12-29       2014         53       53 |
 364. | Mon 2013-12-30   Sun 2013-12-29       2014         53       53 |
 365. | Tue 2013-12-31   Sun 2013-12-29       2014         53       53 |
 366. | Wed 2014-01-01   Sun 2013-12-29       2014          1       53 |
 367. | Thu 2014-01-02   Sun 2013-12-29       2014          1       53 |
      |----------------------------------------------------------------|
 368. | Fri 2014-01-03   Sun 2013-12-29       2014          1       53 |
 369. | Sat 2014-01-04   Sun 2013-12-29       2014          1       53 |
 370. | Sun 2014-01-05   Sun 2014-01-05       2014          2        1 |
 371. | Mon 2014-01-06   Sun 2014-01-05       2014          2        1 |
 372. | Tue 2014-01-07   Sun 2014-01-05       2014          2        1 |
      |----------------------------------------------------------------|
 373. | Wed 2014-01-08   Sun 2014-01-05       2014          2        1 |
 374. | Thu 2014-01-09   Sun 2014-01-05       2014          2        1 |
 375. | Fri 2014-01-10   Sun 2014-01-05       2014          2        1 |
 376. | Sat 2014-01-11   Sun 2014-01-05       2014          2        1 |
 727. | Sun 2014-12-28   Sun 2014-12-28       2014         53       53 |
      |----------------------------------------------------------------|
 728. | Mon 2014-12-29   Sun 2014-12-28       2014         53       53 |
 729. | Tue 2014-12-30   Sun 2014-12-28       2014         53       53 |
 730. | Wed 2014-12-31   Sun 2014-12-28       2014         53       53 |
 731. | Thu 2015-01-01   Sun 2014-12-28       2014          1       53 |
 732. | Fri 2015-01-02   Sun 2014-12-28       2014          1       53 |
      |----------------------------------------------------------------|
 733. | Sat 2015-01-03   Sun 2014-12-28       2014          1       53 |
 734. | Sun 2015-01-04   Sun 2015-01-04       2015          2        1 |
 735. | Mon 2015-01-05   Sun 2015-01-04       2015          2        1 |
 736. | Tue 2015-01-06   Sun 2015-01-04       2015          2        1 |
 737. | Wed 2015-01-07   Sun 2015-01-04       2015          2        1 |
      |----------------------------------------------------------------|
 738. | Thu 2015-01-08   Sun 2015-01-04       2015          2        1 |
 739. | Fri 2015-01-09   Sun 2015-01-04       2015          2        1 |
 740. | Sat 2015-01-10   Sun 2015-01-04       2015          2        1 |
1091. | Sun 2015-12-27   Sun 2015-12-27       2015         53       53 |
1092. | Mon 2015-12-28   Sun 2015-12-27       2015         53       53 |
      |----------------------------------------------------------------|
1093. | Tue 2015-12-29   Sun 2015-12-27       2015         53       53 |
1094. | Wed 2015-12-30   Sun 2015-12-27       2015         53       53 |
1095. | Thu 2015-12-31   Sun 2015-12-27       2015         53       53 |
1096. | Fri 2016-01-01   Sun 2015-12-27       2015          1       53 |
1097. | Sat 2016-01-02   Sun 2015-12-27       2015          1       53 |
      |----------------------------------------------------------------|
1098. | Sun 2016-01-03   Sun 2016-01-03       2016          2        1 |
1099. | Mon 2016-01-04   Sun 2016-01-03       2016          2        1 |
1100. | Tue 2016-01-05   Sun 2016-01-03       2016          2        1 |
1101. | Wed 2016-01-06   Sun 2016-01-03       2016          2        1 |
1102. | Thu 2016-01-07   Sun 2016-01-03       2016          2        1 |
      |----------------------------------------------------------------|
1103. | Fri 2016-01-08   Sun 2016-01-03       2016          2        1 |
1104. | Sat 2016-01-09   Sun 2016-01-03       2016          2        1 |
1819. | Sun 2017-12-24   Sun 2017-12-24       2017         52       52 |
1820. | Mon 2017-12-25   Sun 2017-12-24       2017         52       52 |
1821. | Tue 2017-12-26   Sun 2017-12-24       2017         52       52 |
      |----------------------------------------------------------------|
1822. | Wed 2017-12-27   Sun 2017-12-24       2017         52       52 |
1823. | Thu 2017-12-28   Sun 2017-12-24       2017         52       52 |
1824. | Fri 2017-12-29   Sun 2017-12-24       2017         52       52 |
1825. | Sat 2017-12-30   Sun 2017-12-24       2017         52       52 |
1826. | Sun 2017-12-31   Sun 2017-12-31       2018         53        1 |
      |----------------------------------------------------------------|
1827. | Mon 2018-01-01   Sun 2017-12-31       2018          1        1 |
1828. | Tue 2018-01-02   Sun 2017-12-31       2018          1        1 |
1829. | Wed 2018-01-03   Sun 2017-12-31       2018          1        1 |
1830. | Thu 2018-01-04   Sun 2017-12-31       2018          1        1 |
1831. | Fri 2018-01-05   Sun 2017-12-31       2018          1        1 |
      |----------------------------------------------------------------|
1832. | Sat 2018-01-06   Sun 2017-12-31       2018          1        1 |
2183. | Sun 2018-12-23   Sun 2018-12-23       2018         52       52 |
2184. | Mon 2018-12-24   Sun 2018-12-23       2018         52       52 |
2185. | Tue 2018-12-25   Sun 2018-12-23       2018         52       52 |
2186. | Wed 2018-12-26   Sun 2018-12-23       2018         52       52 |
      |----------------------------------------------------------------|
2187. | Thu 2018-12-27   Sun 2018-12-23       2018         52       52 |
2188. | Fri 2018-12-28   Sun 2018-12-23       2018         52       52 |
2189. | Sat 2018-12-29   Sun 2018-12-23       2018         52       52 |
2190. | Sun 2018-12-30   Sun 2018-12-30       2019         53        1 |
2191. | Mon 2018-12-31   Sun 2018-12-30       2019         53        1 |
      |----------------------------------------------------------------|
2192. | Tue 2019-01-01   Sun 2018-12-30       2019          1        1 |
2193. | Wed 2019-01-02   Sun 2018-12-30       2019          1        1 |
2194. | Thu 2019-01-03   Sun 2018-12-30       2019          1        1 |
2195. | Fri 2019-01-04   Sun 2018-12-30       2019          1        1 |
2196. | Sat 2019-01-05   Sun 2018-12-30       2019          1        1 |
      +----------------------------------------------------------------+
              Hemanshu, thank you as well for your suggested code. When I ran this subscript above, the Flu_watch numbers did not start from each Sunday. I suspect that this may be due to the calendar beginning on a Tuesday, Jan 1 2013.

              Comment

              • #8
                Shannon Meadows my made-up dataset starts with Tuesday, Jan 1 2013, to match yours, and it codes the weeks starting each Sunday. Could you tell me exactly what you did, so we can troubleshoot?

                Also, I think there is a mistake in your manually coded wanted variable -- e.g. since 1/1/2014 is a Wednesday, that week should be coded 1, and not 53.

                I do believe my code works the way you want, but if you can double-check and let me know if you're still facing trouble, we can try and fix it with the help of a data example and the exact code and output you get.

                Comment

                • #9
                  The code I proposed in #5 can be fixed by adding a few lines to the end. I believe this now produces the desired results, as clarified in #7.

                  Code:
                  //    CREATE A DEMONSTRATION DATA SET
clear*
set obs `=td(31dec2025)-td(31dec2012)'
gen date = td(31dec2012) + _n
format date %tdDay_CCYY-NN-DD

//    CALCULATE FLU WATCH WEEKS
gen year_of_date = year(date)
gen weekday_jan_1 = dow(mdy(1, 1, year))
gen start_flu_watch_week_1 = cond(weekday_jan_1 < 4, mdy(1,1,year), ///
    mdy(1,1,year) + 7 - weekday_jan_1)
format start_flu_watch_week_1 %tdCCYY-NN-DD

gen int flu_watch_week = floor((date - start_flu_watch_week_1)/7) + 1
clonevar flu_watch_year = year_of_date
replace flu_watch_year = flu_watch_year - 1 if flu_watch_week == 0
replace flu_watch_week = 53 if flu_watch_week == 0
                  The combination of flu_watch_year and flu_watch_week is now, I believe, as desired.

                  Comment

                  Previous Next
                  Working...
                  X