topic Filter a list table by the selection in an other list table in SAS Visual Analytics

Filter a list table by the selection in an other list table

mbosma — Tue, 16 Jan 2018 12:59:55 GMT

Hello everyone,

I am using SAS Visual Analytics 7.2 and I have a question about the filter interaction between a list table and a list. My list table filters my list that contains one of the columns/variables from the list table. Now, when I use this list to filter a second list table that also contains the column/variable from the list, the second list table is not only filtered by the chosen value from the list, but the entire selected row in the first list table is passed through as a filter. I will try to explain my problem with a simple example. Let the first list table be given by:

Name Age Sex

Pete 21 M

Rose 23 F

Hank 45 M

Jenny 67 F

James 23 M

Now, this list table is linked to a list containing the variable Sex, and when I select the first row of the list table (Pete), I want to obtain the value for the variable Sex of that row, which is M, and use this as a filter for the second list table that for example looks like this:

Name Age Sex

Holly 33 F

Pete 21 M

Rose 23 F

Hank 45 M

Jenny 67 F

James 23 M

I want to use the selection in the first list table to filter on the variable Sex in the second list table. So, if I select the row for Pete in the first list table, I want to have all the rows with Sex equal to M in the second list table. My idea was to place a list containing the variable Sex between the two list tables. Then by selecting Pete in the first list table, which is linked to the list, I have that in the list for Sex only M is selected. Then, I would think that by linking this list to my second list table, it would filter the second list table on Sex = M, however, it filters on all the information on the row for Pete, hence it only shows the exact same row (Pete) in the second list table, instead of all rows with Sex = M. Now, my question: Is it possible to select a row in a list table, and use only the value of one particular column/variable of that row which can be used to filter another list table (containing this same column/variable).

I hope my explanation is clear enough and you guys know a solution.

Thanks in advance.

Mick

Re: Filter a list table by the selection in an other list table

BrunoMueller — Tue, 16 Jan 2018 13:40:14 GMT

I have no longer VA7.2 available, with VA7.4 you can do what you want.

ListTable1 filters List1

Assign a Character list parameter to List1

Enable Required checkbox, will select one value be default

Use the Character list parameter in a filter on ListTable2

See below

Re: Filter a list table by the selection in an other list table

mbosma — Tue, 16 Jan 2018 14:01:02 GMT

Hi Bruno,

Thank you for your quick answer. I have searched for the Character List Parameter in VA 7.2, but could not find it. Therefore, I searched for the additionial options in VA 7.4 and found this: "Parameters have been enhanced. You can now have parameters with multiple values, which enables you to use them in a list control." I guess this is the update I would need to be able to use the Character List Parameter.

Thanks again,

Mick

Re: Filter a list table by the selection in an other list table

BrunoMueller — Tue, 16 Jan 2018 14:12:22 GMT

Yes Character list parameters are new with VA7.4.

You will get a lot more additional functionality when you upgrade to VA7.4, so it is worthwile the effort.

Re: Filter a list table by the selection in an other list table

leoncai — Wed, 02 Jan 2019 06:20:24 GMT

Hi Bruno,

I have a similar question as Mick, however, I would like to ask if I am able to select a row from a list table with a page link to another page and filter the list table in the target page?

For instance, I have data as shown below in the first page:

I would like to select a row from the above list table and link the selection to another page. What I want to achieve is that from the selected row, I want to filter another list table in the target page using the NODE value selected. Is this possible?

Also, when I did page link using the first list table to the target page, I selected the "Set the value of controls in the target page prompt bar" option, however, it does not set the value for the controls in the target page and thus was not able to filter the list table.

Thank you and I hope to hear from you soon!

Best regards,

Leon

Re: Filter a list table by the selection in an other list table

Sam_SAS — Wed, 02 Jan 2019 15:37:20 GMT

Hi Leon,

What do the controls on your target page look like? I believe you will need to have a control on the target page that has NODE assigned.

Thanks,
Sam

Re: Filter a list table by the selection in an other list table

leoncai — Thu, 03 Jan 2019 01:30:40 GMT

Hi Sam,

I tried using all kinds of control except "Slider" and assigned NODE as their category on the target page. However, I was still unable to get the NODE value selected on the first page to be filled into the controls when I link the pages.

Best regards,

Leon

Re: Filter a list table by the selection in an other list table

Sam_SAS — Thu, 03 Jan 2019 14:22:12 GMT

Hi Leon,

What version of VA are you using?

Is it possible that your controls on the target page are not located in the prompt area? Controls can be placed either in the prompt area or in the main body of the page. (To show the prompt area, select the ... icon on the page tab and then select Expand page prompts.)

Controls that are not in the prompt area will be filtered by the page link, but their selections will not be set.

Sam

Re: Filter a list table by the selection in an other list table

Madelyn_SAS — Thu, 03 Jan 2019 14:33:32 GMT

I tested what appears to be your scenario and it worked for me.

Section 1 had a crosstab.

Section 2 had a list table and a drop-down control in the section prompt area.

I assigned the data item I wanted to pass to the drop-down control.

I linked the crosstab to Section 2 and specified to "Set the value for controls in the target section prompt area"

I navigated the link from the crosstab to Section 2 and the drop-down control contained the value passed from the crosstab.

That drop-down control, in turn, filtered the list table in Section 2.

Re: Filter a list table by the selection in an other list table

leoncai — Fri, 04 Jan 2019 02:02:57 GMT

Hi Sam and Madelyn,

Thank you so much for your replies and help, I realized that my mistake was not putting my filter in the page prompt area.

I am currently using VA 8.2.

Thanks again.

Best regards,

Leon

Re: Filter a list table by the selection in an other list table

Sam_SAS — Fri, 04 Jan 2019 12:39:07 GMT

Great! Glad to hear it is working for you now 🙂

Sam

Re: Filter a list table by the selection in an other list table

AnnaBrown — Fri, 04 Jan 2019 17:21:04 GMT

I'm glad you found some useful info, leoncai! If one of the replies was the exact solution to your problem, can you "Accept it as a solution"? Or if one was particularly helpful, feel free to "Like" it. This will help other community members who may run into the same issue know what worked.

Thanks!
Anna