Constructing date from elements

[Peter Jacobsen]

I have the following variables:

Year
Month
Week number
Day of week

How do I construct a date variable from these elements?

Yours sincerely,
Peter Jacobsen

[Carlo Lazzaro]

Peter:
welcome to this forum.
As per FAQ, an example would have helped helping you out.
That said, you may want to consider:

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

. g day_week="Sun"

. g month="May"

. g day_num="29"

. g year="2022"

. egen string_date=concat( day_week month day_num year), p(",")

. generate numvar1 = date( string_date , "#MDY")

. format numvar1 %td

. list

     +-----------------------------------------------------------------+
     | day_week   month   day_num   year       string_date     numvar1 |
     |-----------------------------------------------------------------|
  1. |      Sun     May        29   2022   Sun,May,29,2022   29may2022 |
     +-----------------------------------------------------------------+

.

[Peter Jacobsen]

Hi Carlo,
Thank you and sorry for not providing an example:

set obs 1 Number of observations (_N) was 0, now 1. . g day_week="1" . g month="2" . g week_num="5" . g year="2021" So quite a bit different from your example since I only have week number and day of week which in this example is 1 (monday).

Yours sincerely,
Peter

[Carlo Lazzaro]

Peter:
you may be interested in -help datetime_business_calendars_creation- and related entry, Stata .pdf manual.

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input float(day_week month week_num year)
1 2 5 2021
0 6 24 2021
0 5 22 2022
end

gen wanted=.
forval day= 1/31{
    qui replace wanted= mdy(month, `day', year) if dow(mdy(month, `day', year))==day_week & week(mdy(month, `day', year))==week_num
}
format wanted %td

Res.:

. l

     +------------------------------------------------+
     | day_week   month   week_num   year      wanted |
     |------------------------------------------------|
  1. |        1       2          5   2021   01feb2021 |
  2. |        0       6         24   2021   13jun2021 |
  3. |        0       5         22   2022   29may2022 |
     +------------------------------------------------+

[Nick Cox]

Everything hinges on how week numbers are defined.

Some small papers on weekly data in Stata

. search week, sj

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

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

The first Monday in any year is

the first day of the year if that day is Monday (easy)
the second day if that day is Sunday (not too difficult)
the third ... seventh day if that day is Saturday Friday .Thursday Wednesday Tuesday.

Hence this code gets you the first Monday,

clear 
set obs 5
gen year = 2017 + _n

gen dow1Jan = dow(mdy(1, 1, year)) 
gen firstMonday = mdy(1, 1, year) + (dow1Jan == 0) + inrange(dow1Jan, 2, 6) * (8 - dow1Jan)
assert dow(firstMonday) == 1 

format firstMonday %td 

list

     +----------------------------+
     | year   dow1Jan   firstMo~y |
     |----------------------------|
  1. | 2018         1   01jan2018 |
  2. | 2019         2   07jan2019 |
  3. | 2020         3   06jan2020 |
  4. | 2021         5   04jan2021 |
  5. | 2022         6   03jan2022 |
     +----------------------------+

.

If and only if your weeks 1 to whatever start on the 1st to whatever Mondays of a year, that code may help.

Wrestling intermittently with small week problems over the years (likely to make you feel a small weakling) leads me to summarize my advice as: If you have a choice with weekly data in Stata, work with daily dates 7 days apart.

[Peter Jacobsen]

* Example generated by -dataex-. For more info, type help dataex
clear
input float(day_week month week_num year)
1 2 5 2021
0 6 24 2021
0 5 22 2022
end

gen wanted=.
forval day= 1/31{
qui replace wanted= mdy(month, `day', year) if dow(mdy(month, `day', year))==day_week & week(mdy(month, `day', year))==week_num
}
format wanted %td

Res.:

. l

+------------------------------------------------+
| day_week month week_num year wanted |
|------------------------------------------------|
1. | 1 2 5 2021 01feb2021 |
2. | 0 6 24 2021 13jun2021 |
3. | 0 5 22 2022 29may2022 |
+------------------------------------------------+

Hi Andrew,
Thank you for your help. It partially solves my problem. But quite a few variables are empty. What is "day_week" corresponding to in my variables written above?
Yours sincerely,
Peter

[Andrew Musau]

Defined in:

help dow()

dow(e_d)
Description: the numeric day of the week corresponding to date e_d; 0 = Sunday, 1 = Monday, ..., 6 = Saturday
Domain e_d: %td dates 01jan0100 to 31dec9999 (integers -679,350 to 2,936,549)
Range: integers 0 to 6 or missing

Essentially, if your days of the week are coded 1 "Monday", 2 "Tuesday", ..., 7 "Sunday", you first need

replace day_week=0 if day_week==7

before running the code in #5. Also check that the week numbers are defined in the same way as you have them.

help week()

[Peter Jacobsen]

This is what I mean:

clear
input float(day_week month week_num year)
1 2 5 2021
4 2 6 2021
7 2 7 2021
end 
 gen wanted=. forval day= 1/31{ qui replace wanted= mdy(month, `day', year) if dow(mdy(month, `day', year))==day_week & week(mdy(month, `day', year))==week_num } format wanted %td

Result:

 day_week    month    week_num    year    wanted 1    2    5    2021    01feb2021 4    2    6    2021    11feb2021 7    2    7    2021

[Andrew Musau]

As I said in #8

Essentially, if your days of the week are coded 1 "Monday", 2 "Tuesday", ..., 7 "Sunday", you first need

replace day_week=0 if day_week==7

before running the code in #5.

clear
input float(day_week month week_num year)
1 2 5 2021
4 2 6 2021
7 2 7 2021
end 

replace day_week =0 if day_week==7
gen wanted=.
forval day= 1/31{
    qui replace wanted= mdy(month, `day', year) if dow(mdy(month, `day', year))==day_week & week(mdy(month, `day', year))==week_num
}
format wanted %td

Res.:

. l

     +------------------------------------------------+
     | day_week   month   week_num   year      wanted |
     |------------------------------------------------|
  1. |        1       2          5   2021   01feb2021 |
  2. |        4       2          6   2021   11feb2021 |
  3. |        0       2          7   2021   14feb2021 |
     +------------------------------------------------+