BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lucydog
Fluorite | Level 6

I'm new to SAS VA (using v. 7.5) so I may not be using the right terminology. But I want the person viewing my report to be able to filter data to display from a list of values that overlap (are not exclusive). In my example, I want to have a list of age categories such as: 12+ years, 12-18 years, 18+ years, 18-64 years, 65+ years.

 

So, instead of the drop-down list shown below (with exclusive values), I want to be able to create a list of choices with overlapping values. I have both category and numeric age variables to help me with this task.

 

lucydog_0-1621280033829.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Sam_SAS
SAS Employee

Hello,

 

This should be possible using a parameter and an advanced filter.

 

community3c1.gifcommunity3c.gif

 

For the drop-down list, you need to have a column that contains the selector values such as "15-19 years". If you have access to the source data table, adding these values to the table would be the simplest approach. If you cannot update the source table, you can create the values within VA by creating either a custom category or a calculated item. For example, I did this as a custom category:

 

community3a.gif

It is important to remember that these mappings don't really matter. All we are doing is making a column that has the values "11-14" and "13-16".

 

Now, create a drop-down list object in the body of your page. It must NOT be placed in the page prompt area or report prompt area, because we do not want the control to directly filter anything. The data item that has your selector labels should be assigned to the Category role, and you will create a new character parameter to assign to the Parameter role.

community3b.gif

 

Now, on the object that you want to filter, create an advanced filter. In my example, I am using this expression:

 

IF ( 'Age Group'p NotMissing )
RETURN (
IF ( 'Age Group'p = '11-14' )
RETURN ( 'Age'n BetweenInclusive(11, 14) )
ELSE ( 'Age'n BetweenInclusive(13, 16) ) )
ELSE ( 1 = 1 )

I will put the same code with comments below:

IF ( 'Age Group'p NotMissing )  * Check if the parameter is set (is anything selected in the drop-down list)
RETURN (
   IF ( 'Age Group'p = '11-14' )  * If the selection is '11-14', then...
   RETURN ( 'Age'n BetweenInclusive(11, 14) )  * Select rows where Age is between 11 and 14
   ELSE ( 'Age'n BetweenInclusive(13, 16) ) ) * Select rows where Age is between 13 and 16
ELSE ( 1 = 1 ) * Nothing is selected in the list, so return TRUE and show all rows.

By adding additional IF/ELSE statements, you should be able to handle as many selections as needed.

 

Let us know if you get it working or if you have additional questions.

 

Thanks,

Sam

View solution in original post

3 REPLIES 3
Sam_SAS
SAS Employee

Hello,

 

This should be possible using a parameter and an advanced filter.

 

community3c1.gifcommunity3c.gif

 

For the drop-down list, you need to have a column that contains the selector values such as "15-19 years". If you have access to the source data table, adding these values to the table would be the simplest approach. If you cannot update the source table, you can create the values within VA by creating either a custom category or a calculated item. For example, I did this as a custom category:

 

community3a.gif

It is important to remember that these mappings don't really matter. All we are doing is making a column that has the values "11-14" and "13-16".

 

Now, create a drop-down list object in the body of your page. It must NOT be placed in the page prompt area or report prompt area, because we do not want the control to directly filter anything. The data item that has your selector labels should be assigned to the Category role, and you will create a new character parameter to assign to the Parameter role.

community3b.gif

 

Now, on the object that you want to filter, create an advanced filter. In my example, I am using this expression:

 

IF ( 'Age Group'p NotMissing )
RETURN (
IF ( 'Age Group'p = '11-14' )
RETURN ( 'Age'n BetweenInclusive(11, 14) )
ELSE ( 'Age'n BetweenInclusive(13, 16) ) )
ELSE ( 1 = 1 )

I will put the same code with comments below:

IF ( 'Age Group'p NotMissing )  * Check if the parameter is set (is anything selected in the drop-down list)
RETURN (
   IF ( 'Age Group'p = '11-14' )  * If the selection is '11-14', then...
   RETURN ( 'Age'n BetweenInclusive(11, 14) )  * Select rows where Age is between 11 and 14
   ELSE ( 'Age'n BetweenInclusive(13, 16) ) ) * Select rows where Age is between 13 and 16
ELSE ( 1 = 1 ) * Nothing is selected in the list, so return TRUE and show all rows.

By adding additional IF/ELSE statements, you should be able to handle as many selections as needed.

 

Let us know if you get it working or if you have additional questions.

 

Thanks,

Sam

lucydog
Fluorite | Level 6

Thank you very much, Sam. This worked and I would not have been able to figure it out on my own, ever.

 

Ideally, though, I wanted a page, or possibly a report, drop-down list with overlapping values. But it seems that these "advanced filters with parameters" only work at the object level? When I moved the filter to "page control" -- which is an option -- it behaved strangely and on objects where I had not created a filter. [You had said, "It must NOT be placed in the page prompt area or report prompt area", and that appears to be good advice.]

 

So the filter worked perfectly for filtering my one object. However, I would like this same filter to work on lots of objects/pages. So, I wondered, do I need to create the same filter for each object, or is there a way 1) to copy this filter to other objects; or 2) to use this filter as a page or report control?

 

Sam_SAS
SAS Employee

Hi Lucy,

 

Great to hear it worked for you.

 

Controls in the report prompt area or the page prompt area automatically filter all objects in the report (or page, for a page prompt) so that is why your drop-down list must be in the body of a page. This is a common issue for users (as a user myself I find it hugely frustrating!) and the dev team is looking into a way to improve that situation. I can't promise this will be fixed, but it is a known issue. 

 

Depending on the version of VA that you have, you might be able to convert the filter to a "common" filter that can be assigned to multiple objects. Using Common Filters 

 

Alternatively, if you edit the filter in "Text mode", you can copy-paste the expression to facilitate making additional copies of it.

 

Sam

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1231 views
  • 1 like
  • 2 in conversation