topic Re: Date time format conversation in SAS Programming

Date time format conversation

helannivas88 — Thu, 30 Jan 2020 18:59:08 GMT

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.

Re: Date time format conversation

PaigeMiller — Thu, 30 Jan 2020 19:24:14 GMT

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

Re: Date time format conversation

mkeintz — Thu, 30 Jan 2020 19:46:52 GMT

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

The "datatype" and "directives" parts of Picture Statement , and
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;

Re: Date time format conversation

helannivas88 — Sun, 09 Feb 2020 17:32:17 GMT

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.

Re: Date time format conversation

helannivas88 — Sun, 09 Feb 2020 17:53:56 GMT

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;

Re: Date time format conversation

mkeintz — Sun, 09 Feb 2020 18:44:03 GMT

Cool!