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

[camfarrell25]

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?

[Tom]

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

[Kurt_Bremser]

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

[Tom]

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.

[Kurt_Bremser]

---

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

[Tom]

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.