topic Re: String 26 to DATE - TIME in SAS Enterprise Guide

String 26 to DATE - TIME

anonymous_user — Tue, 16 Aug 2016 13:07:55 GMT

Hi guys

How can I convert this 26 lenght string "2016-08-11-15.48.53.216961" to date time format?

Regards from Brazil

Re: String 26 to DATE - TIME

ChrisHemedinger — Tue, 16 Aug 2016 13:21:00 GMT

I'm not sure that's a standard datetime format. The closest match I see is the E8601DTw.d informat. Find the list of ISO date/time formats and informats here.

data test;
  length dt 8;
  format dt datetime20.;
  /* different than 2016-08-11-15.48.53.216961 */
  dt = input("2016-08-11T15:48:53.216961", E8601DT26.);
run;

If that doesn't work, you might need to parse out the pieces and build your own datetime value in piecemeal.

Re: String 26 to DATE - TIME

Kurt_Bremser — Tue, 16 Aug 2016 13:32:27 GMT

The simplest option is to replace the dash after the date with a 'T' or a blank ('T' is better, as it conforms to the ISO standard) and use the e8601dt informat for conversion.

format outvar datetime25.6;
substr(invar,11,1) = 'T';
outvar = input(invar,e8601dt26.);

Re: String 26 to DATE - TIME

ChrisHemedinger — Tue, 16 Aug 2016 13:39:23 GMT

@Kurt_Bremser's approach is so much simpler than parsing out the pieces! I had been working on an alternative that coerced the value by replacing the '-' with the 'T' and various '.' with ':' -- but Kurt's is much more elegant so I won't bother to share 🙂

Re: String 26 to DATE - TIME

anonymous_user — Tue, 16 Aug 2016 13:52:36 GMT

Am I doing something wrong?

data A;
set A;
/*DATA_HORA_INCLUSAO_TEMP*/
format outvar datetime25.6;
substr(DATA_HORA_INCLUSAO_TEMP,11,1) = 'T';
outvar = input(DATA_HORA_INCLUSAO_TEMP,e8601dt26.);
run;

NOTE: Invalid argument to function INPUT at line 20 column 10.

Re: String 26 to DATE - TIME

Kurt_Bremser — Tue, 16 Aug 2016 14:04:02 GMT

Ah, I overlooked that one. The dots in the time need to be replaced with colons:

data test;
invar = '2016-08-11T15.48.53.216961';
format outvar datetime25.6;
substr(invar,14,1) = ':';
substr(invar,17,1) = ':';
outvar = input(invar,e8601dt26.6);
run;

Re: String 26 to DATE - TIME

anonymous_user — Tue, 16 Aug 2016 14:08:53 GMT

Thank you very much guys.

Re: String 26 to DATE - TIME

ChrisHemedinger — Tue, 16 Aug 2016 14:15:05 GMT

Ha! I should have kept going and posted my approach!

data test;
  length dt 8 dt2 8 fixdate $ 26 origdate $ 26;
  format dt datetime26.6 dt2 datetime26.6;

  /* using properly formatted input */
  dt = input("2016-08-11T15:48:53.216961", E8601DT26.);
 
  /* supplied input */
  origdate = "2016-08-11-15.48.53.216961";

  /* coerce the incoming date to a proper format */
  fixdate = substr(origdate,1,10) || 'T' 
      || translate(substr(origdate,12,8),':','.') || '.'
      || substr(origdate,21,6); 

  dt2 = input(fixdate, E8601DT26.);
 
run;

Re: String 26 to DATE - TIME

anonymous_user — Tue, 16 Aug 2016 14:54:00 GMT

Got it Chris!! Thanks