Date parameters in SAS Visual Analytics
Date data elements can be used in control objects such as Drop-Down Lists in Visual Analytics Designer, and it works just fine if all you want is to define interactions. But what if the date you select is not the only month you are interested in? For example, what if the selected date is a reference month, and what you really want in the report is the last three months starting on the selected month? This is not a direct filter, and therefore simple interactions won’t work. Your solution requires using parameters.
Parameters provide a powerful way to give report consumers control to modify and interact with reports. Parameters were first introduced in SAS Visual Analytics 7.1 and are like variables that hold a single value. Those values can be set via control objects that are added to the report, and then referenced by filters, calculations, rankings, and display rules. What used to be a fixed value defined at design time by a report designer, can now be modified by the report consumer for better analysis. An initial value for a parameter can be set at design time but it can also be passed to a report as part of a report URL for added flexibility. If you want to know more about parameters, check out the SAS Visual Analytics 7.1 User’s Guide. Documentation is also available for subsequent versions, such as 7.2 and 7.3, the most current release.
If you have already used parameters when designing reports in SAS Visual Analytics, you know how powerful they are, but finding a proper control that allows dates to be entered and assigned to parameters becomes a challenge. This is why:
- Not all controls accept parameters
- Majority of controls accept only categories (characters and dates)
- Parameters accept only character and numeric types, no dates
The solution proposed here is to transform the date item into a character item to populate the control and transform the selected value stored in the parameter back into a date to be used in the report.
Note: even though we will be using date elements in this example, the solution works for datetime and time elements as well.
Solution Explained:
There are only two types of parameters supported in SAS Visual Analytics: numeric and character. Therefore, a parameter can only be associated with controls that take either one of them.
There are five types of controls in Visual Analytics:
- Drop-Down List
- List
- Button Bar
- Text Input
- Slider
Lists don’t allow parameters, so we can eliminate them for the purpose of using dates on control objects with parameters.
You can make a single value slider to accept parameters, but sliders are not good for this particular problem. Sliders require a measure (numeric value) or a date as input and even though we can use the TreatAs() function to transform dates into numeric info, a date displayed as a number is not user friendly.
Drop-Down Lists, like Button Bars and text Input objects, accept only categories (dates and characters). They could potentially be good candidates for this task.
Looking closer, we soon realize that Button Bars are not recommended for high cardinality data, and the number of distinct dates can grow really quickly.
Text Input objects may be the best option for high cardinality data, but they require additional validation of the values typed and may impose extra steps. It’s true that you can assign a date element in its category role, which would validate the input text as it is typed, but due to the multi format nature of dates, having to type the exact date format may become a little cumbersome for the report consumer. The category role for Text Input objects is optional, and leaving it unassigned may offer some advantages that we will discuss at the end.
If you want to provide a list of values to the end user, the best option becomes a Drop-Down List.
Because the supported types for parameters are numeric and character, and Drop-Down Lists only accept dates or characters as input, this leaves us with only one option: characters.
The first thing you need to do is transform your date element into a character. This can be easily accomplished by using the Format() function. The Format() function takes two parameters: the value to be formatted and the format. There are multiple options for dates, but if you want to have dates displayed in chronological order, you will want to choose a format that displays year first, followed by month and then day. In our example, we are only interested in months, so we could have selected the format YYMM7, which displays a date such as October 17, 2013 as 2013M10. This is not required, but if you want to make it easier to read, you could replace the letter M with – (dash), so that 2013M10 would become 2013-10. In this example, we are taking a date column called Transaction Date and creating a calculated item of type character called YYYY-MM that looks like this:
If you define a parameter of type character, let’s call it Selected YYYY-MM, and associate it to the Drop-Down List, as well as the previous calculated item YYYY-MM this is how the Drop-Down List role tab would look:
At this point, your Drop-Down List is populated with the month values. When you select one of those values, a character formatted as YYYY-MM will be assigned to your parameter.
The next step is to take the character value stored in the parameter and convert it back to a date element. This can be accomplished by using the function Parse(). Function Parse() takes two input parameters: the text string to be parsed and the informat to be used for parsing. Again, you will find a number of informats that could be used, but I’ve found ANYDTDTE9. extremely flexible for this purpose. This informat can actually be used with a variety of date and datetime input values. Supposing the calculated item to hold the transformed date value is called Selected Month Year, its formula would look like this:
Considering that the parameter Selected YYYY-MM only contains information for year and month, the output of the expression above will assign day = 1 by default. In our example, if the selected month is 2013-10, the calculated item would contain the date representing October 1st, 2013. If you are going to use this selected month to further filter the data, you may need to account for this fact, but this is a topic for another Tips & Tricks.
Note: both Parse() and Format() functions are found under the Text (simple) group of operators in the expression window. The Replace() function is under the Text (advanced) group.
The figure below summarizes the flow of the information across calculated items and report objects:
- Date column Transaction Date is used to create a character calculated item with the date formatted as YYYY-MM.
- Calculated item is called YYYY-MM.
- Calculated item YYYY-MM is used to populate the Drop-Down List category role. Parameter Selected YYYY-MM is also associated with this control object.
- Selected drop-down value is assigned to the parameter.
- Parameter is used in another calculated item to parse the character value back into a date.
- This calculated item is called Selected Month Year.
- Selected Month Year, formatted as MMMYYYY, is consumed on a filter, visualization, etc.
As I’ve mentioned before, if you choose to use a Text Input control instead of a Drop-Down List, I’d recommend only assigning a character parameter to its role and nothing else:
By doing that, you would have the flexibility to type in 2013M10, 2013-10, 2013/10, 10-2013, 10/2013, etc. and even full dates like 12oct2013, 10/12/2013, etc., which would make it much easier for report consumers. All of those different input formats are allowed because of the flexibility of the informat ANYDTDTE9. used to parse the parameter.
Hi Renato,
Thank you for a quick response!
Yes, we're using the version 7.2. Yes, I agree that 7.4, at least on the papre, provides the user with much more options to capture the current data selections. Still, 7.4 does not support the slides with multiple values which means we still won't be able to retrieve the date range selections, which are widely used in my organization.
But the funny thing is that even in my older version SAS VA does capture all these filters as a text as seen on the screen below. Is there a reason for not leting the report developers to have access to that information? It would make our lives so much easier!
Best Regards,
Ian
