DATA Step, Macro, Functions and more

How to convert character format 2016-03-15 12:05:29.222699 to a date format ?

Reply
Contributor
Posts: 27

How to convert character format 2016-03-15 12:05:29.222699 to a date format ?

[ Edited ]

Hello,

 

I have the following values for date in character/string format and I wish to convert to date (datetime22.3) but no method seems to work.

A solution in PROC SQL would be ideal.

 

2016-03-15 12:05:29.222699


any ideas?

 

 

Super User
Super User
Posts: 6,502

Re: How to convert character format 2016-03-15 12:05:29.222699 to a date format ?

The INFORMAT of anydtdte26. seems to do the trick.

 

795   data want ;
796     input @1 anydt anydtdtm26. @1 date yymmdd10. time time17.;
797     format anydt datetime. date date. time time.;
798     put (_all_) (=);
799   cards;

anydt=15MAR16:12:05:29 date=15MAR16 time=12:05:29

 

Super User
Posts: 6,946

Re: How to convert character format 2016-03-15 12:05:29.222699 to a date format ?

You might also consider using the e8601dt informat for this conversion:

data test;
x1 = "2016-03-15 12:05:29.222699";
x2 = input(x1,e8601dt26.6);
format x2 datetime22.3;
run;

proc print noobs;
run;

Result:

            x1                                    x2

2016-03-15 12:05:29.222699    15MAR2016:12:05:29.223
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,502

Re: How to convert character format 2016-03-15 12:05:29.222699 to a date format ?

[ Edited ]

Be careful using a decimal setting on an INFORMAT. That means that when in the input text doesn't have a period then imply a period. Probably not going to be a problem with time data, but could cause real confusion and wrong results when reading values that could be integers.

Super User
Posts: 6,946

Re: How to convert character format 2016-03-15 12:05:29.222699 to a date format ?


Tom wrote:

Be careful using a decimal setting on an INFORMAT. That means that when in the input text doesn't have a period then imply a period. Probably not going to be a problem with time data, but could cause real confusion and wrong results when reading values that could be integers.


Yep. e8601dt26. (without the decimals) works just the same, the fractional part is still read correctly.

I'm just so used to clean input data (all our timestamps come from DB/2 unloads) that I don't notice such things.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,502

Re: How to convert character format 2016-03-15 12:05:29.222699 to a date format ?

[ Edited ]

Trouble is that FORMAT and INFORMAT specifcations look similar, but the meaning of adding the decimal part to an INFORMAT is totally different than the meaing of adding it to a FORMAT.  It is just best to get in the habit of NOT adding a decimal part to an INFORMAT unless you specifically did not write the period when writting the text version of the data and you want SAS to divide those values by 10**D.

Ask a Question
Discussion stats
  • 5 replies
  • 264 views
  • 2 likes
  • 3 in conversation