Reading Non-Standard Datetime Values

Monday, 11 May, 2009

⨳ 1 minute read ⨳ 194 words ⨳ datetime valuesquatchsas

another datetime posting. this time, i needed to read in datetime values with the following format: DD/MM/YYYY HH:MM:SS AM where the leading zeros are optional for the day, month, and hour portion.

since i couldn’t (quickly) locate an informat that is this flexible, it was time to parse. first order of business was to get the rogue datetime value into SAS in its current state:

input x ? $50.;

next, i needed to break the string apart and use each portion as arguments for functions:

dt=dhms(input(scan(x, 1, ' '), mmddyy10.),
   0,
   0,
   input(catx(' ', scan(x, 2, ' '), scan(x, 3, ' ')), time.));

i could have used prx, but since my data set was small (obs < 2,500) and prx statements are somewhat beastly, i figured i’d use basic sas functions.

sample code can viewed below:

data null;
  input x ? $50.;
  dt=dhms(input(scan(x, 1, ' '), mmddyy10.)
        , 0
        , 0
        , input(catx(' ', scan(x, 2, ' ')
        				, scan(x, 3, ' ')), time.)
        );
  put dt datetime19.;
datalines;
5/5/2009 2:22:17 AM
04/08/2015 04:23:42 PM
run;

Reading Non-Standard Datetime Values - May 11, 2009 - Richard Koopmann