a variable that indicates whether it is the first week of the month, second week, etc...

[Francisco Nobre]

Dear all,

I have a date variable in my dataset in the following format:

27feb2015
20feb2015
13feb2015

and so on. I wanted to create another variable that indicates whether the different dates belong to the first week of month, second week and so on. Is there a way to do this?

Many thanks

[Andrew Musau]

What is the first week of the month? Is it the first seven days of the month, or the week that started in the previous month and extends to the current, or the first complete week of the month, or something else? Do your weeks start on Mondays or on Sundays?

[Nick Cox]

Andrew Musau is bang on as usual. But it is possible to show some technique. Here is a sandbox example with daily dates for the first three months of 2021.

. clear

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

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

. format ddate %td

. list if inlist(_n, 1, _N)

     +-----------+
     |     ddate |
     |-----------|
  1. | 01jan2021 |
 90. | 31mar2021 |
     +-----------+

. gen mdate = mofd(ddate)

. format mdate %tm

. bysort mdate (ddate) : gen wofm = sum(dow(ddate) == 0)

. 
. tab mdate wofm

           |                               wofm
     mdate |         0          1          2          3          4          5 |     Total
-----------+------------------------------------------------------------------+----------
    2021m1 |         2          7          7          7          7          1 |        31 
    2021m2 |         6          7          7          7          1          0 |        28 
    2021m3 |         6          7          7          7          4          0 |        31 
-----------+------------------------------------------------------------------+----------
     Total |        14         21         21         21         12          1 |        90

The principles should seem simple. How to code them in Stata could easily be more of a challenge.

Here the presumption is that weeks start on Sundays for which dow() returns 0. So, we go through the daily dates in each month and count 0s. Easily, indeed usually, a month could start on any other day of the week, in which cases we start with incomplete weeks coded 0 (meaning, no Sunday observed yet).

Now twists on the idea should seem easy. First, starting on Monday or any other day of the week just means testing for a different result from dow() Second. wanting the numbering of weeks to start at 1 just means adding 1 in calculating wofm.

Perhaps none of this applies at all and you want weeks defined otherwise, that is days 1 to 7, 8 to 14, and so on define weeks 1 to 5 (or 4 for non-leap February) and there is usually an incomplete week 5.

That is easy enough -- given rummaging around in the help for functions! --

. by mdate : gen WOFM = ceil(day(ddate) / 7)

. tab mdate WOFM

           |                          WOFM
     mdate |         1          2          3          4          5 |     Total
-----------+-------------------------------------------------------+----------
    2021m1 |         7          7          7          7          3 |        31 
    2021m2 |         7          7          7          7          0 |        28 
    2021m3 |         7          7          7          7          3 |        31 
-----------+-------------------------------------------------------+----------
     Total |        21         21         21         21          6 |        90

Given the extraordinary range of calendars and calendar practices across countries and subject areas, there could be yet other definitions, but I came in agreeing with Andrew: you really have to spell out your definition.

[Nick Cox]

Note on last code block in previous:

 
 . by mdate :

isn't needed, but it does no harm.

[Nick Cox]

Here is a detail that could bite in the first example in which weeks start on Sundays.

As shown, the counter always started at 0 because in all the three months in the example, the first day of the month was not a Sunday. Suppose you wanted to start the counter at 1. For the data example, just adding 1 will work.

However, it is clearly possible that a month could start on a Sunday (August 2021 was an example). Hence more careful code would re-start the counter at 1 if and only if the counter starts at 0.

bysort mdate (ddate) : replace wofm = wofm + 1 if wofm[1] == 0

As said, the same logic applies to any other rule about the day of the week that is deemed to be the first in its week.