BookmarkSubscribeRSS Feed

Date parameters in SAS Visual Analytics

Started ‎11-09-2015 by
Modified ‎11-20-2015 by
Views 14,355

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.

 

Data types supported in Drop-Down List roles

 

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:

 

Expression to transform dates into formatted character items

 

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:

 

Drop-Down List roles assignment

 

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:

 

Expression to transform the character parameter into a date

 

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.

 

Groups of operatos where functions can be found

 

The figure below summarizes the flow of the information across calculated items and report objects:

  original.png

  

  1. Date column Transaction Date is used to create a character calculated item with the date formatted as YYYY-MM.
  2. Calculated item is called YYYY-MM.
  3. 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.
  4. Selected drop-down value is assigned to the parameter.
  5. Parameter is used in another calculated item to parse the character value back into a date.
  6. This calculated item is called Selected Month Year.
  7. 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: 

 

Text Input control roles assignment

 

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.

Comments

This is essentially what I was looking for. Thanks a lot for this post. It is very well explained.

Thanks for your feedback. I'm glad to hear you've found it useful!


Hi

 

 

 

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!

 

 

ReportObjectFilters.JPG

 

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. 

The way you have described the chart (revenue in X axis and profit in Y axis) you could use a scatter plot with quarter in the color role or in the lattice role:
[cid:image001.png@01D6232E.450CE8B0]

Technically you could also use a numeric series, but I don't think it would fit in this use case.
Or you could try something different, as this parallel coordinates chart:
[cid:image002.png@01D6232F.29851170]
Version history
Last update:
‎11-20-2015 02:23 PM
Updated by:
Contributors

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started