BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Heretic
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

18 REPLIES 18
Reeza
Super User

What's wrong with datetime20?

Heretic
Calcite | Level 5

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.

art297
Opal | Level 21

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.

Heretic
Calcite | Level 5

The Type in the import wizard says Date/Time.

Reeza
Super User

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.

Heretic
Calcite | Level 5

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.

Reeza
Super User

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.

Heretic
Calcite | Level 5

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.

Heretic
Calcite | Level 5

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.

art297
Opal | Level 21

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

Heretic
Calcite | Level 5

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. 

TomKari
Onyx | Level 15

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

Heretic
Calcite | Level 5

Thanks for the expanded explanation.  It helps too.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 4960 views
  • 7 likes
  • 4 in conversation