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

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

camfarrell25 — Mon, 19 Sep 2016 18:09:59 GMT

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?

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

Tom — Mon, 19 Sep 2016 17:56:28 GMT

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

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

Kurt_Bremser — Tue, 20 Sep 2016 11:55:12 GMT

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

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

Tom — Tue, 20 Sep 2016 12:54:47 GMT

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.

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

Kurt_Bremser — Tue, 20 Sep 2016 13:18:56 GMT

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

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

Tom — Tue, 20 Sep 2016 14:00:12 GMT

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.