SAS Visual Analytics: Getting the Most from Your Dates
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]
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.
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.
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:
- Rename the data item. I called mine Arrival Month.
- Drag the Arrival Date data item to the work area. This is the datetime variable.
- Drag the DatePart operator on the data item. This will extract the date portion of the value.
- 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.
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?
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. ;-)
Latest posts by Tricia Aanderud (see all)
- SAS Add-In for MS Office: Abe Lincoln Sends Glee in a Snowstorm - February 18, 2014
- #SASGF14: You’re Invited to a TweetUp! - February 10, 2014
- SAS Stored Process Dealing with Disappearing Results - January 31, 2014
- More SAS Stored Process Sleight of Hand Tricks - January 29, 2014
- Little Known Secrets from SAS Stored Process Magicians - January 26, 2014