BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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

15 REPLIES 15
sandyming
Calcite | Level 5

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

SASPhile
Quartz | Level 8

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

The above string doesnt seem to work

Reeza
Super User

24621 - Create custom datetime format using PROC FORMAT with PICTURE

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

advoss
Quartz | Level 8

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',' '));

Tom
Super User Tom
Super User

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;

advoss
Quartz | Level 8

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

snoopy369
Barite | Level 11

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;

Tom
Super User Tom
Super User

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

advoss
Quartz | Level 8

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

snoopy369
Barite | Level 11

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

Amir
PROC Star

Hi,

Is the following what you want?:

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

Regards,

Amir.

OS2Rules
Obsidian | Level 7


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.

SASuserAtPharma
Calcite | Level 5

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';

rfortin
Calcite | Level 5

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

 

 

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
  • 22411 views
  • 1 like
  • 10 in conversation