SAS 9.4 Datetime format problem

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

SAS 9.4 Datetime format problem

[ Edited ]

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;

 


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 10,571

Re: SAS 9.4 Datetime format problem

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Valued Guide
Posts: 629

Re: SAS 9.4 Datetime format problem

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.

New Contributor
Posts: 4

Re: SAS 9.4 Datetime format problem

[ Edited ]
Posted in reply to andreas_lds

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

Valued Guide
Posts: 629

Re: SAS 9.4 Datetime format problem

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

New Contributor
Posts: 4

Re: SAS 9.4 Datetime format problem

Posted in reply to andreas_lds
You mean, my code? I edited my post. Thank you!! Smiley Happy
Solution
3 weeks ago
Super User
Posts: 10,571

Re: SAS 9.4 Datetime format problem

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 4

Re: SAS 9.4 Datetime format problem

Posted in reply to KurtBremser
It worked! Thank you!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 82 views
  • 0 likes
  • 3 in conversation