I’m new to SAS Visual Analytics, so please forgive me if I’ve missed the obvious answer to this, but a search hasn’t yielded anything for me.
We are trying to dashboard the results of a survey using various filters. Most questions visualize correctly, and the data matches the numbers we’ve generated using our flat files. However, we are running into an issue for items where respondents were able to select more than one response option (i.e., “select all that apply” questions). SAS Viya is correctly counting the number of unique responses for each option, but it is incorrectly calculating the percentages those counts represent. What seems to be happening is that Viya is taking each row from the table and adding them together to produce a denominator used in the percentage calculation. However, we need the denominator to use the distinct count of individuals within the whole table, not just within each row. Here’s an example: we have 4430 undergraduate students that took the survey, so the denominator for each row in this chart should be 4430. However, because some respondents selected multiple options, the percentages are being calculated using a denominator of 5230 – the sum of headcounts for each of the rows/bars. Therefore, the 1001 students that selected “academic performance” are being calculated as 1001/5230 (19%) when actually, 23% (1001/4430) of students selected that item.
Any thoughts on how to display percentages based on distinct counts when the categories are not mutually exclusive?
Thanks for any help!
Please have a look at this post as it might be a similar type of issue.
Thank you for the suggestion! John and I are with the same organization, and so we've discussed this solution. It works for his scenario, but mine is slightly different in that I'm not using a simple frequency. Instead, I'm looking to calculate the percentage based off of a distinct count of ID numbers in the chart, rather than just a sum of cases. So in this code
Sum [_ByGroup_] ('Population'n) / AggregateTable(_Sum_, Table( _Sum_, Fixed('Term'n), 'Population'n))
My "population" would be a distinct count of ID numbers. However, using a distinct count will not work within the given formula. Any thoughts on how to get around this?
Ok, I have another idea 😊
Could you please try this approach?
Distinct [_ForAll_] ('ID'n)
'Headcount'n / 'ID (Distinct count)'nDefine the format to be Percent.
Here's image from my report that might help get better grasp of this.
I hope this helps and if it doesn't, I think I'm going to need some kind of sample data.
Thanks for the help, Petri!
Although this solution didn't quite work for us, it does give us some tools to solve related questions we had. For now, our team is tabling this discussion and will return to it in an "office hours" session with SAS representatives in the near future. Much appreciated!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.