DATA Step, Macro, Functions and more

Convert Datetime to ddMONyyyybhh:mm:ss format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Convert Datetime to ddMONyyyybhh:mm:ss format

Hi, I need to convert the current datetime into ddMONyyyybhh:mm:ss format.

 

For example - if the current time is - 11/10/2015 04:36:12

expected result - 10NOV2015 04:36:12

 

Tried the below, but need the blank space between year and hour, Please help me..

test=put(datetime(),datetime19.);

 

 

 


Accepted Solutions
Solution
‎11-12-2015 03:57 PM
Trusted Advisor
Posts: 1,115

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

[ Edited ]

There are various ways to do this. Perhaps one of the simplest is to add the following statement after your correct assigment statement:

substr(test,11,1)=' ';

As you may have noticed, format datetime19. creates a leading blank. To get rid of that, you could do this:

test=left(put(datetime(),datetime19.));
substr(test,10,1)=' ';

Or this:

test=put(datetime(),datetime19.-l);
substr(test,10,1)=' ';

 

View solution in original post


All Replies
Solution
‎11-12-2015 03:57 PM
Trusted Advisor
Posts: 1,115

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

[ Edited ]

There are various ways to do this. Perhaps one of the simplest is to add the following statement after your correct assigment statement:

substr(test,11,1)=' ';

As you may have noticed, format datetime19. creates a leading blank. To get rid of that, you could do this:

test=left(put(datetime(),datetime19.));
substr(test,10,1)=' ';

Or this:

test=put(datetime(),datetime19.-l);
substr(test,10,1)=' ';

 

Occasional Contributor
Posts: 17

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

thank u all for the repsonse. Used the substr and it is working.

 

My understanding is that substr will extract partial text, Could you please explain how it works in this case ?

Trusted Advisor
Posts: 1,115

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

Thank you for liking and accepting my solution.

 

There are two different flavors of the SUBSTR function: If it is used to the right of an equals sign, it returns a substring. Left of the equals sign, however, it replaces character value contents. (See the two separate entries for SUBSTR in the online help for more details.) So, in our case it was used to overwrite the first colon in the datetime string with a blank.

Super Contributor
Posts: 275

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

data _null_;
date="11/10/2015 4:36:12";
_date=input(date,mdyampm19.);
format _date datetime19.;
put _all_;
run;

Valued Guide
Posts: 2,175

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

Create a user defined format with the datetime "directives" (find that word in the documentation of PROC FORMAT)
Super User
Super User
Posts: 7,401

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

Well, you have seen some solutions above, i.e. convert it to text, use an inbuilt format, or create your own.  I would ask why you want to do this, what is the purpose of it?  If you go with the text variety, then sorting becomes an issue, and you lose the ability to do simple maths on the variable.  If you create your own format, then a programmer picking your code up has to look at that format to see what it does (assuming there is no bitness issue on the catalog).  Whereas just using the base SAS dates has none of the above issues?  Now if its for a report your creating you could hybridise, have a character version and a numeric version, and only display the character one, whilst using the numeric for other processing.  Or you could change the report format.  Personally I tend to do both of these.  In the underlying dataset I would have variables for the necessary calculations, processing, general a date value, and a time value, but sometimes a date/time value.  Then there would also be a character version.  The reason is that dates have to be complete dates for them to be valid, i.e. day/mon/yr all present and right, but if you have partial dates you may want to display the partials, but not perform calculations, e.g. date=., time="01:00"t; datec="01-2012"; timec="01:00"; datetimec="2012-01---T01:00:00"

You will see in this example I can't do date transformations directyl as date is missing, so would have imputation rules, but I can still do time calculations, and display the data as it is entered or in numeric format.  More flexibility in your data means less coding overall.

Valued Guide
Posts: 2,175

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

RW9
I have exactly this need:
because so much data is passed to and "managed" in excel
DATETIME. formatted cells remain as strings.
With my userformat XLDATE. these values are automatically recognised by the excel importer.
proc format ;
picture XLDATE (round default= 19 )
other = '%0d%b%0Y %0H:%0M:%0S' (datatype= datetime );
run ;

The only weaknesses with this are:
although I requested ROUND - it does not take effect
and there appears to be no "datetime directive" for decimal places of a second. When these become real issues I just put up with using formulas in excel to derive the timestamp value from a datetime21.2 string.
Occasional Contributor
Posts: 17

Re: Convert Datetime to ddMONyyyybhh:mm:ss format

Thank you all, I used the substr and it is working. My understanding is that substr will extract partial text, Could you pls explain how the subtr works in this case ?
☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 302 views
  • 1 like
  • 5 in conversation