Desktop productivity for business analysts and programmers

Creating a User Defined Date/Time Format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Creating a User Defined Date/Time Format

I’m a semi-technical new SAS EG (5.1) user with practically no programming experience trying to create a user defined date/time format.  What I need is to create a layout that reflects m/d/yyyy h:m without a AM/PM designation.  (Example: 7/2/2012 17:32)

I’ve looked through the Source Data Informat s under the Date/Time formats and the closest I could find was MDYAMPMw.d.  If someone could just point me in the right direction it would be greatly appreciated.


Accepted Solutions
Solution
‎10-31-2012 12:53 PM
PROC Star
Posts: 7,433

Re: Creating a User Defined Date/Time Format

The code shown on that page, namely:

proc format;

  picture MyMSdt other='%0d-%0m-%0Y %0H:%0M:%0S' (datatype=datetime);

run;

is how one creates a user defined format.  EG might provide a GUI that walks one through the process but, even if it does, an essential part would be specifying what you want the format to do.  The above code does that.  The only things you would have to change are switching the positions or day (%0d" and month (%0m), and changing the hyphens to forward slashes (i.e., from - to /)

View solution in original post


All Replies
Super User
Posts: 19,098

Re: Creating a User Defined Date/Time Format

What's wrong with datetime20?

Occasional Contributor
Posts: 9

Re: Creating a User Defined Date/Time Format

I’m starting this project with importing an Excel file.  IfI use a datetime 18 format when I import it places the month abbreviation in the output. 7FEB2012…..  If I use datetime 20 the output is blank.

PROC Star
Posts: 7,433

Re: Creating a User Defined Date/Time Format

Is it a date field or a datetime field?  Not that datetime20. will provide the exact format you specified but, if it is a datetime field, the result shouldn't be blank.

Occasional Contributor
Posts: 9

Re: Creating a User Defined Date/Time Format

The Type in the import wizard says Date/Time.

Super User
Posts: 19,098

Re: Creating a User Defined Date/Time Format

I think what you may need to do is import it as datetime18 because that's how it is in the excel file, but then display with datetime20. to get what you want to see.

Informat vs format....

Good luck with EG. I find it takes you so far and then you have to go to code. It is a shiny click and point interface and works great if you're accessing clean data sources with very simple requirements.

Occasional Contributor
Posts: 9

Re: Creating a User Defined Date/Time Format

I need to format the date fields either on import or export. The format inside EG it doesn’t really impact what I’ve created so far. 

After I import the data I do a lot of filter/append/link/and queries but eventually I have to export it in a tab delimited file format and it is imported into an old Access database (don’t ask).  That is actually where it blows up if it isn’t in the right format. 

Sad thing is that the filter/append/link/query part saves one of our staff two weeks of manual processing.  All of that processing takes 5 minutes in EG.  The only thing hanging this process up is this date time format issue. 

I think I'll need the luck.  Our organization has neither clean data sources nor simple requirements/processes.  I guess I’ll have to break down and learn some SAS coding.  My head is already hurting at the thought.

Super User
Posts: 19,098

Re: Creating a User Defined Date/Time Format

Do you have SAS/ACCESS or SAS/Connect licensed? Then you can connect straight to your db and append/insert into your tables directly which may help with the intermediary step.

Occasional Contributor
Posts: 9

Re: Creating a User Defined Date/Time Format

Thanks for the help guys.  You were both right.

Arthur,

I just re-read what you wrote and a section about Picture Formats and get it now.  No wizards for a user defined format.  Ran a modified version of the little program based on what you posted.  I selected the name of my format under user defined had a custom date / time.    Works fine.

Reeza,

You were right about the datetime18.  There is no need to change formats.  I was following the end user's instructions on how they accomplish their manual process and mimicked their format change.  It was an unnecessary step.  Excel, SAS, and Access all spoke the same date time language if you just left everything alone. 

Now I know how to program for a custom format and don’t need it.  My brain hurts.

PROC Star
Posts: 7,433

Re: Creating a User Defined Date/Time Format

Occasional Contributor
Posts: 9

Re: Creating a User Defined Date/Time Format

I saw that code on the community site however I wanted to learn to create the user defined formats instead of coding for it. First I'm just beyond the web tutorial stage of using EG so when I see code my brain still locks up.  Second, the organization I work for has a lot of legacy processes with a plethora of goofy formats so I'd like to set each up and click on the drop down when I need them.

Solution
‎10-31-2012 12:53 PM
PROC Star
Posts: 7,433

Re: Creating a User Defined Date/Time Format

The code shown on that page, namely:

proc format;

  picture MyMSdt other='%0d-%0m-%0Y %0H:%0M:%0S' (datatype=datetime);

run;

is how one creates a user defined format.  EG might provide a GUI that walks one through the process but, even if it does, an essential part would be specifying what you want the format to do.  The above code does that.  The only things you would have to change are switching the positions or day (%0d" and month (%0m), and changing the hyphens to forward slashes (i.e., from - to /)

Occasional Contributor
Posts: 9

Re: Creating a User Defined Date/Time Format

I appreciate the input and that you can use SAS code with within EG to get things done ( and more efficiently) but I’m here for the shiny GUI with the point-and-click, menu and wizard driven interface. Smiley Happy

Unfortunately what I’ve seen so far is that SAS doesn’t do a very good job in demonstrating how to use EG in that mode. 

PROC Star
Posts: 1,146

Re: Creating a User Defined Date/Time Format

This looks like one of the functions that they haven't yet added to Enterprise Guide. (You can only create "look-up" style formats). There is a custom task available to extend EG for this, but that's probably a bit beyond you at this point.

My suggestion is follow Art's advice using SAS code. To do this:

-   In Enterprise Guide, select File | New | Program

This will open a code editor window. You can just copy and paste the three lines from Art's post right into the window. Make sure you take the line that says "run;". That's really important in SAS.

-  Select Run (beside the green arrow)

This will execute your code, and in this case create a custom format named MyMSdt. You can then use that anywhere in Enterprise Guide.

One important note is that since this code doesn't specify where to store the format, it will be put into your WORK library, which means it will be deleted at the end of your Enterprise Guide session. So you'll have to recreate it next time. If you've learned about permanent libraries, you can store the format in one by changing your "proc format" line to something like "proc format lib=sasuser;" where this will store it in my permanent SASUSER library.

Good luck!

  Tom

Occasional Contributor
Posts: 9

Re: Creating a User Defined Date/Time Format

Thanks for the expanded explanation.  It helps too.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 1564 views
  • 6 likes
  • 4 in conversation