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).
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
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;
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.
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.
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.
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.
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 😄
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.