Making Best Use of Date Formats in Visual Analytics Designer

You may have noticed that when using date information in SAS Visual Analytics, that the date data values can be displayed in a variety of ways. You may see your dates displayed like Jan1916, 03Jun1915, or 03/12/16, for example. In this blog I’ll help you understand SAS date and time values, and review some of the features of SAS Visual Analytics relating to date and time values.

To help illustrate some of the date features in Visual Analytics Designer, I’ve created a SAS table containing multiple date columns, each containing the same data values, but having different associated formats, along with a datetime column and a time column with associated formats. For analysis purposes, there is also an amount column.

The following figure shows how the data is stored and how the data values would be represented in a simple printed report, for example.

How date and time formats are used in designer

When the table is loaded to memory and added to a designer report as a data source, the columns with date, datetime, and time formats are interpreted as date, datetime and time category data items, as shown in the next figure. Notice that the date column with no format, however, is interpreted as a numeric measure data item.  The formats in designer are as shown below.

This is a list table showing how the values display with the associated formats.

So what are those numbers in the date column all about?

The numbers in the date column represent a SAS data value with no format associated.  This means it is simply a numeric value representing ‘number of days’.  Date values are stored internally as numbers in order to enable calculations on dates, such as the number of days between two dates.
A SAS date value is a numeric value representing the number of days between January 1, 1960 and a specified date.

A SAS datetime value represents the number of seconds between January 1, 1960 and a specified date and time.

A SAS time value represents the number of seconds since midnight of the current day. These values are between 0 and 86400.

For SAS tables, however, date values can be displayed using a variety of different formats (almost 100!)  There are numerous formats available for displaying datetime and time values also.  You are able to take advantage of many of these formats in SAS Visual Analytics—because, after all, who really wants to sit around counting the number of days in order to interpret date values in a report?

Changing the date and time formats in designer

When changing the format for any of the date data items, you can choose from this list:

Changing the format for the datetime data item displays this list:

A data item with time information stored can use these formats:

Another problem to consider:  Suppose your date information was loaded to memory without an associated date format at all, in the case of the date column in the data above.  This is column is interpreted in designer as a numeric data value.  You can solve this problem easily by creating a new calculated data item using the TreatAs Numeric(advanced) operator.  The operator enables you to specify a calculation that treats a value as a different data type.  In this case, the expression below specifies that the date data item should be treated as a date value.  The values of the new date(converted) data item are shown below.  You can also then change the format of the new date(converted) data item if you wish.

Date hierarchies in designer

In designer you can create a default date hierarchy from any date or datetime data item whose format displays a year by simply right-clicking on the data item and selecting the Create Date Hierarchy option.  A date hierarchy consists of year, quarter, month, day.  To create a time hierarchy right-click on a time data item and select Create Time Hierarchy. The time data item results in a hierarchy of hour, minute, second.

These hierarchies are the default hierarchies created for the date1, date3, and time, and datetime data items from the example data above.

Note:  You may have noticed that, in explorer, for a datetime item, you have the option of creating a Date and Time Hierarchy for that data item, consisting of Year, Quarter, Month, Day, Hour, Minute, Second.  The Date and Time Hierarchy is not available in the designer. So the default hierarchy for the datetime data item above consists of only Year, Quarter, Month, Day—the same as for the date1 and date3 hierarchies.

If the format associated with a date only displays Month, (the MONTH. Format associated with date2, for example), then in designer, you won’t be able to create a hierarchy from the data item until you change the format to one that displays year also.   If a hierarchy is not supported for a data item, then the Create Hierarchy option is not displayed when you right-click on the data item.

Derived data items for measure based on data or time intervals

Having data items whose format displays a year value also enables you to create derived items for reports that are based on time intervals. When you click on one of the intervals below, a selection list of data items display.  The only data items that are supported for the intervals are those that contain a year—so that’s data1, data3, and datetime in this data.

The formulas for these derived items use periodic operators that aggregate values over time: RelativePeriod, ParallelPeriod, and CumulativePeriod.

The special derived data items feature creates the formulas for these aggregations for you, but the same Periodic operators are also available for creating your own New Aggregated Measures:

Some of these derived columns are illustrated here using data that contains a Date by Month data item and an Expenses data item.  The data spans 2010-2011.

You can edit each of the derived data items to view the calculation and to make changes, if necessary.

The calculation for Expenses(Difference from Previous Parallel Period) is shown below, as an example.

_Sum_ is the aggregation that is applied to the measure, Expenses.

The data item for the periodic calculation is Date by Month.

The inner interval (smaller time period) for the aggregation is _Inferred_ , which means it has been determined from the report object. You could change to specific values of _ByMonth_ , _ByQuarter_ , or _ByYear_ .

The outer interval (larger time period) is also _Inferred_ , with other possible values being _ByMonth_ , _ByQuarter_ , or _ByYear_ .

The number of outer intervals to offset from the current period is 0. This means that the period from the current outer interval (Year) is used.

The scope for the period is _Full_ , meaning that the values are aggregated for the entire period. Alternate values are _ToDate_ (up to a specific day in the current period), or _ToToday_ (aggregates only up to the equivalent of today’s position in the current interval).  If you change the scope to _ToDate_ , you would need to specify a specific date in the last field.

So considering the above descriptions, for the list table, the expression below would produce the same resulting values for Expenses(Difference from Previous Parallel Period).

Periodic operators can return missing values under certain scenarios.  These scenarios, along with complete details on all of the date operators are documented Appendix 5 of the SAS Visual Analyics 7.3: User’s Guide.

Hopefully, this blog provides you with a better understanding of the basics of dates and times, and enable you to use some of the great features for dates in SAS Visual Analytics.

Making Best Use of Date Formats in Visual Analytics Designer was published on SAS Users.