SAS : how to import a csv file with datetime variable where the data in different format between mm/dd/yyyy hh:mm:ss and dd/mm/yyyy hh:mm

Question

How to import a csv dataset into SAS properly. The main problem is the records under the variable date_time is in the format of dd/mm/yyyy hh:mm and mm/dd/yyyy hh:mm:ss? How to use SAS informat to corrrect this problem?

Answer 1

Read the CSV file with your own data step so that you can preserve the text of the field by reading it as a character variable.

Then to properly convert ambiguous string like 10/12/2021 you will need some other way to learn whether that observation should use date style of MDY or DMY. Perhaps you know what country the data is from? Or you have some other date value on the record that might help. For example if you know the dates for this record should be after Nov 2021 then you can deduce that 10/12/2021 means the tenth of December.

Once you know which style to use for a particular observation use the appropriate INFORMAT to read the date. So if you read the text into DATESTRING and you calculated a variable named DATESTYLE that tells which type of date style to use for this observations then add this snippet of code to the data step to convert the string into a datetime value.

if datestyle='MDY' then do;
   datetime=dhms(input(scan(datestring,1,' '),mmddyy10.),0,0
                .input(scan(datestring,2,' '),time8.))
   ;
end;
else do;
   datetime=dhms(input(scan(datestring,1,' '),ddmmyy10.),0,0
                .input(scan(datestring,2,' '),time8.))
   ;
end;