Importing a datetime variable with format yyyy-mm-ddThh:mm:ss.fffffffff

[somebody]

I have a csv data file that contains a datetime column with value like the title. Below is an example. I tried using PROC IMPORT and the Import Data option but the output is just the date, which is clearly not what I am after. 

2021-06-01T07:00:00.294210290-04

How can I import this column in a data step? I search online and run into this format but I still could not figure out how. 

https://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#n1xn55wyvuf...

So far, this is what I have tried: 

 proc import datafile='F:\data2000.csv' out=class
               dbms=csv replace;
guessingrows=10000;
run;

data WORK.class   ;
	%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
	infile 'F:\data2000.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
	  ....
	  informat Date_Time E8601DTw. ;
	  ...
	  
	  format Date_Time E8601DTw. ;
	 ...
	input
	          ...
	           Date_Time
	           ...
	;
	if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
	run;

[Tom]

That is not a style that SAS has an informat for.  It is very similar to the E8601DZ, but that needs an actual TIME string after the + or -.

You could read it as a string and the append :00 to the end and then use the E8601DZ informat.

Example:

143   data test;
144     string='2021-06-01T07:00:00.294210290-04';
145     len=length(string);
146     dt = input(cats(string,':00'),E8601DZ35.);
147     format dt datetime29.9;
148     put (_all_) (=/);
149   run;


string=2021-06-01T07:00:00.294210290-04
len=32
dt=01JUN2021:11:00:00.294210196

Note there is no need to use PROC IMPORT to guess how to read a text file. Just write your own data step to read it.

[somebody]

thanks, I used a DATA step to import the date_time column as text and extract the date part. However, I cannot format the time part as time as it has some microseconds. Do you know how I could format the time part? my time variable looks like this I think the -04 is time zone, not sure how to adjust for it

07:00:00.294210290-04

[Kurt_Bremser]

See how to extra the time from the datetime, and format it:

data _null_;
string='2021-06-01T07:00:00.294210290-04';
dt = input(cats(string,':00'),E8601DZ35.);
timevar = timepart(dt);
format
  dt datetime29.9
  timevar time18.9
;
put (_all_) (=/);
run;

You will note that the datetime value is not correct after the 6th decimal (microseconds), because if the limits of 64-bit floating point arithmetic.

[Tom · Accepted solution]

Parse it again into two parts.

data test;
  string='07:00:00.294210290-04';
  time = input(scan(string,1,'+-'),time20.);
  offset_hours = input(substr(string,length(string)-2),32.);
  format time tod19.9 ;
  put (_all_) (=/);
run;

Results

string=07:00:00.294210290-04
time=07:00:00.294210290
offset_hours=-4

[ballardw]

Do you really need time to the billionth of a second???

[somebody]

yes, this is stock trading data and microsecond matters