Date time stamp conversion

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Date time stamp conversion

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:MMSmiley FrustratedS:sss (2018-05-25 08:14.368000).

 

I have searched for a solution with no luck.

 

Thanks


Accepted Solutions
Solution
‎06-13-2018 09:11 AM
Contributor
Posts: 42

Re: Date time stamp conversion

Posted in reply to KurtBremser

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


All Replies
Super User
Super User
Posts: 9,599

Re: Date time stamp conversion

Posted in reply to Allistair

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.

Contributor
Posts: 42

Re: Date time stamp conversion

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:MMSmiley FrustratedS: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;

Super User
Super User
Posts: 9,599

Re: Date time stamp conversion

[ Edited ]
Posted in reply to Allistair

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...

Contributor
Posts: 42

Re: Date time stamp conversion

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.

Super User
Super User
Posts: 9,599

Re: Date time stamp conversion

Posted in reply to Allistair

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.

Contributor
Posts: 42

Re: Date time stamp conversion

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.

Super User
Posts: 10,280

Re: Date time stamp conversion

Posted in reply to Allistair

@Allistair 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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 42

Re: Date time stamp conversion

Posted in reply to KurtBremser

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

 

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

 

Super User
Posts: 10,280

Re: Date time stamp conversion

Posted in reply to Allistair

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,583

Re: Date time stamp conversion

Posted in reply to Allistair

@Allistair 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:MMSmiley FrustratedS: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

Contributor
Posts: 42

Re: Date time stamp conversion

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.

Super User
Posts: 13,583

Re: Date time stamp conversion

Posted in reply to Allistair

@Allistair 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.

 

Contributor
Posts: 42

Re: Date time stamp conversion

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



Super User
Posts: 10,280

Re: Date time stamp conversion

Posted in reply to Allistair

How about posting an example for your dataset work.x10_06_18_sla??

And in a usable form, please.

We're all still fishing in the dark.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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