Informat for dd/mm/yyyy hh:mm:ss

[TW_]

Hi all,

I am currently trying to import a CSV file and keep getting the below Note;

NOTE: Invalid data for STARTDATE in line 2 444-462.

I know that this is being caused due to the data which is trying to be read in is 22/07/2018 09:56:20 in the UK format of dd/mm/yyyy hh:mm:ss but I cannot seem to find the right informat to properly read the data.

I have tried DATE, DATETIME, ANDTTME, even E8601DX (all with a width of 16) but nothing seems to work.

I am using a data step and defining lengths, formats, informats etc., and I am also using SAS EG 7.15 (7.100.5.5850) (64-bit) to access  a LINUX server running 9.4.

Any help would be greatly appreciated.

[Jagadishkatam]

Please use the below code in your datastep for the date variable.

data want;
INFILE '/home/test.csv' dsd;
 
 input datevar:ANYDTDTM.;
 
 format datevar DATETIME19.;
put datevar= DATETIME19.;
 
 run;

[Kurt_Bremser]

Personally, I would prefer to force strict formats. I don't trust the guessing involved by using the ANYDTDTM informat.

data want;
input string $19.;
datetime_value = dhms(input(substr(string,1,10),ddmmyy10.),0,0,input(substr(string,12),time8.));
format datetime_value e8601dt19.;
cards;
22/07/2018 09:56:20
;
run;

[mwstat]

Hi,

I tried to use this suggestion to read in data that came to me like this

6/28/2021 22:51

6/28/2021 23:41

6/29/2021 2:18

6/29/2021 5:35

6/29/2021 2:13

I modified the string to be 1,9 and the date to be mmddyy but no luck

Any suggestions?

TIA

[Kurt_Bremser]

You need to adapt the statement to the variable length of the date, the MDY order, and the shorter time:

datetime_value = dhms(input(scan(string,1),mmddyy10.),0,0,input(scan(string,2),time5.));

[mwstat]

Thanks for the suggestions. I wanted to come back and say that I have also had success using

data want1;
set attempt_nov;
time_created1= timepart(DTAttempt);
date_created1 = datepart(DTAttempt);
week1=week(date_created1);
format date_created1 mmddyy10. time_created1 time.;
run;

[Tom]

---

@mwstat wrote:

Thanks for the suggestions. I wanted to come back and say that I have also had success using

data want1;
set attempt_nov;
time_created1= timepart(DTAttempt);
date_created1 = datepart(DTAttempt);
week1=week(date_created1);
format date_created1 mmddyy10. time_created1 time.;
run;

---

That will only work if you already have a DATETIME variable, not the character string that the original question is asking about.

[SuryaKiran · Accepted solution]

You can find the answer by yourself, In SAS EG use the Import Wizard (File>Import Data in SAS Eg) to read the data from CSV. Import wizard will anatomically identify the informat and formats in most of the cases. 

It will create the code and you can copy the code and make some changes as per your requirement. 

For this scenario it gave me ANYDTDTM19. informat.

[trt]

I have to say this is the worst format to take for import.

 ANYDTDTM19. informat is choosing format for each single new line, and makes serious mistakes when month and day can be misunderstood. For example 12JUN2018 (12.06.2018) will by rule always misinterpreted as 06DEC2018.

That is why it is very important to set options datestyle=dmy;

to make it sure that day is read always first.

[SanderB]

This answer is actually wrong because ANYHDTDTM will assume MM-DD-YYYY first over DD-MM-YYYY. If you give it 12-01-2023 it will read it as the 1st of December, but it should the 12th of January.

[Kurt_Bremser]

---

@SanderB wrote:
This answer is actually wrong because ANYHDTDTM will assume MM-DD-YYYY first over DD-MM-YYYY. If you give it 12-01-2023 it will read it as the 1st of December, but it should the 12th of January.

---

This is not (entirely) correct. The ANY... informats will prefer DMY or MDY depending on the DATESTYLE system option (which has a default setting of LOCALE, which means the current locale setting also influences the date order).