What is the SAS format and informat for "mm/dd/yyyy hh:mm:ss"?

Question

The Question

Please is there a built-in SAS format and informat for datetime in mm/dd/yyyy hh:mm:ss? For example:

06/25/2015 03:02:01 (June 25th 2015, 3 o'clock 2 minute 1 second).

The Background

I am trying to parse a CSV flatfile to a SAS dataset in which a datetime column is currently presented in mm/dd/yyyy hh:mi:ss format. I usually would just read it as a string, and then use the substr() function to pick out the date and time parts (as strings), use informat to resolve the SAS date and time (as numbers), and then combine them together to form a SAS datetime (as numbers). I am guessing there could be a cleaner way to do this and therefore am curious if there is already a built-in SAS datetime for mm/dd/yyyy hh:mm:ss which will enable me to read this in one-parse, like this:

data test;
    infile "c:\temp\test.csv" dlm=',' missover;
    input 
        dtvar : <the datetime format for mm/dd/yyyy hh:mm:ss>.
        var1 $
        var2 $
        ;
    format dtvar DATETIME19.;
run;

I have been doing lots of Google searches on this and no luck. Would be very grateful for this!

EDIT / Update (on additional research efforts and findings)

Following the generous forum responses I have performed a comparison between the bespoke (home-made) format mdyhms and the SAS built-in format mdyampm.

The success factors are:

• Format case: be able to display 1748351045 as "06/25/2015 03:02:01"
• Informat case: be able to convert "06/25/2015 03:02:01" to 1748351045

The bespoke format mdyhms looks like this:

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

The comparison exercise below reveals some interesting observations:

• Format case: the bespoke format mdyhms is the winner.
• Informat case: the built-in format mdyampm is the winner.

Now the comparison exercise...

Format Case

*** bespoke mdyhms as a format (Winner);
data try_format_mdyhms;
  x = "25JUN2015:03:02:01"dt;  /* 1750820521 */
  put x;                       /* 1750820521 */
  put x:mdyhms.;               /* 06/25/2015 03:02:01 */
run;

*** built-in mdyampm as a format (Loser);
data try_format_mdyampm;
  x = "25JUN2015:03:02:01"dt;  
  put x;                       /* 1750820521 */
  put x:mdyampm.;              /* 6/25/2015  3:02 AM */
run;

Informat Case

*** bespoke mdyhms as an informat (Loser);
data try_informat_mdyhms;
  x = input("06/25/2015 03:02:01",mdyhms.);  /* informat fail (error) */
  put x; 
run;

*** built-in mdyampm as an informat (Winner);
data try_informat_mdyampm;
  x = input("06/25/2015 03:02:01",mdyampm.);  /* 1750820521 */
  put x;                                      /* 1750820521 */
run;

Next step:

Are there any built-in (and/or bespoke) formats that will enable BOTH format and informat as per following success factors:

• Format case: be able to display 1748351045 as "06/25/2015 03:02:01"
• Informat case: be able to convert "06/25/2015 03:02:01" to 1748351045

(the bespoke mdyhms and built-in mdyampm seem to be able to achieve one of the two, but not both). Or have I missed anything?

Answer 1

There are two informats that can read the text "06/25/2015 03:02:01" and convert it to correct SAS datetime value: ANYDTDTM and MDYAMPM

Answer 2

Have a look at the stackoverflow answer here for using datetime values. You should be able to update it with:

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