topic Re: Convert SAS Date character and format it in SAS Procedures

Convert SAS Date character and format it

juliamorgan — Tue, 22 Nov 2016 21:18:12 GMT

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

Re: Convert SAS Date character and format it

LinusH — Tue, 22 Nov 2016 21:22:01 GMT

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.

Re: Convert SAS Date character and format it

juliamorgan — Tue, 22 Nov 2016 21:28:31 GMT

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

Re: Convert SAS Date character and format it

Shmuel — Tue, 22 Nov 2016 22:04:00 GMT

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;

Re: Convert SAS Date character and format it

Reeza — Tue, 22 Nov 2016 22:21:05 GMT

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.

Re: Convert SAS Date character and format it

juliamorgan — Wed, 23 Nov 2016 00:22:29 GMT

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.

Re: Convert SAS Date character and format it

Shmuel — Wed, 23 Nov 2016 01:54:53 GMT

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.

Re: Convert SAS Date character and format it

juliamorgan — Wed, 23 Nov 2016 01:58:21 GMT

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.

Re: Convert SAS Date character and format it

Reeza — Wed, 23 Nov 2016 05:15:38 GMT

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 😄