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!
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;
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);
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;
@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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.