Generate Date/Time Variable

[Shisho Jakas]

Dear Statalist,

I am writing to ask for your help with generating date/time variable, which I am struggling for. The variables I have is as follows.

Month: Month
Date: Date
StartH: Start Hour
StartM: Start Minute
EndH: End Hour
EndM: End Minute
AM: (dummy) AM=1, PM=0

What I would like to do is to create the variable that I can see all the information including month, date, start hour, start minute, end hour, end minute, AM/PM. I simply altered from numeric to string all the variables, but it does not look any good. The code I used is:

tostring AM, replace 
replace AM="AM" if AM==1
replace AM="PM" if AM==0
gen startt=string(Month)+"/"+string(Date)+""+string(StartH)+":"'+string(StartM)+""+string(AM)
gen endt=string(Month)+"/"+string(Date)+""+string(EndH)+":"'+string(EndM)+""+string(AM)

Since the thing I need to do is to calculate the survey duration that could be obtained by subtracting start time from end time, I do not think that this is the typical way to do it. This does not work to calculate the length of the survey by entering the code like:

gen duration=endt-startt

I searched for several posts, but I could not find any great answers for my issue (or I am not clever enough to understand those and apply them to mine)
Could you please let me know the code for this matter?

I attach the data extracted by dataex from the datafile below. I really appreciate your help in advance.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(Month Date StartH StartM EndH EndM AM)
 7  9  9 10  9 19  1
 7 10  2 52  2 57  0
 .  .  .  .  .  .  .
 7  8  4 15  4 20  0
 7  9 16 50 17 23  0
 7  6  2 33  2 52  0
 7  7  9  0  9 15  1
 7 10  6 55  7 23  0
 7  8  8  5  8 36  0
 7  8 11 30 11 31  1
 7  8  6 14  6 19  0
 .  .  .  .  .  .  .
 7  7  8  0  8 10  1
 7 11  2 10  2 36  0
 7 11  9 45 10 12  1
 7  9  6 10  6 14  1
 .  .  .  .  .  .  .
 7  7  5 58  6  4  0
 7  7  6 37  7 15  0
 7 10  1  0  1 35  0
 7  8  5 15  5 28  0
 .  .  .  .  .  .  .
 .  .  .  .  .  .  .
 7 10 10  3 10  8  1
 7  9 11 15 11 42  1
 7  9 10 30 11 10  1
 7  8  7 24  7 26  0
 7  7  5 15  6  0  0
 .  .  .  .  .  .  .
 7  8  8 35  8 40  1
 7 10  9 30  9 44  1
 7  9  8  0  8  5 99
 7  9 10 15 10 45  1
 .  .  .  .  .  .  .
 7 10  9  0  9 40  1
99 99 99 99 99 99 99
 .  .  .  .  .  .  .
 7  6  1 30  2 10  0
 7  9  9  0  9  5  1
 7 10  8 15  8 38  1
 7 10  9  5  9 45  1
 7  7  7 40  8 20  0
 7  8 12  0 12 30 99
 7  9 11 43 11 56  1
 7 11  5  4  5 25  0
 .  .  .  .  .  .  .
end
label values CalAP_e_id1 CalAP_e_id1_label
label def CalAP_e_id1_label 0 "PM", modify
label def CalAP_e_id1_label 1 "AM", modify

[Nick Cox]

You've started in the right kind of way. You just need to push a little further -- and above all read help datetime to understand the principles.

How about this?

foreach v in Month Date StartH StartM EndH EndM { 
    local new = upper("`v'")
    local `new' string(`v')
}

gen double start = clock(`MONTH' + " " + `DATE' + " " + `STARTH' + ":" + `STARTM', "MD h m") + cond(AM == 0, 12 * 60 * 60000, 0)
gen double end = clock(`MONTH' + " " + `DATE' + " " + `ENDH' + ":" + `ENDM', "MD h m") + cond(AM == 0, 12 * 60 * 60000, 0)
format start end %tcHH:MM 

gen duration = (end - start) / 60000 

sort start end 
list if !missing(start, end), sepby(Month Date)

    +------------------------------------------------------------------------------+
     | Month   Date   StartH   StartM   EndH   EndM   AM   start     end   duration |
     |------------------------------------------------------------------------------|
  1. |     7      6        1       30      2     10    0   13:30   14:10         40 |
  2. |     7      6        2       33      2     52    0   14:33   14:52         19 |
     |------------------------------------------------------------------------------|
  3. |     7      7        8        0      8     10    1   08:00   08:10         10 |
  4. |     7      7        9        0      9     15    1   09:00   09:15         15 |
  5. |     7      7        5       15      6      0    0   17:15   18:00         45 |
  6. |     7      7        5       58      6      4    0   17:58   18:04          6 |
  7. |     7      7        6       37      7     15    0   18:37   19:15         38 |
  8. |     7      7        7       40      8     20    0   19:40   20:20         40 |
     |------------------------------------------------------------------------------|
  9. |     7      8        8       35      8     40    1   08:35   08:40          5 |
 10. |     7      8       11       30     11     31    1   11:30   11:31          1 |
 11. |     7      8       12        0     12     30   99   12:00   12:30         30 |
 12. |     7      8        4       15      4     20    0   16:15   16:20          5 |
 13. |     7      8        5       15      5     28    0   17:15   17:28         13 |
 14. |     7      8        6       14      6     19    0   18:14   18:19          5 |
 15. |     7      8        7       24      7     26    0   19:24   19:26          2 |
 16. |     7      8        8        5      8     36    0   20:05   20:36         31 |
     |------------------------------------------------------------------------------|
 17. |     7      9        6       10      6     14    1   06:10   06:14          4 |
 18. |     7      9        8        0      8      5   99   08:00   08:05          5 |
 19. |     7      9        9        0      9      5    1   09:00   09:05          5 |
 20. |     7      9        9       10      9     19    1   09:10   09:19          9 |
 21. |     7      9       10       15     10     45    1   10:15   10:45         30 |
 22. |     7      9       10       30     11     10    1   10:30   11:10         40 |
 23. |     7      9       11       15     11     42    1   11:15   11:42         27 |
 24. |     7      9       11       43     11     56    1   11:43   11:56         13 |
 25. |     7      9       16       50     17     23    0   04:50   05:23         33 |
     |------------------------------------------------------------------------------|
 26. |     7     10        8       15      8     38    1   08:15   08:38         23 |
 27. |     7     10        9        0      9     40    1   09:00   09:40         40 |
 28. |     7     10        9        5      9     45    1   09:05   09:45         40 |
 29. |     7     10        9       30      9     44    1   09:30   09:44         14 |
 30. |     7     10       10        3     10      8    1   10:03   10:08          5 |
 31. |     7     10        1        0      1     35    0   13:00   13:35         35 |
 32. |     7     10        2       52      2     57    0   14:52   14:57          5 |
 33. |     7     10        6       55      7     23    0   18:55   19:23         28 |
     |------------------------------------------------------------------------------|
 34. |     7     11        9       45     10     12    1   09:45   10:12         27 |
 35. |     7     11        2       10      2     36    0   14:10   14:36         26 |
 36. |     7     11        5        4      5     25    0   17:04   17:25         21 |
     +------------------------------------------------------------------------------+

I think you mangled the dataex output. The label values command won't work because there is no such variable. Hands off the results of our command!

[Nick Cox]

Note: The code treats 99 for am as equivalent to 1. But it looks as if 99 is some kind of code for missing. Best fix that straight away using mvdecode.

[Shisho Jakas]

Dear Nick,

I appreciate your kind teaching and suggestion. I am sorry for the confusion that I have not properly renamed the variables in the stata before extracting the data so that I changed the names after dataex. I could saved the local you suggested, but the error occurred saying: +" "+ invalid name.

How can I fix this problem?

And, I would like to ask one more question about the code you wrote to understand the principle more.

 gen double start = clock(`MONTH' + " " + `DATE' + " " + `STARTH' + ":" + `STARTM', "MD h m") + cond(AM == 0, 12 * 60 * 60000, 0)

Could you let me know what the last part (underlined) of this line means? : cond(AM == 0, 12 * 60 * 60000, 0)

Thank you so much in advance.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(Month Date StartH StartM EndH EndM AM)
 7  9  9 10  9 19 1
 7 10  2 52  2 57 0
 .  .  .  .  .  . .
 7  8  4 15  4 20 0
 7  9 16 50 17 23 0
 7  6  2 33  2 52 0
 7  7  9  0  9 15 1
 7 10  6 55  7 23 0
 7  8  8  5  8 36 0
 7  8 11 30 11 31 1
 7  8  6 14  6 19 0
 .  .  .  .  .  . .
 7  7  8  0  8 10 1
 7 11  2 10  2 36 0
 7 11  9 45 10 12 1
 7  9  6 10  6 14 1
 .  .  .  .  .  . .
 7  7  5 58  6  4 0
 7  7  6 37  7 15 0
 7 10  1  0  1 35 0
 7  8  5 15  5 28 0
 .  .  .  .  .  . .
 .  .  .  .  .  . .
 7 10 10  3 10  8 1
 7  9 11 15 11 42 1
 7  9 10 30 11 10 1
 7  8  7 24  7 26 0
 7  7  5 15  6  0 0
 .  .  .  .  .  . .
 7  8  8 35  8 40 1
 7 10  9 30  9 44 1
 7  9  8  0  8  5 .
 7  9 10 15 10 45 1
 .  .  .  .  .  . .
 7 10  9  0  9 40 1
99 99 99 99 99 99 .
 .  .  .  .  .  . .
 7  6  1 30  2 10 0
 7  9  9  0  9  5 1
 7 10  8 15  8 38 1
 7 10  9  5  9 45 1
 7  7  7 40  8 20 0
 7  8 12  0 12 30 .
 7  9 11 43 11 56 1
 7 11  5  4  5 25 0
 .  .  .  .  .  . .
end
label values AM CalAP_e_id1_label
label def CalAP_e_id1_label 0 "PM", modify
label def CalAP_e_id1_label 1 "AM", modify

[Nick Cox]

I could save the local you suggested, but the error occurred saying: +" "+ invalid name. How can I fix this problem?

I have almost no idea how to fix that, as you don't show the code you tried. Almost no idea -- except that people often define locals in one place and then try to access them in another. One place might be the main interactive session and another place might be a do-file editor window, or vice versa. This error arises from the definition of local macros -- as visible only locally -- and is explained in excruciating detail at https://journals.sagepub.com/doi/10....36867X20931028

12 * 60 * 60000

is the number of milliseconds (ms) in half a day. 12 hours have 60 minutes each and each of those have 60000 ms. That's what you need to add to get pm times represented properly, given the definitions of hours, minutes and seconds and Stata's use of ms for date-time variables. I trust myself to say multiply 12 and 60 in my head, but the whole sum is too much like hard work when Stata is better at arithmetic than I am. Equally there is a special function to help but I couldn't be bothered to look it up yesterday, but I just did that and here it is:

. di msofhours(12)
43200000

. di 12 * 60 * 60000
43200000

If you are asking these questions, that seems to imply that you have not yet read

help datetime

which people often want to postpone reading because it is a lot of detail and not especially amusing -- but not understanding how dates and times are held in Stata just leads to errors, delays and confusion.

[Shisho Jakas]

Dear Nick,

I tried the exact same code you wrote for me. Thank you for your explanation. I will look into the datetime as you suggested then try to think more to solve it. Thank you!

[Nick Cox]

Well no, because the code worked for me and there is no special setting

set stataguru on

or

set njc on

which makes code work for me and not for you. If it didn't work, something else is wrong and you need to explain what you did exactly (although I made a guess at what is wrong);