Date time format conversation

[helannivas88]

Hi,

I have a dataset where the datetime column stored as 'DDMONYYYY:HH:MI:SS.mmmmmm'. I want to format to 'YYYY-MM-DD:HH:MI:SS.mmmmmm'.

I have tried with format as e8601dt19. But i got output as '2019-05-20T11:03:22'. I dont want to have T in my output.

Is there any other format that can be used to get the desired output. Thanks in Advance.

[PaigeMiller]

---

@helannivas88 wrote:

Hi,

I have a dataset where the datetime column stored as 'DDMONYYYY:HH:MI:SS.mmmmmm'. I want to format to 'YYYY-MM-DD:HH:MI:SS.mmmmmm'.

I have tried with format as e8601dt19. But i got output as '2019-05-20T11:03:22'. I dont want to have T in my output.

Is there any other format that can be used to get the desired output. Thanks in Advance.

---

I think datetime. is the format you want. Depending on how many decimal places you want after the seconds, you could use datetime19.

[mkeintz · Accepted solution]

This is a case where I think you will have to define your own format.  In particular use the PICTURE statement (in PROC FORMAT) with the option (DATATYPE=DATETIME).  This will allow you to use "directives" (the % characters below) to specify year, month, day, hour, minutes, second as well as the colon and dash separators.  You can learn more in 

1. The "datatype" and "directives" parts of Picture Statement , and
2. Example 7: Change the 24–Hour Clock to 00:00:01–24:00:00 

Here's what you probably can use:

proc format ;
  picture myfmt  
   other= '%Y-%0m-%0d:%0H:%0M:%0S' (datatype=datetime );
run;

data _null_;
   x='20may2019:11:03:22'dt;
   put x=datetime20.  /
       x=e8601dt19.   /
       x=myfmt.;
run;

[helannivas88]

Thanks @mkeintz for the picture format. But using the format , Im unable to derive the microseconds(or) milli seconds

For Eg:

proc format ;
  picture myfmt 
   other= '%Y-%0m-%0d:%0H:%0M:%0S' (datatype=datetime );
run;

data _null_;

x='08FEB2020:18:29:00.000000'dt;

put x=datetime27. /

x=e8601dt19. /

x=myfmt.;

run;

I'm not getting output as 2020-02-08 18:29:00.000000. I tried the picture format as '%Y-%0m-%0d:%0H:%0M:%000000S' (datatype=datetime ) (or) '%Y-%0m-%0d:%0H:%0M:%0S.nnnnnn' (datatype=datetime ) , but nothing works.

Please let me know if we are able to get the desired output. Thanks in Advance.

[helannivas88]

I have changed like below and it worked perfectly.

proc format ;

picture myfmt

other= '%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime );

run;

data _null_;

x='08FEB2020:18:29:00.022020'dt;

x=myfmt26.6;

run;

[mkeintz]

Cool!