A datetime question

[Niki Mink]

Hi all, I need help with creating a datetime variable. I've already been through the stata help and the manuals but the problem remains.
I have 2 variables in my dataset, one containing the date (string) and a second one containing the time (double)

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 tre01b double tre01c
"2022-07-15" -1.8933219e+12
"2022-07-16" -1.8933093e+12
"2022-07-16" -1.8933096e+12
"2022-07-17" -1.8933414e+12
"2022-07-17" -1.8933189e+12
"2022-07-18"  -1.893333e+12
"2022-07-23" -1.8933348e+12
"2022-07-25" -1.8933192e+12
end
format %tc tre01c

I have run the following command to merge both into a datetime variable:

generate double first_dose_day = clock( tre01b , "YMD")
format %tc first_dose_day

format tre01c %tcHH:MM
gen double dt_dose1=first_dose_day + tre01c
format dt_dose1 %tcNN/DD/CCYY_HH:MM

But there seems to be a problem as the new variable dt_dose1 gives me this:

. list dt_dose1 in 3/10

dt_dose1

3. 07/16/1962 13:15
4. 07/17/1962 16:45
5. 07/17/1962 16:40
6. 07/18/1962 07:50
7. 07/18/1962 14:05

8. 07/19/1962 10:10
9. 07/24/1962 09:40
10. 07/26/1962 14:00


I can't identify what the problem is and have tried several different ways like changing the tre01c variable to string and then using clock, etc.


I am aware that is a very basic question, but somehow I can't seem to find a solution! I would appreciate any help you can provide.
Thanks
Niki

[Hemanshu Kumar]

Consider this:

tostring tre01c, gen(tre01c2) format(%tcHH:MM:SS) force
gen dt_dose = tre01b + " " + tre01c2
gen double dt_dose1 = clock(dt_dose,"YMDhms")
format %tc dt_dose1

[Nick Cox]

On the face of it, the problem arises upstream, insofar as the time component taken literally is a time in 1900

.  di %tc -1.8933219e+12
01jan1900 13:15:00

Here's my guess at what you need:

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 tre01b double tre01c
"2022-07-15" -1.8933219e+12
"2022-07-16" -1.8933093e+12
"2022-07-16" -1.8933096e+12
"2022-07-17" -1.8933414e+12
"2022-07-17" -1.8933189e+12
"2022-07-18"  -1.893333e+12
"2022-07-23" -1.8933348e+12
"2022-07-25" -1.8933192e+12
end
format %tc tre01c

gen double wanted = clock(tre01b, "YMD") + mod(tre01c, 24 * 60 * 60000)

format wanted %tc 

list 

     +------------------------------------------------------+
     |     tre01b               tre01c               wanted |
     |------------------------------------------------------|
  1. | 2022-07-15   01jan1900 13:15:00   15jul2022 13:15:00 |
  2. | 2022-07-16   01jan1900 16:45:00   16jul2022 16:45:00 |
  3. | 2022-07-16   01jan1900 16:40:00   16jul2022 16:40:00 |
  4. | 2022-07-17   01jan1900 07:50:00   17jul2022 07:50:00 |
  5. | 2022-07-17   01jan1900 14:05:00   17jul2022 14:05:00 |
     |------------------------------------------------------|
  6. | 2022-07-18   01jan1900 10:10:00   18jul2022 10:10:00 |
  7. | 2022-07-23   01jan1900 09:40:00   23jul2022 09:40:00 |
  8. | 2022-07-25   01jan1900 14:00:00   25jul2022 14:00:00 |
     +------------------------------------------------------+

[Niki Mink]

That worked perfectly! Thank you very much. I'll look into the 'mod' function in detail.

Thanks again!

[Nick Cox]

The issue is probably that some other software regards 1 January 1900 as the origin for date-times.

[Sandra Summers]

1900 Start Date
I have a very basic question. I have imported and XLS file into Stata. The year variable is %10.0g my years are 2013,2014,2015 ......2020

However the xtdescribe

code: 10000163, 10000216, ..., 10083476 n = 280
year: 1900, 2013, ..., 2020 T = 9
Delta(year) = 1 unit
Span(year) = 121 periods
(code*year uniquely identifies each observation)

I have checked the data and there are no 1900 in the list, and there are no missing values in the year column.

Please can you tell me how to correct the data for this? Many thanks