topic Re: Change text to datetime in SAS Enterprise Guide

Change text to datetime

cmcquain — Fri, 12 Jan 2018 14:58:51 GMT

I am using SAS Enterprise Guide and I am trying to format a text field by the name of 'Admit Date' with data such as: 20180110164700 to a datetime format. Hopefully formatted to YYYY-MM-DD HH:MM:SS. Any help would be appreciated.

Thank you

Re: Change text to datetime

RW9 — Fri, 12 Jan 2018 15:19:17 GMT

Well, in code you do (and this assumes I am right on what the date should be - really a good idea to get the sender to apply some sort of standard date format to the data, something like ISO dates):

data want;
  set have;
  mydt=dhms(input(substr(text,1,8),yymmdd8.),input(substr(text,9,2),best.),input(substr(text,10,2),best.),input(substr(text,12,2),best.));
  format mydt datetime.;
run;

Re: Change text to datetime

cmcquain — Fri, 12 Jan 2018 16:29:20 GMT

I had to modify your code some as shown DHMS(input(substr(t3.'Admit Date'n,1,8),yymmdd8.),input(substr(t3.'Admit Date'n,9,2),hh2.),input(substr(t3.'Admit Date'n,10,2),mm2.),input(substr(t3.'Admit Date'n,12,2),ss2.)) Format = DATETIME19. I am still getting the wrong output. From the original 20180110164700 I am getting this 10JAN2018:17:05:10 when the field should look like 10JAN2018:16:47:00

Re: Change text to datetime

cmcquain — Fri, 12 Jan 2018 16:47:36 GMT

I got it! I had to change substring count on minutes and seconds.

DHMS(input(substr(t3.'Admit Date'n,1,8),yymmdd8.),input(substr(t3.'Admit Date'n,9,2),hh2.),input(substr(t3.'Admit Date'n,11,2),mm2.),input(substr(t3.'Admit Date'n,13,2),ss2.))