Data/Time Conversion Help

[Ali Ahmed Awan]

I have a string variable with timestamps. Please see example data below:

* Example generated by -dataex-. For more info, type help dataex
clear
input str30 report_date
" April 11 2022 9:37pm " 
" April 11 2022 9:58am " 
" April 11 2023 10pm "   
" April 11 2023 10pm "   
" April 11 2023 5:10am " 
" April 11 2023 8:55pm " 
" April 12 2016 9:16pm " 
" April 12 2019 10:30pm "
" April 12 2020 10:43am "
" April 12 2021 10:11am "
" April 12 2021 12:20pm "
end

When I convert using the following code:

gen _report_dt = clock(report_date, "MDY hm") 
format _report_dt  %tc

It misses observations where minutes part is not included. For example, " April 11 2023 10pm " and " April 11 2023 10pm " will be missing in the generated values. Is there a simple way to fix this issue?

Thanks,

Ali

[Nick Cox]

See

help datetime

for the advice to use

gen double

Otherwise I guess the missing values will be fixed using replace and format MDY h.

[Nick Cox]

To wit, this works with your data example. Other variations in presented date should be tackled in the same spirit. Look at what can't be converted.

* Example generated by -dataex-. For more info, type help dataex
clear
input str30 report_date
" April 11 2022 9:37pm " 
" April 11 2022 9:58am " 
" April 11 2023 10pm "   
" April 11 2023 10pm "   
" April 11 2023 5:10am " 
" April 11 2023 8:55pm " 
" April 12 2016 9:16pm " 
" April 12 2019 10:30pm "
" April 12 2020 10:43am "
" April 12 2021 10:11am "
" April 12 2021 12:20pm "
end

gen double wanted = clock(report_date, "MDY hm")
replace wanted = clock(report_date, "MDY h") if missing(wanted)

format wanted %tc 

list 

     +----------------------------------------------+
     |             report_date               wanted |
     |----------------------------------------------|
  1. |   April 11 2022 9:37pm    11apr2022 21:37:00 |
  2. |   April 11 2022 9:58am    11apr2022 09:58:00 |
  3. |     April 11 2023 10pm    11apr2023 22:00:00 |
  4. |     April 11 2023 10pm    11apr2023 22:00:00 |
  5. |   April 11 2023 5:10am    11apr2023 05:10:00 |
     |----------------------------------------------|
  6. |   April 11 2023 8:55pm    11apr2023 20:55:00 |
  7. |   April 12 2016 9:16pm    12apr2016 21:16:00 |
  8. |  April 12 2019 10:30pm    12apr2019 22:30:00 |
  9. |  April 12 2020 10:43am    12apr2020 10:43:00 |
 10. |  April 12 2021 10:11am    12apr2021 10:11:00 |
     |----------------------------------------------|
 11. |  April 12 2021 12:20pm    12apr2021 12:20:00 |
     +----------------------------------------------+

.

In a large dataset, follow with some equivalent to

list report_date if missing(wanted)

[Ali Ahmed Awan]

Thanks, Nick!