topic Re: Formatting string values to datetime issue in SAS Programming

Formatting string values to datetime issue

lydiawawa — Tue, 21 Aug 2018 15:11:45 GMT

Hi All,

I have a string variable with the following type of value:

04-27-2018 09:41:50:815

Code that I used to format the variable to datetime is as of the following:

data test;
   set test;
   date1 = input(date,anydtdtm30.);
   format date1 datetime30.;
run;

Instead of converting values to date and time, the returned values are all blank.

Does anyone know where is the issue is?

Thanks!

Re: Formatting string values to datetime issue

Kurt_Bremser — Tue, 21 Aug 2018 15:16:05 GMT

Instead of letting the any.... informat make guesses, I'd dissect the string into the time and date part, input those separately with the fitting informats, and then build the datetime by doing

datetime = dhms(date,0,0,time);

Re: Formatting string values to datetime issue

novinosrin — Tue, 21 Aug 2018 15:30:22 GMT

I agree with @Kurt_Bremser, along those lines

data w;
date='04-27-2018 09:41:50:815';
date_extract=input(substr(date,1,10),mmddyy10.);
time_extract=input(substr(date,11),time.);
new_date_time=dhms(date_extract,0,0,0)+time_extract;*combine date and time extract;
format new_date_time datetime30.;
run;

Re: Formatting string values to datetime issue

ballardw — Tue, 21 Aug 2018 20:41:07 GMT

@lydiawawa wrote:

Hi All,

I have a string variable with the following type of value:

04-27-2018 09:41:50:815

Code that I used to format the variable to datetime is as of the following:
data test;
   set test;
   date1 = input(date,anydtdtm30.);
   format date1 datetime30.;
run;
Instead of converting values to date and time, the returned values are all blank.

Does anyone know where is the issue is?

Thanks!

Not actually surprised. That last colon really should be a decimal to indicate fractional seconds.

If you don't NEED the fractional seconds:

data example;
  str='04-27-2018 09:41:50:815';
  dt = input(substr(str,1,19),anydtdtm.);
  format dt datetime.;
run;

Re: Formatting string values to datetime issue

SuryaKiran — Tue, 21 Aug 2018 18:28:12 GMT

You can ignore the milliseconds if not required. You can use @Kurt_Bremser approach with some other functions,


data test;
str="04-27-2018 09:41:50:815";
DATETIME=DHMS( input(scan(str,1," "),mmddyy10.),     /* Date */
			   input(scan(scan(str,2," "),1,':'),2.),/* Hour */
			   input(scan(scan(str,2," "),2,':'),2.),/* Min  */
			   input(scan(scan(str,2," "),3,':'),2.) /* Sec  */
			  );
format datetime datetime26.;
run;