Wrangling date plus time variable

[Anne Todd]

I have some data where the date and time is all together in one string variable. I originally tried to split and parse into two variables, and then convert to date and merge them back together, but couldn't get this final step to work properly.

And then after that, I am hoping to do some data cleaning, so something like

gen level_rank = "A" if level == 1 & newdatevar < June 12 at 15:01

(I'm not sure exactly how a joint date/time variable would look so obviously that last part is not real code).

What I've attempted is something like this:

split datetime, parse(" ")
generate newdate = date(datetime1, "mdy")
format newdate %td

replace datetime2 = subinstr(datetime2, ":", "", .)
destring datetime2, replace
gen level_rank = "A" if level == 1 & newdate < date("14jun2013", "dmy") & datetime2 < 1501

So basically, I convert the day/month/year into its own true date variable in Stata, and the time to a regular numeric variable, which still allows me to do a < but surely this is less efficient than creating one standard date + time variable in Stata and working with that...but I just can't seem to get that to work. My end goal is to be able to recode variables differently based on if they took the survey before June 12 at 15:01. Thanks much.



Some example data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str15 datetime byte(level wrx)
"6/14/2013 14:00" 1 15
"6/11/2013 15:00" 2 13
"6/12/2013 18:00" 1 14
"6/11/2013 15:00" 2 13
"6/14/2013 14:00" 1 50
"6/14/2013 14:00" 1 42
"6/14/2013 14:00" 1 23
"6/11/2013 15:00" 2 13
"6/12/2013 15:00" 2 51
"6/12/2013 18:00" 2 14
"6/12/2013 15:00" 2 42
"6/11/2013 15:00" 2 13
"6/14/2013 14:00" 1  2
"6/14/2013 14:00" 1 21
"6/14/2013 14:00" 1 31
"6/14/2013 14:00" 1 26
"6/12/2013 15:00" 2 21
"6/14/2013 14:00" 1 21
"6/12/2013 15:00" 2 28
"6/12/2013 18:00" 1 14
"6/12/2013 15:00" 2 28
"6/11/2013 15:00" 2 13
"6/12/2013 18:00" 2 14
"6/11/2013 15:00" 2 13
"6/14/2013 14:00" 1 23
"6/14/2013 14:00" 1 23
"6/14/2013 14:00" 1 21
"6/14/2013 14:00" 1 31
"6/14/2013 14:00" 1 28
"6/12/2013 15:00" 2 21
"6/14/2013 14:00" 1 21
"6/12/2013 15:00" 2  8
"6/12/2013 15:00" 2  4
"6/12/2013 15:00" 2 34
end

[Nick Cox]

It seems that you know about daily dates as numeric variables but have not gone beyond that.

help datetime

introduces you to the Brave New World beyond.

* Example generated by -dataex-. For more info, type help dataex
clear
input str15 datetime
"6/11/2013 15:00"
"6/12/2013 15:00"
"6/12/2013 18:00"
"6/14/2013 14:00"
end

gen double stata_datetime = clock(datetime, "MDY hm")
format stata_datetime %tc 

list if stata_datetime > clock("12 June 2013 00:00", "DMY hm")

     +--------------------------------------+
     |        datetime       stata_datetime |
     |--------------------------------------|
  2. | 6/12/2013 15:00   12jun2013 15:00:00 |
  3. | 6/12/2013 18:00   12jun2013 18:00:00 |
  4. | 6/14/2013 14:00   14jun2013 14:00:00 |
     +--------------------------------------+

is a basic example of what you can do and

help tc()

shows how to make it easier.

[William Lisowski]

How to create a Stata date-and-time variable.

. generate double dt2 = clock(datetime,"MDYhm")

. format dt2 %tc

. list in 1/5

     +----------------------------------------------------+
     |        datetime   level   wrx                  dt2 |
     |----------------------------------------------------|
  1. | 6/14/2013 14:00       1    15   14jun2013 14:00:00 |
  2. | 6/11/2013 15:00       2    13   11jun2013 15:00:00 |
  3. | 6/12/2013 18:00       1    14   12jun2013 18:00:00 |
  4. | 6/11/2013 15:00       2    13   11jun2013 15:00:00 |
  5. | 6/14/2013 14:00       1    50   14jun2013 14:00:00 |
     +----------------------------------------------------+

Note especially that the date-and-time variable must be created as a double - a float will not have the required precision.

To address the rest of your objectives, Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

[Anne Todd]

Thank you both. It appears I do need a refresher on Chapter 24, as I was struggling with what turns out to be the fairly simple -clock- function.

[William Lisowski]

Don't feel bad about that - in writing post #4 I didn't get "MDYhm" right on the first try and had to look at help datetime to get my example to work. For me it's always time for a refresher.

[Anne Todd]

Thank you, William Lisowski!