BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rodorell
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

6 REPLIES 6
andreas_lds
Jade | Level 19

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.

Rodorell
Calcite | Level 5

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

andreas_lds
Jade | Level 19

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

Rodorell
Calcite | Level 5
You mean, my code? I edited my post. Thank you!! 🙂
Kurt_Bremser
Super User

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.

Rodorell
Calcite | Level 5
It worked! Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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