BookmarkSubscribeRSS Feed
juliamorgan
Fluorite | Level 6

In SAS EG I am attempting to convert datetime() to a string in a particular format.

 

I know how to convert to a string -

put(datepart(datetime()),***)

 

I would like it to be formatted

 

ddd, dd mmm yyyy, hh:mm AM/PM     (how I would be formattted in Excel)

 

Wed, 23 Nov 2016, 09:30 AM

 

What do i use in *** to get it to display like this?

 

(I am not wanting to use a SAS Date - I will never want this field for any calculations).

 

8 REPLIES 8
LinusH
Tourmaline | Level 20
I don't know what exactly happen when exporting to Excel.
But you are asking for some sort of timestamp, so you shouldn't use datepart.
I suggest that you explore the different datetime formats and start from there.
Data never sleeps
juliamorgan
Fluorite | Level 6

It has nothing to do with Excel or exporting - I was using it as an example of how I would format in Excel to show the format that I wanted (30 seconds to do a custom date format in Excel).

 

I have spent around a good couple of hours going through the various formats - there is nothing there that will do this. And then several more hours trying to work out how to convert to a string in the required format.

 

 

I am creating my queries / tables in EG, then using those tables in VA.

 

VA will ignore any formats applied to dates in the tables created in EG and does not have the formatting that I require.

 

Therefore, the only way that I can see to do this, is to create, from datetime() a formatted string in EG.

 

 

If I want a timestamp (which is exactly what I want) what should I use instead?

 

Thanks

 

 

Shmuel
Garnet | Level 18

Create your own format by:

 

proc format lib=work;

     picture myfmt

         other = "%a, %0d %b %Y, %h:%m %p" (datatype=datetime);

run;

/* test */

data _NULL_;

       dt = datetime();

      put dt= myfmt30.;

run;

Reeza
Super User

Well, as noted there isn't a standard format so you can create your own using a proc format and picture statement.

 

@Shmuel code provides the correct format you requested. 

You can get a quick little overview here, if desired:

http://www2.sas.com/proceedings/forum2008/168-2008.pdf

 

If you run the proc format in EG, you can then apply it with the PUT statement. 

 

 

juliamorgan
Fluorite | Level 6

Thanks everyone, creating a format would be almost there but the issue with this is that the format will be just for me - adds complications when it comes to getting this loaded into the production environment. Guess I'll just have to admit defeat and use a format that I don't want.

Shmuel
Garnet | Level 18

It is easy to make the format available to any one in the company, who uses same sas server or instalation.

You just need change the library from

     proc format lib=work;

to a common library, what ever its name.

The other benefit will be that you run that proc format once only, no need to atach it to your program.

 

juliamorgan
Fluorite | Level 6

Thanks - however I don't have permission to use any library in UAT or production (only development). Someone else said that it was hard to get theirs added due to internal procedures etc - so it really isn't worth the fight - thought it would be a two minute job - an entire day later - the available formats will have to be used.

Reeza
Super User

You can use string functions with the standard formats if you really want this. 

 

Otheriwse if you have flexibility using a standard format is definitely easier. Note that when this is char you can aggregate or expect it to sort correctly at all. Picking something that sorts properly may not be a bad idea. 

 

 

Functions 

DATEPART, TIMEPART to convert or components

Day, Month, Year () respectively extract the  names components. 

 

Formats:

DOWNAME3 -> Mon, Tue, Wed, etc

 

wordatex. -> 5 March 2016 (if you want the 3 char month try specifying a length of 11. Not sure if it will work)

 

MONNAME3 -> 3 character month, Jan, Feb, Mar, etc.

 

For the time, TIMEAMPM7.

 

You can use CATX() to combine components. 

 

All the ingredients are there, some assembly required 😄 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1580 views
  • 0 likes
  • 4 in conversation