Hi all!!
I have a problem with loading some data because of the date-time format. Data comes like this:
2015-03-31T12:15:17.142412000+11
that is, yyyy-mm-ddThh:mm:ss.fffffffff+hh
which in SAS 9.4 (the one i'm using) would be "E8601DZw.d". However, it doesn't load correctly, so I started searching for a solution and looking in the SAS documentation E8601DZw.d Informat, I found it explicitly says: "Restriction: The shorter form +|–hh is not supported.", which unfortunately is mine.
Thus, what can I do? Is there any other way to read my format in this version of SAS?
Also it is important to know that the data file is a 80GB csv file, so, it takes a while to read it.
Thank you!!!
My code (edited):
data WORK.tnq ; infile 'C:\Folder\Test.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ; informat Date_Time E8601DZ32.6 ; format Date_Time E8601DZ32.6 ; input Date_Time $; * Splitting Date and Time; Date = datepart(Date_Time); Time = timepart(Date_Time); informat Date E8601DA10. ; informat Time E8601TM15.6 ; format Date E8601DA10. ; format Time E8601TM15.6 ; if _ERROR_ then call symputx('_EFIERR_',1); month = month(date) + (year(date)*12) - 24164; run;
Appending the ':00' works:
data want;
input instring :$32.;
dtval = input(cats(instring,':00'),E8601DZ35.);
format dtval datetime26.6;
cards;
2015-03-31T12:15:17.142412000+11
;
run;
Edit: corrected the length of instring down to 32.
E8601DZw.d has another limitation: "ffffff are optional fractional seconds, with a precision of up to six digits" (from the documentation), without that i would suggest to read as string, append ":00" and then using input-statement to create a sas-datetime, but i am not sure whether this works.
The fractional seconds are not a problem. I succesfully loaded some similar data that come with 9 digits, but I used 6 (the maximum, as you point out). It worked perfectly. I think my problem relates to the last +HH part. How can I read it as string? Maybe that would be an approach, although it might take a long long time (80 GB file).
Post the data-step you are using right now to read the file, then the suggestion will fit in better.
Appending the ':00' works:
data want;
input instring :$32.;
dtval = input(cats(instring,':00'),E8601DZ35.);
format dtval datetime26.6;
cards;
2015-03-31T12:15:17.142412000+11
;
run;
Edit: corrected the length of instring down to 32.
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.