BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EMC9
Obsidian | Level 7

Good Day

 

I am trying to convert a Date time stamp that I have for e.g 03APR18:08:41:37 to YYYY-MM-DD HH:MM:SS:sss (2018-05-25 08:14.368000).

 

I have searched for a solution with no luck.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
EMC9
Obsidian | Level 7

Thanks for the help ,  I manged to figure it out and the code below worked fine.

 

proc format;

     picture dtpic

     other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)

;

 

data want;

     set have;

     format Create_date dtpic.;

;run;

View solution in original post

15 REPLIES 15
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post test data in th form of a datastep.

Now we need to ask teh question, is that a datetime variable, is it text?  

Assuming it is numeric datetime, then you would put it into the relevant format, most likely an e8601dt. format.

EMC9
Obsidian | Level 7

Hi 

 

It is a datetime variable & not text.

 

I have manged to convert all to datetime as per the below step, I need to now convert the below in the desired output which is : 

YYYY-MM-DD HH:MM:SS:sss

Eg. 2018-05-25 08:14.368000

 

 

proc sql;

create table want as

select *, DHMS(CREATE_DATEINCIDENTS,0,0,0) as CREATE_DATEINCIDENTS1 format=datetime20.,
DHMS(REPORTED_DATE,0,0,0) as REPORTED_DATE1 format=datetime20.,
DHMS(RESPONDED_DATE,0,0,0) as RESPONDED_DATE1 format=datetime20.,
DHMS(CLOSED_DATE,0,0,0) as CLOSED_DATE1 format=datetime20.,
DHMS(FIRST_DEP_DATE,0,0,0) as FIRST_DEP_DATE1 format=datetime20.,
DHMS(LAST_DEP_DATE,0,0,0) as LAST_DEP_DATE1 format=datetime20.

from have;

run;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

As I mentioned, I believe e8601dt is the format you want, from the docs:

https://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#n03vr57gdoz...

Example at bottom of that page. 

 

 

Which is in the main list of formats:

https://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#p0z62k899n6...

EMC9
Obsidian | Level 7

Hi 

 

Thanks for the update.

 

I have tried that :

 

data test;
format CREATE_DATEINCIDENTS e8601dt.;
set work.'10_06_18_sla'n;

run;

 

output = 

1960-01-01T05:55:11

 

it is now displaying all 19600101 & "T" which I don't want.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

That indicates to me that create_dateincidents is not a datetime variable.  It could be a time value, as the date part is displaying a default.

Post some test data, no need to go through this guessing game.

Also, very bad idea to use:

set work.'10_06_18_sla'n;

the named string.   I would highly suggest you avoid that, it is really only for external datasources.

EMC9
Obsidian | Level 7

Hi not sure how to load data :

 

sample I copied is :

 

10APR2018:00:00:00
10APR2018:00:00:00
16APR2018:00:00:00
16APR2018:00:00:00
19APR2018:00:00:00
26APR2018:00:00:00

 

when I click on properties it displays the calender icon  & TYPE = NUMERIC & FORMAT=DATETIME20.

Kurt_Bremser
Super User

@EMC9 wrote:

Hi not sure how to load data :

 

sample I copied is :

 

10APR2018:00:00:00
10APR2018:00:00:00
16APR2018:00:00:00
16APR2018:00:00:00
19APR2018:00:00:00
26APR2018:00:00:00

 

when I click on properties it displays the calender icon  & TYPE = NUMERIC & FORMAT=DATETIME20.


Not that hard:

data have;
input CREATE_DATEINCIDENTS datetime20.;
format CREATE_DATEINCIDENTS e8601dt20.;
cards;
10APR2018:00:00:00
10APR2018:00:00:00
16APR2018:00:00:00
16APR2018:00:00:00
19APR2018:00:00:00
26APR2018:00:00:00
;
run;

proc print data=have noobs;
run;

Result:

CREATE_DATEINCIDENTS

 2018-04-10T00:00:00
 2018-04-10T00:00:00
 2018-04-16T00:00:00
 2018-04-16T00:00:00
 2018-04-19T00:00:00
 2018-04-26T00:00:00

See my footnotes for hints about posting data and code.

EMC9
Obsidian | Level 7

Thank you , is there a way to remove the "T" in the output ?

 

2018-04-26"T"00:00:00

 

Kurt_Bremser
Super User

The T is part of the ISO standard for datetimes. If you don't want it for final displays, store the formatted value in a character variable and use substr() or translate() to remove it.

ballardw
Super User

@EMC9 wrote:

Hi 

 

It is a datetime variable & not text.

 

I have manged to convert all to datetime as per the below step, I need to now convert the below in the desired output which is : 

YYYY-MM-DD HH:MM:SS:sss

Eg. 2018-05-25 08:14.368000

 

 

proc sql;

create table want as

select *, DHMS(CREATE_DATEINCIDENTS,0,0,0) as CREATE_DATEINCIDENTS1 format=datetime20.,
DHMS(REPORTED_DATE,0,0,0) as REPORTED_DATE1 format=datetime20.,
DHMS(RESPONDED_DATE,0,0,0) as RESPONDED_DATE1 format=datetime20.,
DHMS(CLOSED_DATE,0,0,0) as CLOSED_DATE1 format=datetime20.,
DHMS(FIRST_DEP_DATE,0,0,0) as FIRST_DEP_DATE1 format=datetime20.,
DHMS(LAST_DEP_DATE,0,0,0) as LAST_DEP_DATE1 format=datetime20.

from have;

run;


If SAS does not supply a date, time or datetime format to display such values exactly as you desire the options in the Proc Format PICTURE statement will let you do so.

proc format library=work;
/* default datetime18 shows 2 digit year (with 2 leading blanks, this makes custom
   datetime format that uses 18 digits and 4 digit year
   characters not part of a directive such as : or / are displayed as literal character
*/
picture mydatetime ( default=24)
low-high = '%Y-%0m-%0d %H:%M:%s'   (datatype=datetime)
;
run;

data _null_;
   x='15Feb2017:10:12:13.368'dt;
   put x= mydatetime26.6 ;
run;

For portability it may be worth staying with the SAS supplied formats though.

 

Just like other formats that control decimal displays you have to indicate how many decimals to get fractions of a second

EMC9
Obsidian | Level 7

Thanks - this is actually working but I noticed some fields didn't change , when I open the cell to view it displays the below :

2018/04/03 08:41:37 AM - I tried to edit your code below with no luck.

ballardw
Super User

@EMC9 wrote:

Thanks - this is actually working but I noticed some fields didn't change , when I open the cell to view it displays the below :

2018/04/03 08:41:37 AM - I tried to edit your code below with no luck.


Open what cell with what to view the values?

If you mean the external source file such as a spread sheet then the comparison is next to meaningless.

 

If the value is displayed in SAS that way then it is character, unless you already have a custom date time format to do AM PM time, and needs to be read with an appropriate informat, likely the ANYDTDTM (any datetime format).

data example;
   x="2018/04/03 08:41:37 AM";
   y= input(x, anydtdtm32.);
   format y datetime20.;
run;

And then apply the new format to the new actual datetime valued variable. Sometimes the ANYDTDTM needs to explicitly have more digits indicated than you think to read things correctly. Not sure why but sometimes using 32 gets better results than the default.

 

EMC9
Obsidian | Level 7

Thanks , I might be slow or missing something , I am trying to do that with my below code but it does not resolve.

 

e.g code :

 

data example;
x="2018/04/03 08:41:37 AM";
y= input(x, anydtdtm32.);
format y datetime20.;
run;

 

 

proc sql;

create table want as

select *, DHMS(CREATE_DATEINCIDENTS,0,0,0) as CREATE_DATEINCIDENTS1 format=mydatetime26.6,
DHMS(REPORTED_DATE,0,0,0) as REPORTED_DATE1 format=mydatetime26.6,
DHMS(RESPONDED_DATE,0,0,0) as RESPONDED_DATE1 format=mydatetime26.6,
DHMS(CLOSED_DATE,0,0,0) as CLOSED_DATE1 format=mydatetime26.6,
DHMS(FIRST_DEP_DATE,0,0,0) as FIRST_DEP_DATE1 format=mydatetime26.6,
DHMS(LAST_DEP_DATE,0,0,0) as LAST_DEP_DATE1 format=mydatetime26.6,
DHMS(Create_Date,0,0,0) as Create_Date1 format=datetime21.,
DHMS(HandOverRegion,0,0,0) as HandOverRegion1 format=datetime20.,
DHMS(HandOverFulfilment,0,0,0) as HandOverFulfilment1 format=datetime20.,
DHMS(FULFILLMENT_CHANGE,0,0,0) as FULFILLMENT_CHANGE1 format=datetime20.,
DHMS(Modified_Date,0,0,0) as Modified_Date format=datetime20.
/* (((Create_Date/60)/60)/24) + '01JAN1970'd as Create_Date_new format mydatetime26.6,*/
/* datepart(Create_Date)+ '01JAN1970'd as date_test format=mydatetime26.6*/

from work.x10_06_18_sla;

run;

 

The output displays correctly until LAST_DEP_DATE, from Create_Date it does not resolve.

 

can you please maybe show me what I am doing wrong.

 

thanks



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 3933 views
  • 0 likes
  • 4 in conversation