Creating calendar week

[Bendik Sogn]

Hi,

I want to create a week variable according to the weeks in the year 1 through 52. Our dataset is offline, and we cannot install any packages as dataex.

Our data runs over two years, and we want to group observations in weeks and then compare the differences between the weeks. Currently we have made two date variables, "DMY hms" and "DMY". We now want a variable with "YW" or something similar. The point is to get calendar weeks in our data.

Additionally, we want the weeks to start on a specific date (5th of January 2015), and not the first day of the year.

Appreciate some input on this, thanks!

[Nick Cox]

Not being able to install dataex from SSC shouldn't stop you from giving a realistic example of your data using input code.

You want your own definition of weeks, really. That's fine. Some good news is that this is discussed in literature that should be accessible:

. search week, sj

Search of official help files, FAQs, Examples, SJs, and STBs

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

If you run search week, sj yourself you should get clickable links to pdf versions of those papers. No one need read these papers if they have your problem, because the code below should seem self-explanatory in any case.

Some bad news is that you've not really explained all your rules, so if I've guessed wrong you have more explaining to do.

I see that 5 January 2015 was a Monday. So I guess that one rule is that

* Weeks start on Mondays.

If so, we just count Mondays in each year and number them (and the corresponding weeks) 1 upwards.

I created a sandbox to do this using the fact that dow() fed a daily date returns 1 for Mondays.

clear
set obs 731
gen daily = mdy(12,31,2014) + _n
format daily %td

gen year = year(daily)
bysort year : gen week = sum(dow(daily) == 1)

What's missing is your other rule:


* A rule that says what to do with any incomplete weeks (6 days or fewer) at the beginning and end of each other.

A side-effect of the code just given is that any days before the first Monday in a year are labelled week 0. Many prefer to see such days regarded as the completion of the last week of the previous year. We can do that and see where we are.

sort daily
clonevar week2 = week
replace week2 = week2[_n-1] if week2 == 0


. l if (daily - mdy(1,1,year))   <= 6 | (mdy(12,31,year) - daily)  <= 6, sepby(year)

     +---------------------------------+
     |     daily   year   week   week2 |
     |---------------------------------|
  1. | 01jan2015   2015      0       . |
  2. | 02jan2015   2015      0       . |
  3. | 03jan2015   2015      0       . |
  4. | 04jan2015   2015      0       . |
  5. | 05jan2015   2015      1       1 |
  6. | 06jan2015   2015      1       1 |
  7. | 07jan2015   2015      1       1 |
359. | 25dec2015   2015     51      51 |
360. | 26dec2015   2015     51      51 |
361. | 27dec2015   2015     51      51 |
362. | 28dec2015   2015     52      52 |
363. | 29dec2015   2015     52      52 |
364. | 30dec2015   2015     52      52 |
365. | 31dec2015   2015     52      52 |
     |---------------------------------|
366. | 01jan2016   2016      0      52 |
367. | 02jan2016   2016      0      52 |
368. | 03jan2016   2016      0      52 |
369. | 04jan2016   2016      1       1 |
370. | 05jan2016   2016      1       1 |
371. | 06jan2016   2016      1       1 |
372. | 07jan2016   2016      1       1 |
725. | 25dec2016   2016     51      51 |
726. | 26dec2016   2016     52      52 |
727. | 27dec2016   2016     52      52 |
728. | 28dec2016   2016     52      52 |
729. | 29dec2016   2016     52      52 |
730. | 30dec2016   2016     52      52 |
731. | 31dec2016   2016     52      52 |
     +---------------------------------+

The missings at the start of the data are just side-effects of the sandbox data having to start somewhere.

The bottom line here is that Stata's weekly dates are almost never what anyone wants. I've made a strong hint to the company that Stata should implement ISO weeks some time. At least that's one standard.