BookmarkSubscribeRSS Feed
Reggiete
Calcite | Level 5

Hello Everyone,

 

I am new to sas and I am trying to convert a character string which is in ISO8601 to datetime format in sas.

 

My code looks like this

 

proc sql;

Select appnum

,input(submitdate,datetime22.9) as Submitdate

from test

;quit;

 

submitdate is being pulled from an XML data source so its currently in varchar format

 

Could any help me with converting the submitdate(which is in iso8601) to datetime?  I am new and I only use proc sql; since I have a sql background and still very new to SAS.

 

 

10 REPLIES 10
novinosrin
Tourmaline | Level 20

Have you tried this link yet?

 

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003169817.htm

 

 You may want to apply one of these informat from the above link:

 

DatetimeYYYY-MM-DDThh:mm:ss.nnnnnn2008-09-15T15:53:00E8601DTw.d
Datetime with time zoneYYYY-MM-DDThh:mm:ss.nnnnnn+|-hh:mm2008-09-15T15:53:00+05:00E8601DZw.d
Read date from a datetimeYYYY-MM-DD2008-09-15E8601DNw.
Reggiete
Calcite | Level 5

hey,

 

I found that page on a google search, but don't quite understand what I would need to do to convert from ISO8601 varchar to datetime22.9 format in SAS.  using the above code.

novinosrin
Tourmaline | Level 20

The idea is to apply informat from the page and the informat should convert iso date to a datetime value of sas. Then you apply a datetime format on the numeric datetime value that you derived by applying the informat

Reeza
Super User

@Reggiete 

Input ( your_variable, <informat - what variable currently looks like>) as new_variable format=<format - what you want variable to look like>

 

The INFORMAT is likely one from @novinosrin post and the format is DATETIME20. 

 

The decimal portion of 9 is likely not what you actually want, so I would try datetime20 first. 

 



Reggiete
Calcite | Level 5

would this be correct I still get an error.

input(date, informat=e8601dzw.d) as Date format=datetime20

novinosrin
Tourmaline | Level 20

input(date, informat=e8601dzw.d) as Date format=datetime20input(date, informat=e8601dzw.d) as Date format=datetime20

 

e8601dzw.d   specify and remove informat=

w

specifies the width of the input field.

Range1–32
d

specifies the power of 10 by which to divide the value. If the data contain decimal points, the d value is ignored. This argument is optional.

Range0–31

 

Specify the informat just like you did earlier with a datetime example

Reeza
Super User

Not quite, the INPUT statement doesn't require the INFORMAT keyword.

 

input(date, e8601dz.) as Date format=datetime20. 

Reggiete
Calcite | Level 5

hey reeza, 

 

example of the date i am trying to convert '2015-12-14T12:00:29.3387979-05:00'

 

 

input(submitdate, E8601Dz.) as date format=datetime20.

 

i tried the above code and i get blanks 

ballardw
Super User

Informats will have a default width if not supplied. In the case of E8601DZ that default is 26. Which for your example is in the middle of the decimal portion of the seconds which means the required part of -05:00 is not seen by the informat. Which will require using the W part of the format.

 

data junk;
   x= '2015-12-14T12:00:29.3387979-05:00';
   y=input(x,e8601dz33.);
   format y datetime20.;
   put y datetime20.;
run;

Informats, other thant when used in fixed column input statements, seldom have problems when W is larger than the input string's length.

 

If you have more decimals in the seconds you may want to use the maximum of W=35 in the informat.

 

Is your clock actually accurate to the millionth of a second???

Reggiete
Calcite | Level 5

Thank you that worked! many thanks! 

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
  • 10 replies
  • 21578 views
  • 1 like
  • 4 in conversation