dates in Starbucks


In yesterday’s post, one of the needed cleaning was to extract date and day of week from the string:
. list date in 1/5

     +--------------------------------------------+
     |                                       date |
     |--------------------------------------------|
  1. |  Date: August 31, 2015 at 1:42:41 PM GMT+8 |
  2. | Date: August 24, 2015 at 12:36:55 PM GMT+8 |
  3. |    Date: July 27, 2015 at 2:51:27 PM GMT+8 |
  4. |    Date: July 20, 2015 at 2:45:43 PM GMT+8 |
  5. |    Date: July 20, 2015 at 2:07:49 PM GMT+8 |
     +--------------------------------------------+


There are a number of ways to do this. One is to use the strpos() and the substr() string functions. strpos(s,b) returns the position of the first occurrence of the character or set of characters b in s. For example:
. di strpos("miggy", "g")
3

. di strpos("miggy", "ig")
2

substr(s,f,l), on the other hand, returns the subset of string s, starting from the character in position f and with length l. For example:
. di substr("miggy", 1, 3)
mig

. di substr("miggy", 2, 4)
iggy

So to use substr() in our date example we need to have the position of date and its length. The date starts after Date:, so its position is 6. Since the date ends before ” at” the length must be given by the position of ” at” and 6 (note we included the empty space before at to differentiate this with the “at” in “Date”:
gen atposition = strpos(date, " at")
gen lengthdate = atposition - 6
gen d = substr(date, 6, ldengthdate)

. list date d in 1/5

     +---------------------------------------------------------------+
     |                                       date                  d |
     |---------------------------------------------------------------|
  1. |  Date: August 31, 2015 at 1:42:41 PM GMT+8    August 31, 2015 |
  2. | Date: August 24, 2015 at 12:36:55 PM GMT+8    August 24, 2015 |
  3. |    Date: July 27, 2015 at 2:51:27 PM GMT+8      July 27, 2015 |
  4. |    Date: July 20, 2015 at 2:45:43 PM GMT+8      July 20, 2015 |
  5. |    Date: July 20, 2015 at 2:07:49 PM GMT+8      July 20, 2015 |
     +---------------------------------------------------------------+

Now we have that date, how can we extract the day of week? Is July 20 a Monday? Tuesday? To do this, we need to translate our date variable d into something Stata can interpret as date. As of now, Stata reads d as any other string.
. gen d2 = daily(d, "MDY")

. list d d2 in 1/5

     +--------------------------+
     |                d      d2 |
     |--------------------------|
  1. |  August 31, 2015   20331 |
  2. |  August 24, 2015   20324 |
  3. |    July 27, 2015   20296 |
  4. |    July 20, 2015   20289 |
  5. |    July 20, 2015   20289 |
     +--------------------------+

What have we done?! Don’t panic. We can read d, Stata can read d2. But there is a way to format this for us humans:
. format d2 %td

. list d d2 in 1/5

     +------------------------------+
     |                d          d2 |
     |------------------------------|
  1. |  August 31, 2015   31aug2015 |
  2. |  August 24, 2015   24aug2015 |
  3. |    July 27, 2015   27jul2015 |
  4. |    July 20, 2015   20jul2015 |
  5. |    July 20, 2015   20jul2015 |
     +------------------------------+

Now to what we really want to get, the day of week:
. gen day = dow(d2)

. label def daylab 0 "Sunday" 1 "Monday" 2 "Tuesday" ///
      3 "Wednesday" 4 "Thursday" 5 "Friday" 6 "Saturday", replace

. label values day daylab

. sort d2

. list d d2 day in 1/5

     +---------------------------------------+
     |             d          d2         day |
     |---------------------------------------|
  1. |  May 25, 2015   25may2015      Monday |
  2. |  May 25, 2015   25may2015      Monday |
  3. |  May 26, 2015   26may2015     Tuesday |
  4. |  May 26, 2015   26may2015     Tuesday |
  5. |  May 27, 2015   27may2015   Wednesday |
     +---------------------------------------+


Yes, I am in Starbucks. This is one of those days when I have to leave very early to avoid cursing in traffic so early in the morning. Who prefers traffic when you can enjoy the view of Manila Bay parking lot?

Screen Shot 2015-09-01 at 9.10.56 AM
(Photo taken a month ago but I take the same spot every time)

One Response

  1. super helpful — thanks!

Leave a Reply