Convert SAS Date character and format it

[juliamorgan]

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

[LinusH]

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.

[juliamorgan]

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]

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]

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]

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]

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]

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]

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 😄