Percentages for "Select all" Survey Questions

[EEARL]

Hi all,

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!

-Elijah

[PetriRoine]

Hello @EEARL 

Please have a look at this post as it might be a similar type of issue.

https://communities.sas.com/t5/SAS-Visual-Analytics/Calculating-frequency-percentage-by-category-in-...

Best regards,
Petri

[EEARL]

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?

[PetriRoine]

Ok, I have another idea 😊

Could you please try this approach?

1. Create a New calculated Item to get the number of distinct ID's
   

   Distinct [_ForAll_] ('ID'n)

2. Use the feature New data from aggregation of... 
   Selected items: Headcount, Response, ID (Distinct count)
3. In your newly created table create a New Calculated Item Percent
   

   'Headcount'n / 'ID (Distinct count)'n

   Define the format to be Percent.
4. Now use this new data to create the Bar chart and Crosstab.

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.

Best regards,

Petri

[EEARL]

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!