topic Re: Importing a datetime variable with format yyyy-mm-ddThh:mm:ss.fffffffff in SAS Programming

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

somebody — Tue, 08 Feb 2022 00:17:11 GMT

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#n1xn55wyvuf9zin14s3ccylye2ck.htm

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;

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

Tom — Tue, 08 Feb 2022 02:26:05 GMT

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.

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

somebody — Tue, 08 Feb 2022 10:31:28 GMT

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

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

Kurt_Bremser — Tue, 08 Feb 2022 15:03:00 GMT

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.

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

ballardw — Tue, 08 Feb 2022 16:00:53 GMT

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

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

Tom — Tue, 08 Feb 2022 19:13:45 GMT

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

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

somebody — Tue, 08 Feb 2022 22:15:27 GMT

yes, this is stock trading data and microsecond matters