Converting date or week variable to biweekly variable

[Vincent KH Lee]

Hi,

I am trying to convert date variable in "YMD" format, to biweekly variable.

* Example generated by -dataex-. For more info, type help dataex
clear
input float date
21576
21592
21622
21656
21663
21663
21671
21698
21698
21700
21723
21733
21790
21828
21941
21941
21942
21942
21947
21947
21947
21948
21949
21954
21956
21959
21963
21972
22020
22021
22022
22025
22028
22031
22034
22046
22048
22050
22056
22071
22092
22099
22111
22122
22125
22125
22125
22125
22142
22142
22142
22153
22153
22153
22160
22181
22218
22254
22254
22254
22255
22275
21941
21941
21941
21941
22224
22234
22238
22252
22252
22254
22265
22265
22276
22276
22279
22279
22279
21680
21680
21680
21680
21680
21687
21689
21700
21715
21772
21803
21807
21813
21820
21825
21837
21842
21960
21966
22158
22158
end
format %td date

I want the biweekly variable to account for Monday-to-Sunday as one week, and also account for a month like February, where there are in some cases less than 4-weeks. (So for instance, if the last week of February ends on Wednesday of Week3, Thursday (the very next day) of March 1st, should be classified as another week than Week 3).

Ultimately, I want to aggregate the dataset on biweekly bases, so I am planning on using the "collpase" command with biweekly time variable.

So far I have tried,

gen week = wofd(date)
format week %tw
br week

gen biweek = ceil((week - 0.5)/2)

replace biweek = biweek + mod(week-1,2)*0.5 if mod(week,2) == 0

preserve 
collapse (sum) sale_amt, by(biweek) 
restore

This makes sense in my head, but when I try to verify whether this works or not, the "biweek" variable I created is not in a human-readable, which prevents me from physically inspecting the issue with special month like February.

#1. Is there a better way to convert daily or week variable (generated with wofd()) to a biweekly format?

#2. Is there a way to convert the biweek variable to a human-readable format? the best I can think of is creating a new variable using "string()"....



Thanks in advance!

[Vincent KH Lee]

To clarify,

ideally, I want the biweekly variable to look somewhat like this:

"Week1-2" "Week3-4"... and so on, so there should be 52(per year)/2 = 26 biweekly periods...

[Nick Cox]

Your criteria won't work together. You can have 26 periods in every year if you use Stata's definition of weeks as a start, as week 1 always starts on 1 January, etc., all the way to week 52 which always extends to 31 December and lasts 8 or 9 days.

But if you insist on starting on Mondays and ending on Sundays, and nesting in months, that's just awkward to implement easily.

Several papers on the quirks of working with weeks. Search the Stata Journal archive.
Erratum: Stata tip 145: Numbering weeks within months N. J. Cox. 2022. Stata Journal Volume 22 Number 2. Stata tip 145: Numbering weeks within months N. J. Cox. 2022. Stata Journal Volume 22 Number 1. Speaking Stata: The last day of the month N. J. Cox. 2019. Stata Journal Volume 19 Number 3. Stata tip 111: More on working with weeks, erratum N. J. Cox. 2012. Stata Journal Volume 12 Number 4. Stata tip 111: More on working with weeks N. J. Cox. 2012. Stata Journal Volume 12 Number 3. Stata tip 68: Week assumptions N. J. Cox. 2010. Stata Journal Volume 10 Number 4.

[Andrew Musau]

If you are comfortable with Stata's definition of weeks (calendar weeks), then

gen wanted= ceil(week(date)/2)

Here, 1 and 2 are mapped to 1, ... , 51 and 52 to 26. See

help datetime

[Nick Cox]

search week, sj

in an up-to-date Stata to get some discussions and suggestions. Your criteria are hard, indeed impossible, to implement together.

[Vincent KH Lee]

Andrew Musau, thank you for your suggestion it worked out perfectly, and I added a label to it so it is a bit more easy for me to read.

gen wanted_label = ""
replace wanted_label = "Biweek" + string(wanted*2-1) + "-" + string(wanted*2) if mod(wanted,2) == 1 
replace wanted_label = "Biweek " + string(wanted*2-2) + "-" + string(wanted*2-1) if mod(wanted,2) == 0

however, my dataset spans from 2019-2020, 2-years period, so I am working on how to add year var infront of "Biweek", as the label added only works for a year.

Nick Cox, I've read the articles you've recommended and do agree what I want is impossible to implement at the moment.

That being said, I should probably revisit and think if I really need that much of precision in generating biweekly time variable.

Thank you for your advices!

[Andrew Musau]

Add

replace wanted_label = string(year(date)) + " " + wanted_label

However note that the label is only informative in this instance. Your variable is calendar week and does not distinguish values in different years - i.e., week 1 in 2019 is grouped with week 1 in 2020. If you want a variable that distinguishes year, then

gen wanted2 = group(year wanted), label

where "wanted" is the variable generated in #3.

[Vincent KH Lee]

Andrew, thank you so much!

gen biweek_label = ""
replace biweek_label = string(year) + " " + "Biweek" + string(biweek*2-1) + "-" + string(biweek*2) if mod(biweek,2) == 1 
replace biweek_label = string(year) + " " + "Biweek " + string(biweek*2-1) + "-" + string(biweek*2) if mod(biweek,2) == 0

(I changed the name from wanted ==> biweek)

I was actually able to label it just fine as you've pointed out, but couldn't really find a way to sort the wanted variable in order.

basically, what happens if I sort the following biweek_label variable I generated it would return strangely sorted results, which I assume is due to string variables that I added at the end (+ string(biweek*2) if mod(biweek,2)).

If it were to be sorted properly, I was going to use encode(), gen(), so that I can use them as time variable in the panel setting (xtset id biweek, for instance).

But your suggestion is much better and indeed works without a problem!

Thanks a bunch!

[Vincent KH Lee]

Just a minor update on the code suggested on #6, for future viewers of this thread,

change gen() --> egen()

egen wanted2 = group(year wanted), label

as group() is a function of egen command, gen() will result in an error.

Thanks!

[Nick Cox]

Here is a belated filling-out of #4

. 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

[Nick Cox]

Here is some technique gathered together. In essence I sngle out two routes among several:

1. Starting with the idea that weeks start on Mondays: Downsides are that you are likely to get incomplete biweeks and biweeks spanning years. The simplest label here seems to be the first day of each biweek.

2. Starting with the idea of Stata's week: Downside is that the last biweek of each year will always be 15 or 16 days long.

clear 
set obs 731
gen date = mdy(12, 31, 2018) + _n 
gen DOW = word("`c(Wdays)'", 1 + dow(date))
gen year = year(date)
format date %td 

gen week1 = sum(dow(date) == 1)
gen biweek1 = floor(week1/2)
egen biweek1_min = min(date), by(biweek1)
gen biweek1_lbl = strofreal(biweek1_min, "%td")

gen biweek2 = ceil(week(date)/2)
egen biweek2_lbl = concat(year biweek2), p(" ")

ds year week1 biweek1_min, not 
l `r(varlist)' in 1/31, sepby(biweek1) noobs abbrev(15)

  +-----------------------------------------------------------------+
  |      date   DOW   biweek1   biweek1_lbl   biweek2   biweek2_lbl |
  |-----------------------------------------------------------------|
  | 01jan2019   Tue         0     01jan2019         1        2019 1 |
  | 02jan2019   Wed         0     01jan2019         1        2019 1 |
  | 03jan2019   Thu         0     01jan2019         1        2019 1 |
  | 04jan2019   Fri         0     01jan2019         1        2019 1 |
  | 05jan2019   Sat         0     01jan2019         1        2019 1 |
  | 06jan2019   Sun         0     01jan2019         1        2019 1 |
  | 07jan2019   Mon         0     01jan2019         1        2019 1 |
  | 08jan2019   Tue         0     01jan2019         1        2019 1 |
  | 09jan2019   Wed         0     01jan2019         1        2019 1 |
  | 10jan2019   Thu         0     01jan2019         1        2019 1 |
  | 11jan2019   Fri         0     01jan2019         1        2019 1 |
  | 12jan2019   Sat         0     01jan2019         1        2019 1 |
  | 13jan2019   Sun         0     01jan2019         1        2019 1 |
  |-----------------------------------------------------------------|
  | 14jan2019   Mon         1     14jan2019         1        2019 1 |
  | 15jan2019   Tue         1     14jan2019         2        2019 2 |
  | 16jan2019   Wed         1     14jan2019         2        2019 2 |
  | 17jan2019   Thu         1     14jan2019         2        2019 2 |
  | 18jan2019   Fri         1     14jan2019         2        2019 2 |
  | 19jan2019   Sat         1     14jan2019         2        2019 2 |
  | 20jan2019   Sun         1     14jan2019         2        2019 2 |
  | 21jan2019   Mon         1     14jan2019         2        2019 2 |
  | 22jan2019   Tue         1     14jan2019         2        2019 2 |
  | 23jan2019   Wed         1     14jan2019         2        2019 2 |
  | 24jan2019   Thu         1     14jan2019         2        2019 2 |
  | 25jan2019   Fri         1     14jan2019         2        2019 2 |
  | 26jan2019   Sat         1     14jan2019         2        2019 2 |
  | 27jan2019   Sun         1     14jan2019         2        2019 2 |
  |-----------------------------------------------------------------|
  | 28jan2019   Mon         2     28jan2019         2        2019 2 |
  | 29jan2019   Tue         2     28jan2019         3        2019 3 |
  | 30jan2019   Wed         2     28jan2019         3        2019 3 |
  | 31jan2019   Thu         2     28jan2019         3        2019 3 |
  +-----------------------------------------------------------------+