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:
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:
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:
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.
This is essentially what I was looking for. Thanks a lot for this post. It is very well explained.
Hi Renato,
Thank you a lot for this article!
I am working cuurently on a new VA project wher the users should be able to investigate the data further and, once they reached the hierarchy level they'd be interested in investigating, they'd like to use the parameters to create a SAS dataset that would include more granular detail which LASR cannot accomodate. Because of the drill-down they already went through, the SAS output data should only include a fraction of rows and thus can be easy to handle for the slice-and-dice.
Unfortunately, the selections they need to have multiple selections, so we do use List boxes and slides with the range values (like minumum and maximum dates). Drop-down menus only allow a single value selection.
Is there ANY way to pass multiple selections made by the user to parameters? Anything a SAS stored process could pick up?
Thank you very much for your help,
Ian
Hi Ian,
Support for date parameters (covered in this article), as well as multi-value parameters was added to VA 7.4. Unfortunately, I'm not aware of any workaround for multi-value parameters in relases prior to 7.4. The main problem is to be able to capture the selected values, so a Stored Process wouldn't be able to help much either.
Best,
Renato
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
Hello !
I found your article very interesting.
However, I miss something : I would like your MM-YYYY dropdown list to filter my data with all the records starting from the "Selected Month Year" parameter.
What I observe is that, changing the value of the dropdown list automatically update my graphs and tables on the current tab to the SELECT Month Year; So it will select for example all the data of 2017-01, only.
Is there a way to "disconnect" the automated filtering of the dropdown list, and have the parameter in a datasource filter where I would have something like "'DateCreatedDMY'n >= 'Selected Month Year'n".
Any idea ?
thanks a lot
Control objects such as dropdowns and others will only automatically filter report objects if the control is added to report or page prompt sections. If you add the control to the report body, and don't define any interaction between this control and other report objects, then you can define an advanced filter expression like the one you said and apply it to the object(s) you want to filter.
Got it, thanks for your answer.
The point is, I have multiple datasource in this report, each tabs having on datasource with its own graphs/objects. So putting the dropdown on the top would feel like it can filter the date of all datasources, which I guess cannot be possible.
I feel I always go to a dead end every time I want to do something on SAS...
but thanks for answering !
You can still use the solution proposed here to filter objects in other tabs (sections/pages) that are associated with different data sources. You only need to create that calculated item that was called Selected Month Year on each data source that you have in your report.
OK but the dropdown list gets populated by a date field coming from ONE of the datasources, before being reformatted to MM-YYYY (step 1 to 3 in your diagram).
So I assume this will work ONLY if the datasource used to populate the dropdown list has the maximum date values (start date and end date), compared to the others, if I want this to work.
Correct?
If your date selector is supposed to filter multiple sources, then it would make sense to me to create a "calendar" table just to feed the selector.
It's worth to mention that the next release (VA 8.3) will offer the ability to manipulate and join tables from the report design interface which will help derive such tables on the fly.
This may not be related to the post but I was wondering if it is possible in SAS VA to create multiple date ranges in one view?
For example: Say I have revenue (x-axis) and profit (y-axis). I would like to create a view that demonstrates revenue across quarters (i.e., multiple date ranges).
I am not entirely sure if this is a time series? I know this is possible in Tableau.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.