topic Import csv file with datetime variable in SAS Programming

Import csv file with datetime variable

CedricLVQ — Tue, 15 Jan 2019 09:15:17 GMT

Hello all,

I am trying to import a csv file containing a datetime filled this way:

01/06/2018 21:45

where 01 is the day, then 06 the month, 2018 the year, 21 the hour and 45 the minute.

Unfortunately, by default SAS is using the ANYDTDTM16. informat which seems to be the US informat with the month filled before the day.

To do so, I used the following code:

PROC IMPORT DATAFILE="&Folder./&File" OUT=TempRH DBMS=CSV REPLACE;

DELIMITER=";";

INFORMAT Date_Heure datetime20.;

RUN;

Does anyone know how to import this correctly?

Thank you.

Cédric

Re: Import csv file with datetime variable

PeterClemmensen — Tue, 15 Jan 2019 09:57:38 GMT

Does this generate an error? If yes, post the log.

You can also import a csv file with the INFILE Statement in a data step. Tons of examples in here.

Re: Import csv file with datetime variable

rudfaden — Tue, 15 Jan 2019 10:03:07 GMT

ANYDTDTM also works with datetime values with day first

Re: Import csv file with datetime variable

Kurt_Bremser — Tue, 15 Jan 2019 10:31:03 GMT

First of all, you are more intelligent than a few grams of silicone, so it's time for you to take over.

Take the data step code created and run by proc import from the log, and adapt it. Read the datetime value into a string, dissect it into its two parts with scan(), input the first part with ddmmyy10., the second with time8., and then build your datetime value with the dhms() function.

want = dhms(input(scan(string,1),ddmmyy10.),0,0,input(scan(string,2),time5.));
format want e8601dt19.;

Re: Import csv file with datetime variable

Ksharp — Tue, 15 Jan 2019 12:50:37 GMT

Try add an option. options datestyle=dmy;

Re: Import csv file with datetime variable

CedricLVQ — Tue, 15 Jan 2019 12:52:34 GMT

No error in the log, not even a warning but the import date time is 06JAN2018 21:45 instead of 01JUN2018 21:45

Re: Import csv file with datetime variable

Ksharp — Tue, 15 Jan 2019 12:54:25 GMT

Try add an option.

options datestyle=dmy;

Re: Import csv file with datetime variable

CedricLVQ — Tue, 15 Jan 2019 12:55:34 GMT

Wouah, thanks, that works great!

Re: Import csv file with datetime variable

CedricLVQ — Tue, 15 Jan 2019 12:57:07 GMT

Thank you for you help.

My colleague advised me to use this trick but it seems that adding:

options datastyle=dmy works.