BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Data Visualization, Visual Analytics

SAS Visual Analytics: Getting Triple Value from Your Dates

Submitted by on 2013-12-20 – 8:46 AM 5 Comments

You always need ways to save time and effort when doing any task. If you are a long time SAS user then you realize how easy it is to mix a format with a date values to save some effort later.  However, if you are new to SAS Visual Analytics or to the SAS way-of-things, you may not realize the treasure trove available with a few simple clicks and  format.  Let’s explore how I used one variable to create multiple data items.

It is One, But It is Many

If you have one SAS datetime variable in your dataset then you have every other date and time format.  For this website usage report, I wanted to show the date and time several ways. For instance, some charts show the arrival visit by month, others by day of week, and some show the average arrival by hour for each day. Again – all this data with one variable.  

This particular report allows the user to compare the arrival across countries (see the section filter at top).  The pick list on the side allows the users to select the day so they can compare the arrival days by time.  I was inspired by the social media articles I have seen where the author is advising you on the best time to send a tweet.  I like trends – so I used a line chart and then added the country so the user could see how the pattern shifts based on where you are in the world. For instance, in the US little happens between 2 AM and 7 AM probably because everyone is asleep.  [See also: Avoiding an Epic Fail with Your Data Visualization]

sas visual analytics date values

Click to enlarge

I like having the pick list of days so the user can zero in on the days of most interest.  Seven lines displayed at once makes it more difficult to zero in on a particular trend. Notice that the pick list also shows the percentage by day.  I am comparing Friday (the most popular day) to the less popular Sunday. The usage time shifts for these days.  Looks like more users are active during the day on Friday and on Sunday more are active during the evening.

If users clicks on a point, the table filters the values to see exactly what values contributed to that point.  However, my purpose was to show the original date variable on the same page with the other values.  I’m not sure if the table adds that much value other than learning that most users find the website using Google at 1 PM on Fridays.   [See also What I Learned About SAS VA Over the Summer]

Creating Multiple Date Variables

If you are thinking you need to add more date variables to the source dataset – you don’t. All of the date and time variables are based on one variable. Let’s look at this example.  I have a datetime variable called Arrival Date (or Arrival Date (Original) shown below), which contains the day and exact minute the user visited the site.  I want the following data items: Arrival Date, Month, Month (Name), Day of Week, and Arrival Hour.  From this one variable, I can get most of the other data items – save the Month (Name) one. We’ll talk about that one in a moment.

va_many_variables 06

It’s a simple method – right-click the Arrival Date data item and select Duplicate Data Item.  In the following  figure, you can see an example of changing the name and format.

Click the new data item called Arrival Date (1) so you can see it’s Properties in the bottom left pane.  Then you can see 3 examples of what Format values I would have selected for different results.  Notice that some of the Format Types provide extra choices in the drop-down list.  For instance, for the Arrival Hour data item, when you select Time as the format you have multiple ways to show the time.  I selected the “6 AM” so the data aggregates by hour.

va_many_variables 03

When You Just Need the Month Name

In the above method, you see how a single variable can change into multiple value with a format.  However, I also wanted a Month data item but without the year.  This is an example report with data from 2002.  This makes the data seem to old but what really happened was someone messed up the calculation and managed to change 2012 to 2002. <whistles … >   Rather than show the year, I just want a month.  To get a month name, you need a date variable not a datetime variable.  Let’s use our existing variable as the basis for this new calculated item.

After creating a new calculated item, the New Calculated Item window appears. Do the following:

  1. Rename the data item.  I called mine Arrival Month.
  2. Drag the Arrival Date data item to the work area.  This is the datetime variable.
  3. Drag the DatePart operator on the data item.  This will extract the date portion of the value.
  4. At this point, SAS VA complains that there is a type mismatch because it wants a number result not a date value result.
    To address this complaint, just select the Date instead of Numeric as Result type.

sas visual analytics new calculated item

For your new data item, again you just need to change the format.   See how handy these things are? You may recall that when you had the datetime variable as the basis for the format,   Month was not even offered as a choice.  Also notice that the several other formats are available that were not there previously.

Now you can select the way you want your data item to appear and you are done.  Since the date is the basis for the variable – you can ensure a proper sort on your values. Cool huh?

va_many_variables 05


One Caution

If you already have a data item in use and to try the change the format, SAS VA will object with a message like “A data item format cannot be changed.  There are calculated items using the data item” appears.  Oh my … sounds quite ominous.  It is saying that a calculation is using this value in the format that it is shown and changing that format may cause the calculation to break.

To fix this situation, duplicate the data item, change its format and use it in your report.  Obviously you don’t want any existing calculations to be impacted since that would just create more work for you.

With all these quick fixes … you have extra time to drink more coffee – it’s the only way to show your co-workers how effortless everything is for you.  😉

Learn SAS Visual Analytics

When it comes to business intelligence and analytical capabilities, SAS® Visual Analytics is the premier solution for data discovery, visualization, and reporting. Click here to learn more about the book.
The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.
Spread the love

Tags: ,


  • Tricia says:

    Thanks Chara.

    I hope you are not a bot just advertising some services. 🙂

  • Chara Brent says:

    Its like you read my mind! You seem to know a lot about this, like you wrote the book in it or something.
    I think that you can do with some pics to drive the message home a little bit, but other than that, this is great blog.
    A fantastic read. I’ll definitely be back.

  • Jaunita says:

    My brother recommended I might like this web site. He was entirely right.
    This post actually made my day. You can’t imagine simply how much time I had spent for this information!


  • Tricia says:

    I love those SAS formats … it makes you into a magician!

  • Great post on the versatility of SAS date formats… A technique that can be extended to other SAS clients too, creating more time for coffee!