In a previous blog, 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.

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

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

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

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.

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.

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.

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.

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.

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.

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.

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.

Both the visual and text forms are shown below:

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.

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

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:

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.