Search code examples
stata

Stata: date comparison in double

I'm trying to divide the data by a certain datetime.

enter image description here

I've created e_timefrom what was originally a string "2019-10-15 20:33:04" for example.

To obtain all the information from the string containing h:m:s, I uses the following command to create a double

gen double e_time = clock(event_timestamp, "YMDhms")

Now I get the result I want from format e_time %tc (human readable), I want to generate a new variable anything that is greater than 2019-10-15 as 1 and anything less than that as 0 .

I've tried

// 1 

gen new_d = 0 if e_time < "1.887e+12"
replace new_d = 1 if e_time >= "1.887e+12"

// 2 

gen new_d = 0 if e_time < "2019-10-15"
replace new_d = 1 if e_time > "2019-10-15"

However, I get an error message type mismatch.

I tried converting a string "2019-10-15" to double \to check if 1.887e+12 really meant 2019-10-15 using display, but I'm not sure how the command really works here.

Anyhow I tried

// 3 

di clock("2019-10-15", "YMDhms")

but it didn't work.

Can anyone give advice on comparing dates that are in a double format properly?

Solution

  • Your post is a little hard to follow (a reproducible data example would help a lot) but the error type mismatch is because e_time is numeric, and "2019-10-15" is a string.

    I suggest the following:

    clear

input str20 datetime
"2019-10-14 20:33:04"
"2019-10-16 20:33:04"
end

* Keep first 10 characters
gen date = substr(datetime,1,10)

* Check that all strings are 10 characters
assert length(date) == 10

* Convert from string to numeric date variable
gen m = substr(date,6,2)
gen d = substr(date,9,2)
gen y = substr(date,1,4)

destring m d y, replace

gen newdate = mdy(m,d,y)
format newdate %d

gen wanted = newdate >= mdy(10,15,2019) & !missing(newdate)

drop date m d y

list

     +------------------------------------------+
     |            datetime     newdate   wanted |
     |------------------------------------------|
  1. | 2019-10-14 20:33:04   14oct2019        0 |
  2. | 2019-10-16 20:33:04   16oct2019        1 |
     +------------------------------------------+