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.
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;
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
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.
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
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.
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
Do you really need time to the billionth of a second???
yes, this is stock trading data and microsecond matters
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.