topic Re: Date time stamp conversion in SAS Programming

Date time stamp conversion

EMC9 — Tue, 12 Jun 2018 11:16:03 GMT

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

Re: Date time stamp conversion

RW9 — Tue, 12 Jun 2018 12:20:19 GMT

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.

Re: Date time stamp conversion

EMC9 — Tue, 12 Jun 2018 12:24:23 GMT

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;

Re: Date time stamp conversion

RW9 — Tue, 12 Jun 2018 12:37:59 GMT

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#n03vr57gdozvean1klfaaa14c9c0.htm

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#p0z62k899n6a7wn1r5in6q5253v1.htm

Re: Date time stamp conversion

EMC9 — Tue, 12 Jun 2018 12:43:18 GMT

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.

Re: Date time stamp conversion

RW9 — Tue, 12 Jun 2018 12:53:37 GMT

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.

Re: Date time stamp conversion

EMC9 — Tue, 12 Jun 2018 13:13:40 GMT

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.

Re: Date time stamp conversion

Kurt_Bremser — Tue, 12 Jun 2018 13:30:46 GMT

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

Re: Date time stamp conversion

EMC9 — Tue, 12 Jun 2018 13:34:11 GMT

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

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

Re: Date time stamp conversion

Kurt_Bremser — Tue, 12 Jun 2018 13:50:08 GMT

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.

Re: Date time stamp conversion

ballardw — Tue, 12 Jun 2018 14:24:34 GMT

@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

Re: Date time stamp conversion

EMC9 — Tue, 12 Jun 2018 16:26:34 GMT

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.

Re: Date time stamp conversion

ballardw — Tue, 12 Jun 2018 17:06:31 GMT

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

Re: Date time stamp conversion

EMC9 — Wed, 13 Jun 2018 09:28:36 GMT

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

Re: Date time stamp conversion

Kurt_Bremser — Wed, 13 Jun 2018 11:02:33 GMT

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.

Re: Date time stamp conversion

EMC9 — Wed, 13 Jun 2018 13:10:47 GMT

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;