topic Re: Format for imported datetime fields as mm/dd/yyyy hh:mm in SAS Procedures

Format for imported datetime fields as mm/dd/yyyy hh:mm

lizzy28 — Wed, 25 Mar 2020 02:56:27 GMT

Hi everyone,

I am trying to import a txt file with multiple datetime fields with the format mm/dd/yyyy hh:mm, for example, 3/16/2020 3:09. I was able to import the fields with DATETIME19. format. Is it possible to have the fields display the way they were in txt file and remain the date format so that I can calculate the hour/minute differences? That means I like to have the format mm/dd/yyyy hh:mm in the imported SAS data file.

Thank you!

Lizi

Re: Format for imported datetime fields as mm/dd/yyyy hh:mm

Patrick — Wed, 25 Mar 2020 04:47:49 GMT

If you can't find a suitable out-of-the box format then there is always the option to create your own picture format using a datetime directive.

Examples found here which already create almost what you're asking for.

One needs simply to remove the directive for seconds and things should work.

proc format;
 picture dttime other='%0m/%0d/%0y %0H:%0M'
 (datatype=datetime);
run;
data test;
  dttm=datetime();
  format dttm dttime.;
run;
proc print data=test;
run;

Please note: The format will not round to minutes but just display complete minutes. 4 minutes 59 seconds will display as 4 minutes.

The internal SAS Date value remains unchanged (=count of seconds since 1/1/1960) and calculations will use the exact values.

Re: Format for imported datetime fields as mm/dd/yyyy hh:mm

ballardw — Wed, 25 Mar 2020 15:00:16 GMT

@lizzy28 wrote:

Hi everyone,

I am trying to import a txt file with multiple datetime fields with the format mm/dd/yyyy hh:mm, for example, 3/16/2020 3:09. I was able to import the fields with DATETIME19. format. Is it possible to have the fields display the way they were in txt file and remain the date format so that I can calculate the hour/minute differences? That means I like to have the format mm/dd/yyyy hh:mm in the imported SAS data file.

Thank you!

Lizi

Personally not a fan of the Microsoft date time display as you never are sure if it is two variables or one when extracted.

If you really want to duplicate the Microsoft appearance then you need to create a custom format.

proc format library=work;
picture msdt
low-high ='%m/%d/%Y %h:%0M' (datatype=datetime)
;
run;

data example;
	x='16Mar2020:03:09'dt;
	put 'Datetime format ' x= datetime. +1 'MSDT format ' x= msdt.;
run;

Caveats with the directives in the Picture appearance options: The quotes here MUST be single quotes, otherwise the directives get interpreted as macro calls. The case of the letters is important, note the %m is month and %M is minute. If you want leading 0 in month, day, hour or minute insert a zero between the % and the letter, upper and lower case Y determine number of digits in the Year. Other characters are literal characters inserted. So the slashes and a space need to be where you want.

If you sometimes what seconds and others don't you will likely need to create different formats as not using leading zeroes in month, day and hour means that you can't use the typical format length indicator to truncate seconds.

You will need to make sure the format is available in every SAS session you want to use this format.

Personally I would stick with the SAS Datetime.

Re: Format for imported datetime fields as mm/dd/yyyy hh:mm

Tom — Wed, 25 Mar 2020 15:05:57 GMT

Why would you want to use MDY order for the parts of date? That will result in strings that are ambiguous. Does '10/12/2019' mean October twelfth or tenth of December?

Re: Format for imported datetime fields as mm/dd/yyyy hh:mm

lizzy28 — Wed, 25 Mar 2020 16:16:51 GMT

It's more personal preference 🙂