DATA Step, Macro, Functions and more

DATETIME22.3 to yyyy-mm-dd time

Reply
Super Contributor
Posts: 647

DATETIME22.3 to yyyy-mm-dd time

How to convert a date column that has value like 21JUN2013:00:00:00.000 to 2013-06-21 00:00:00.000?

Contributor
Posts: 32

Re: DATETIME22.3 to yyyy-mm-dd time

b=put(input(substr(a,1,9),date9.),yymmdd10.)||substr(a,11);

Super Contributor
Posts: 647

Re: DATETIME22.3 to yyyy-mm-dd time

the data type is num and format and informat are datetime22.3

The above string doesnt seem to work

Super User
Posts: 17,771

Re: DATETIME22.3 to yyyy-mm-dd time

24621 - Create custom datetime format using PROC FORMAT with PICTURE

Create your own format, change the / in the code to - I think.

Frequent Contributor
Posts: 91

Re: DATETIME22.3 to yyyy-mm-dd time

PROC FORMAT with PICTURE and DATETIME directive is a good option, but does not seem to provide a method for displaying the decimal portion of the seconds.  Except for Amir's solution, is there a way to get PROC FORMAT to display the decimal portion of the seconds?

An enhancement to Amir's solution (the time. format does not include leading zeroes on the Hour).

char_dt = cat(put(datepart(num_dt),yymmdd10.),' ',translate(put(timepart(num_dt),time12.3),'0',' '));

Super User
Super User
Posts: 6,498

Re: DATETIME22.3 to yyyy-mm-dd time

If you have SAS 9.3 or newer then you can use %s directive for fractional seconds.

proc format;

    picture mydt low-high='%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime);

run;

Frequent Contributor
Posts: 91

Re: DATETIME22.3 to yyyy-mm-dd time

Unless there is a hotfix for SAS 9.3, the %s directive doesn't appear to be available until SAS 9.4.

Regular Contributor
Posts: 244

Re: DATETIME22.3 to yyyy-mm-dd time

This works fine in 9.3 TS1M2 (it doesn't zero pad properly, for some reason, though).  You need the decimal in the used part (and at least in 9.3 it only gives 2 places, not 4).

proc format;

    picture mydt low-high='%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime);

run;

data _test_;

now = datetime()+.05156;

put now= mydt.4;

run;

Super User
Super User
Posts: 6,498

Re: DATETIME22.3 to yyyy-mm-dd time

I am seeing the missing leading zero with SAS 9.3 (TS1M2). I do not have 9.4 to test if it is fixed in that version.

25   data _test_;

26   now = dhms(today(),1,2,3.05156);

27   put now= mydt23.3;

28   put now= mydt19.;

29   run;

now=2013-11-01 01:02: 3.052

now=2013-11-01 01:02: 3

Frequent Contributor
Posts: 91

Re: DATETIME22.3 to yyyy-mm-dd time

Yes, I stand corrected on the SAS 9.3 issue.  lowercase %0s directive works to provide decimals, but does not have leading 0.

Regular Contributor
Posts: 244

Re: DATETIME22.3 to yyyy-mm-dd time

47182 - Adding a leading zero to the %s directive in a PICTURE statement in PROC FORMAT does not ins...

Nope, not fixed (I tested in 9.4).  Surprised it hasn't been yet, it's been 3 months since it was reported...

Super Contributor
Posts: 282

Re: DATETIME22.3 to yyyy-mm-dd time

Hi,

Is the following what you want?:

char_dt=cat(put(datepart(num_dt),yymmdd10.),' ',put(timepart(num_dt),time12.3));

Regards,

Amir.

Super Contributor
Posts: 358

Re: DATETIME22.3 to yyyy-mm-dd time


We use:

proc format;

    picture mydt low-high='%Y-%0m-%0dT%0H:%0M:%0S' (datatype=datetime);

run;

and then job put the value using that format.

New User
Posts: 1

Re: DATETIME22.3 to yyyy-mm-dd time

I had a lab date like this inside this variable "llab_dt_int"

16SEP2015:00:00:00.000

and wanted

2015-09-16T00:00:00

... so similar to yours, I found this worked - where time is 00:00

 

attrib blah format=$25.;

blah=strip(put(datepart(llab_dt_int),yymmdd10.))||'T00:00:00';

Occasional Contributor
Posts: 6

Re: DATETIME22.3 to yyyy-mm-dd time

Thanks everyone for all your suggestions.  With your help, I finally figured this out and have copied the code in the attached word file.  rfortin

 

 

Ask a Question
Discussion stats
  • 15 replies
  • 7578 views
  • 1 like
  • 10 in conversation