11-22-2016 04:18 PM
In SAS EG I am attempting to convert datetime() to a string in a particular format.
I know how to convert to a string -
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).
11-22-2016 04:22 PM
11-22-2016 04:28 PM
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?
11-22-2016 05:04 PM
Create your own format by:
proc format lib=work;
other = "%a, %0d %b %Y, %h:%m %p" (datatype=datetime);
/* test */
dt = datetime();
put dt= myfmt30.;
11-22-2016 05:21 PM
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:
If you run the proc format in EG, you can then apply it with the PUT statement.
11-22-2016 07:19 PM - edited 11-22-2016 07:22 PM
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.
11-22-2016 08:54 PM
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.
11-22-2016 08:58 PM
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.
11-23-2016 12:15 AM
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.
DATEPART, TIMEPART to convert or components
Day, Month, Year () respectively extract the names components.
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