Two different datetime format in one column

[SASLEARNERq22]

HI SAS expert,

I have two different datetime format in my sas dataset submission_dt_tm:
1. 7/31/2023 6:47:42 PM
2. 31/7/2023 17:41

How do I standardize both into one date format.

[SASKiwi]

This can only happen if your data is in a character variable. What happens with a date like '7/8/2023'? Is that 7 Aug 2023 or 8 Jul 2023? There is no way to tell which one is correct. Where did your data come from and how did you import it? No prizes for guessing it's from Excel... 

[PaigeMiller]

You could read the data in using the ANYDTDTM informat. This is a guessing format, it tries to guess what date and time is represented, but if successful, then you can have the results in a consistent format.

[Tom]

---

@SASLEARNERq22 wrote:
HI SAS expert,

I have two different datetime format in my sas dataset submission_dt_tm:
1. 7/31/2023 6:47:42 PM
2. 31/7/2023 17:41

How do I standardize both into one date format.

---

Carefully?

SUBMISSION_DT_TM must be a character string.  Converting those strings to actual datetime values in not hard.  And for those particular values in not ambiguous.  But if either the day of the month or the hour of the day less is than 13  then you need to know what style that string being read was created. 

Consider this string:

7/8/2023 6:47:42

Is that the Seventh of August?  Or July Eighth? 

If you use one of the ANYDT... series of informats it will default to using either MDY or DMY based on your LOCALE setting.  You change the DATESTYLE option setting to force one or the other.    But if you want it to vary from observation to observation then you need some other variable in the dataset to tell you which to use.  Does your data have a country of origin variable?

Is that 6AM or 6PM?   That one is probably easier as you can probably assume it is AM if the PM designation is not there.