topic Re: SAS 9.4 Datetime format problem in SAS Programming

SAS 9.4 Datetime format problem

Rodorell — Thu, 26 Jul 2018 11:02:19 GMT

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;

Re: SAS 9.4 Datetime format problem

andreas_lds — Thu, 26 Jul 2018 10:45:36 GMT

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.

Re: SAS 9.4 Datetime format problem

Rodorell — Thu, 26 Jul 2018 10:55:20 GMT

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).

Re: SAS 9.4 Datetime format problem

andreas_lds — Thu, 26 Jul 2018 10:53:50 GMT

Post the data-step you are using right now to read the file, then the suggestion will fit in better.

Re: SAS 9.4 Datetime format problem

Rodorell — Thu, 26 Jul 2018 11:05:05 GMT

You mean, my code? I edited my post. Thank you!! 🙂

Re: SAS 9.4 Datetime format problem

Kurt_Bremser — Thu, 26 Jul 2018 11:09:53 GMT

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.

Re: SAS 9.4 Datetime format problem

Rodorell — Fri, 27 Jul 2018 03:18:51 GMT

It worked! Thank you!