How to transfer my string variable to date variable?

[Yao Zhao]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 week float google_search_volume_index
"2004-09-12"   .6
"2004-09-19"   .5
"2004-09-26"   .5
"2004-10-03"   .5
"2004-10-10"   .5
"2004-10-17"   .5
"2004-10-24"  .55
"2004-10-31"  .55
"2004-11-07"  .55
"2004-11-14"  .55
"2004-11-21"   .6
"2004-11-28"   .5
"2004-12-05"   .5
"2004-12-12"   .5
"2004-12-19"   .5
"2004-12-26"  .55
"2005-01-02"  .55
"2005-01-09"   .6
"2005-01-16"   .7
"2005-01-23"  .65
"2005-01-30"   .7
"2005-02-06"  .75
"2005-02-13"   .8
"2005-02-20"  .85
"2005-02-27"  .55
"2005-03-06"   .6
"2005-03-13"  .75
"2005-03-20"  .85
"2005-03-27"   .7
"2005-04-03"  .75
"2005-04-10"   .9
"2005-04-17"   .8
"2005-04-24"    1
"2005-05-01" 1.05
"2005-05-08"  .95
"2005-05-15" 1.05
"2005-05-22" 1.05
"2005-05-29"   .9
"2005-06-05" 1.15
"2005-06-12"  1.1
"2005-06-19"  .85
"2005-06-26"  .95
"2005-07-03"  .95
"2005-07-10"   .9
"2005-07-17" 1.05
"2005-07-24" 1.15
"2005-07-31" 1.15
"2005-08-07"   .9
"2005-08-14"  .95
"2005-08-21"  1.2
"2005-08-28"  1.5
"2005-09-04" 1.35
"2005-09-11" 1.25
"2005-09-18" 1.35
"2005-09-25"  1.6
"2005-10-02" 1.75
"2005-10-09"  1.4
"2005-10-16"  2.8
"2005-10-23"  3.4
"2005-10-30"  3.6
"2005-11-06" 2.35
"2005-11-13"  2.3
"2005-11-20"  1.6
"2005-11-27" 1.55
"2005-12-04"  1.7
"2005-12-11"  1.6
"2005-12-18"  1.9
"2005-12-25" 1.55
"2006-01-01" 1.65
"2006-01-08"  1.5
"2006-01-15"  1.4
"2006-01-22" 1.15
"2006-01-29" 1.35
"2006-02-05"  1.3
"2006-02-12" 1.05
"2006-02-19"  1.3
"2006-02-26"  1.3
"2006-03-05" 1.35
"2006-03-12" 1.35
"2006-03-19"  1.1
"2006-03-26"  .95
"2006-04-02" 1.05
"2006-04-09"    1
"2006-04-16" 1.15
"2006-04-23"  .95
"2006-04-30" 1.35
"2006-05-07" 1.25
"2006-05-14" 1.45
"2006-05-21" 1.25
"2006-05-28" 1.35
"2006-06-04" 1.25
"2006-06-11" 1.05
"2006-06-18" 1.25
"2006-06-25" 1.15
"2006-07-02" 1.05
"2006-07-09" 1.05
"2006-07-16" 1.15
"2006-07-23"   .9
"2006-07-30"    1
"2006-08-06"  1.6
end

I need to transfer my "week" variable to the date, because my next step is drawing a figure: Time-series line plot. The y-axis is my 2nd variable, and the x-axis is the date.

[Sergiy Radyakin]

generate d=date(week,"YMD")
generate w=wofd(d)
format w %tw
list

     +---------------------------------------------+
     |       week   google~x           d         w |
     |---------------------------------------------|
  1. | 2004-09-12         .6   12sep2004   2004w37 |
  2. | 2004-09-19         .5   19sep2004   2004w38 |
  3. | 2004-09-26         .5   26sep2004   2004w39 |
  4. | 2004-10-03         .5   03oct2004   2004w40 |
  5. | 2004-10-10         .5   10oct2004   2004w41 |
     |---------------------------------------------|

....................................................

 96. | 2006-07-09       1.05   09jul2006   2006w28 |
 97. | 2006-07-16       1.15   16jul2006   2006w29 |
 98. | 2006-07-23         .9   23jul2006   2006w30 |
 99. | 2006-07-30          1   30jul2006   2006w31 |
100. | 2006-08-06        1.6   06aug2006   2006w32 |
     +---------------------------------------------+

[Nick Cox]

Sergiy Radyakin I don’t think that’s a good idea. Stata’s weeks don’t map onto these dates, as explained in my pieces on weeks in the Stata Journal. Better to get daily dates and then tsset or xtset with delta(7).

search week, sj

[Nick Cox]

Yao Zhao's weeks are defined by Sundays. Therefore we can set up our counting in terms of the weeks since the first Sunday in each year. Such weeks will run 1 to 52 in some years and 1 to 53 in other years (unlike Stata's weeks in which week 52 is always 8 or 9 days long and week 53 never occurs).

If we don't use Stata weeks then Stata's weekly date format is out of order for us: it won't ever show week 53 and as applied to daily dates it will produce nonsense any way.

Stata won't let you define your own display format. No matter: a desired format can be simulated as a string variable and then mapped to value labels using labmask

. search labmask , sj

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

SJ-8-2  gr0034  . . . . . . . . . .  Speaking Stata: Between tables and graphs
        (help labmask, seqvar if installed) . . . . . . . . . . . .  N. J. Cox
        Q2/08   SJ 8(2):269--289
        outlines techniques for producing table-like graphs

Here's complete code. For the data example in #1 value labels every 26 weeks or so seem quite good; for a longer or shorter data series change according to taste. I've shown how to calculate string variables for weekly formats in various ways. Naturally you can display daily dates if you prefer.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 week float google_search_volume_index
"2004-09-12"   .6
"2004-09-19"   .5
"2004-09-26"   .5
"2004-10-03"   .5
"2004-10-10"   .5
"2004-10-17"   .5
"2004-10-24"  .55
"2004-10-31"  .55
"2004-11-07"  .55
"2004-11-14"  .55
"2004-11-21"   .6
"2004-11-28"   .5
"2004-12-05"   .5
"2004-12-12"   .5
"2004-12-19"   .5
"2004-12-26"  .55
"2005-01-02"  .55
"2005-01-09"   .6
"2005-01-16"   .7
"2005-01-23"  .65
"2005-01-30"   .7
"2005-02-06"  .75
"2005-02-13"   .8
"2005-02-20"  .85
"2005-02-27"  .55
"2005-03-06"   .6
"2005-03-13"  .75
"2005-03-20"  .85
"2005-03-27"   .7
"2005-04-03"  .75
"2005-04-10"   .9
"2005-04-17"   .8
"2005-04-24"    1
"2005-05-01" 1.05
"2005-05-08"  .95
"2005-05-15" 1.05
"2005-05-22" 1.05
"2005-05-29"   .9
"2005-06-05" 1.15
"2005-06-12"  1.1
"2005-06-19"  .85
"2005-06-26"  .95
"2005-07-03"  .95
"2005-07-10"   .9
"2005-07-17" 1.05
"2005-07-24" 1.15
"2005-07-31" 1.15
"2005-08-07"   .9
"2005-08-14"  .95
"2005-08-21"  1.2
"2005-08-28"  1.5
"2005-09-04" 1.35
"2005-09-11" 1.25
"2005-09-18" 1.35
"2005-09-25"  1.6
"2005-10-02" 1.75
"2005-10-09"  1.4
"2005-10-16"  2.8
"2005-10-23"  3.4
"2005-10-30"  3.6
"2005-11-06" 2.35
"2005-11-13"  2.3
"2005-11-20"  1.6
"2005-11-27" 1.55
"2005-12-04"  1.7
"2005-12-11"  1.6
"2005-12-18"  1.9
"2005-12-25" 1.55
"2006-01-01" 1.65
"2006-01-08"  1.5
"2006-01-15"  1.4
"2006-01-22" 1.15
"2006-01-29" 1.35
"2006-02-05"  1.3
"2006-02-12" 1.05
"2006-02-19"  1.3
"2006-02-26"  1.3
"2006-03-05" 1.35
"2006-03-12" 1.35
"2006-03-19"  1.1
"2006-03-26"  .95
"2006-04-02" 1.05
"2006-04-09"    1
"2006-04-16" 1.1
"2006-04-30" 1.35
"2006-05-07" 1.25
"2006-05-14" 1.45
"2006-05-21" 1.25
"2006-05-28" 1.35
"2006-06-04" 1.25
"2006-06-11" 1.05
"2006-06-18" 1.25
"2006-06-25" 1.15
"2006-07-02" 1.05
"2006-07-09" 1.05
"2006-07-16" 1.15
"2006-07-23"   .9
"2006-07-30"    1
"2006-08-06"  1.6
end

gen ddate = daily(week, "YMD")
gen year = year(ddate)
tsset ddate, delta(7)

* the first week starts on the first Sunday for which dow() returns 0
gen first_week = cond(dow(mdy(1, 1, year)) == 0, mdy(1, 1, year), mdy(1, 1, year) + 7 - dow(mdy(1, 1, year)))
assert dow(first_week) == 0
gen weekno = 1 + (ddate - first_week) / 7  
assert inrange(weekno, 1, 53)

list, sepby(year)

gen toshow = string(year) + "w" + string(weekno)
gen toshow2 = string(mod(year, 100), "%02.0f") + "w" + string(weekno)

* install from Stata Journal files
labmask ddate, values(toshow)

levelsof ddate if inlist(weekno, 1, 27)
line google ddate, xla(`r(levels)', grid valuelabel) xtitle("") scheme(s1color) yla(, ang(h))

https://www.statalist.org/forums/for...-of-each-month contains further discussion and a link that are both relevant.

[Yao Zhao]

Hi Nick, thank you very much for your help. Can I ask two more questions? First, for the x-axis, how to change current 2005w27 to date-month-year format in the final graph? I want x-axis to be more readable. Second, I want to add 2 vertical lines. One is solid line happening at 2005-10-19, the other is dash line happening at 2005-11-1 (Nov 1st).

Also, can anyone help me to explain -gen first_week = cond(dow(mdy(1, 1, year)) == 0, mdy(1, 1, year), mdy(1, 1, year) + 7 - dow(mdy(1, 1, year)))-?
What does this command mean？

[Nick Cox]

I've already answered hinted at #1. You can display daily dates if you prefer. You just need to choose a date display format.

gen ddate = daily(week, "YMD")
tsset ddate, delta(7)
format ddate %tddd_Mon_CCYY  
line google ddate, xla(`r(levels)', grid valuelabel) xtitle("") scheme(s1color) yla(, ang(h))

#2 is standard stuff

help added line options

After 200+ posts here over 3 years and more, it's a good idea to start using the documentation, I suggest.

[Yao Zhao]

Thanks, Nick. Can you tell me what the meaning and reason of

gen first_week = cond(dow(mdy(1, 1, year)) == 0, mdy(1, 1, year), mdy(1, 1, year) + 7 - dow(mdy(1, 1, year)))

[Nick Cox]

If 1 January in a given year is a Sunday, then it's the first Sunday in the year; otherwise the first Sunday is 1 to 6 days later depending on the day of week of 1 January. The rules are
1 day later if it is a Saturday, 2 days later if it is Friday, and so on.

help mdy() 

help dow()

help cond()