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

[lizzy28]

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

[Patrick · Accepted solution]

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.

[ballardw]

---

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

[Tom]

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?

[lizzy28]

It's more personal preference 🙂