Working with dates in SAS Visual Analytics: Comparing to the previous year


After posting a couple of blogs on the subject of dates and date formats in Visual Analytics Designer, I got a question from a user who wondered how to compare data for a selected date to data from the same day of the previous year. Here’s one way to do this.

The example report enables a user to type in a date value in a variety of formats and displays the sale amount for the specified date, along with the sale amount for the same day of the previous year.

Working with dates in SAS Visual Analytics

The data source includes information on thousands of orders. Irrelevant data items have been hidden, with the items of interest shown below. Transaction Date has an associated MMDDYYYY format and Transaction Weekday is simply a duplicate of the date with an associated Day of Week format.

Working with dates in SAS Visual Analytics02

A parameter, Param date, is associated with the Parameter role of the Text input field and will store the value typed in the field.

Working with dates in SAS Visual Analytics03

Working with dates in SAS Visual Analytics04

Several calculated data items are created for ‘behind the scenes’ filtering and Ref Date is a data item that will store the date converted from the entered text version of the date.

Working with dates in SAS Visual Analytics05

Note that the ANYDTDTE informat is a great one to use when you are uncertain as to exactly how users will be typing in a date value.
Ref Date (Yr-1) is the ‘same day a year ago’ date.

Working with dates in SAS Visual Analytics06

A filter on the list table completes the report:
( Ref Date = Transaction Date ) OR ( Ref Date (Yr-1) = Transaction Date )
The data items below are now used to populate the report showing the data for the specified date for comparison with the data for the same day of the previous year.

Working with dates in SAS Visual Analytics07

Working with dates in SAS Visual Analytics08

With the addition of a few more calculated data items and filters, some additional report objects can offer alternate ways of displaying the data.
Prod Sale (ref date) and Prod Sale (Yr-1) are calculated as below:

Working with dates in SAS Visual Analytics09

The addition of the two new data items allow the information to be presented in the list table below, with this filter applied:  (Prod Sale (ref date) NotMissing OR Prod Sale (Yr-1) NotMissing)

Working with dates in SAS Visual Analytics10

The same filter (Prod Sale (ref date) NotMissing OR Prod Sale (Yr-1) NotMissing) can be applied to a crosstab object to produce the result below:

Working with dates in SAS Visual Analytics11

The addition of one additional calculated data item, Date, and a new crosstab object with a filter on Date, enables still a different display.

Working with dates in SAS Visual Analytics12

I found this to be an interesting example–both the problem and the solutions. I hope this blog will give you more ideas about using your dates to the best advantage in report.


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.


  1. Rathod Bibishan on

    Actually, I need all the data comparisons in a single crosstab or list table. With the last five years of data, I would like to compare the data year over year and month over month, using the year filter, Please suggest.

  2. Can you make a date slider where the values are dates in MMMYYYY format rather than the number/character format of the parameter? It would be more convenient for my users to use a slider than enter the date, especially since they may make an error and enter an incorrect date. I am using VA 7.3.

    • Bobbie Wagoner on

      You can make a date slider with the date in MMMYYYY format, but your parameter can be only character or numeric, requiring the approach in the blog. If you are simply using the slider for a filter interaction, for example, with no parameter, the MMMYYYY format works fine.

  3. Bobbie Wagoner on

    You can accomplish this with MMYYYY data by creating an aggregated column using a periodic operator. The text from of the expression would be this, if Expenses is the measure and Date is the MMYYYY data item.
    RelativePeriod(_Sum_, 'Expenses'n, 'Date'n, _ByMonth_, -1, _Full_, {Date})
    You cannot accomplish this in Designer at the Day level--only year and month.

    • Bobbie Wagoner on

      Since the periodic operators do not go down to day level, you would need to pre-process you data in order to make daily comparisons.

  4. sunny kumar gupta on

    Hi Bobbie Wagoner,

    Please help me on the same. I really need this solution as anyhow 🙁 . I tried to do it but can not pass the multiple values (Dates) in one single parameter.

  5. sunny kumar gupta on

    I really wanted this logic but this logic applied for only one date comparison.

    Actually i need all date comparison in one cross tab or list table

Leave A Reply

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

Back to Top