Dealing with Dates in SAS Visual Analytics Designer

5

In a previous blogVisual Analytics audit data collection, I discussed SAS date and time values, and how date and time formats can be used to your advantage in SAS Visual Analytics. That blog addressed some of the features provided for handling date information, like date formats, date hierarchies, and calculated data items based on time intervals.  In this blog, I’ll continue this discussion and share some tips and techniques for working with dates.

Problem: How do I calculate passage of time?

Example: Out of concern over the time taken for delivery of a certain product, we’d like to take a look at the time, from order to delivery of the product, for each order across several years.  The table in memory has data items Product ID, Order ID, Date Ordered, Date Delivered.

Date in Visual Analytics Designer01

Create a new calculated data item, Days from Order to Delivery, calculated as shown:

Date in Visual Analytics Designer02

(1 is added because  we count the order date as a day as well)

Date in Visual Analytics Designer03

Problem: Why does my year have a decimal?

Example: It’s often nice to have a year data item, in addition to the entire date.    The Year operator extracts the year from the date value.|

Date in Visual Analytics Designer04

But then the year displays as shown below, with the comma and decimal! That’s because the new calculated value is a measure and has a default format of COMMA with a width of 12 and 2 decimal places.

Date in Visual Analytics Designer05

To fix this, simply change the format for the year of date data item to Numeric with a width of 12(or less) and no decimals.  Now the year of date values display as below.

Date in Visual Analytics Designer06

And remember, as long as you have a date data item with an associated format that displays year, you don’t need an additional year data item in order to create a hierarchy. Visual Analytics Designer can create the appropriate hierarchy automatically that includes the year.

And if you want to be able to display just the year value, you can duplicate your date data item, and change the format to Year.

Date in Visual Analytics Designer07

Problem: Ugh!  Someone stored these dates as character values!

If dates are stored as character values, that means that when loaded to memory, the date column becomes a category data item with a $ format. With this treatment, you’re unable to take advantage of any of the nice data formats or the date hierarchy features. Plus, when the values are sorted as below, in ascending or descending order, they’re sorted according to character sorting rules, rather than dates.

Date in Visual Analytics Designer08

It’s easy to create a calculated value (we’ll call it RealDate) using the Parse operator, which interprets a string according to a specified informat.

Date in Visual Analytics Designer09

When you click on the format field, you get this selection, and the DATE format with a column width of 9 fits the need here.

Date in Visual Analytics Designer10

Now RealDate displays and sorts as a date should, and since it is now a calculated date value, you can use the different date formats, create date hierarchies, or use the data item with periodic operators.

Date in Visual Analytics Designer11

Problem: The opposite problem!  My date is stored as a date value, so internally, it’s a number, but I want to display the date value as part of a text string!

Text expanded date info below, is a calculated data item, whose expression uses the Concatenation and Format operators.

Date in Visual Analytics Designer12

Both the visual and text forms are shown below:

Date in Visual Analytics Designer13

Problem: How do I create a new calculated date or datetime data item whose values are a certain number of days from the value of another date or datetime data item.

Remember that a date value is stored internally as the number of days since Jan 1, 1960, and a datetime value is the number of seconds since Jan 1, 1960. So this is just a problem of showing passage of time in either days or seconds.

To create a calculated date data item that is X number of days later than the value of date1, create a formula as below, this example calculating a date 21 days later.

Date in Visual Analytics Designer14

To create a calculated datetime date item that is X number of days later than the value of date1, create a formula as below, this example again calculating a date 21 days later. Note that we are multiplying 21 times the number of seconds in a day (60*60*24).

Date in Visual Analytics Designer15

Problem: My date is stored as a number, but the numbers look like this!  YYYYMM (201603, for example)

There are operators available for creating a calculated value that will enable you to overcome most problems of incoming dates being stored in strange ways.

Here is a nice example that was posted in the Global Visual Analytics list for solving this particular problem by using the Mod and Floor operators. Since there was no day information stored with the original numeric date, the day was arbitrarily chosen to be Day 1 of the month:

Date in Visual Analytics Designer16

That's probably enough about ‘dealing with dates’ for one blog. Hopefully, this information has provided some ideas for solving some of the date problems/issues that you might encounter in your work.

Share

About Author

Bobbie Wagoner

Principal Technical Training Consultant

Bobbie Wagoner is a Principal Technical Training Consultant in the Global Enablement and Learning (GEL) Team within SAS R&D's Global Technical Enablement Division. She has developed training on many SAS products for both customers and SAS technical employees, but has most recently focused on SAS Visual Analytics administration and data preparation. When not working, Bobbie enjoys Atlanta Braves games and entertaining her Australian Terrier, Teddy.

5 Comments

    • This is very informative for the subject.

      I need to know if there is a spcific format that can write out to a file the date format as shown below... please take note of the case.

      30Apr2016:00:00:00

      • Bobbie Wagoner
        Bobbie Wagoner on

        I am glad that you specifically pointed out your interest in mixed case or I likely wouldn't have noticed. You probably already know that the datetime format will print our value in exactly this format, with the exception of the mixed case month. All of the datetime formats that display a month abbreviation use upper case, rather than mixed case.

        • Bobbie Wagoner
          Bobbie Wagoner on

          I should also add that in the Visual Analytics Designer, the datetime format becomes Date, Time in the format list, giving you a choice of these examples:
          September 13, 2010 06:58:43 AM
          Sep 13, 2010 06:58 AM
          Monday, September 13, 2010 06:58:43 AM
          So in Visual Analytics, you can't get exactly what you requested above, even though in BASE SAS, you can get your display, with the exception of case.

  1. This example was great for me to convert GMT to EST by subtracting the datetime of a timestamp by 4*60*60 (the number of seconds in 4 hours). This was needed for my admin audit reports. Thanks for sharing!

Leave A Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top