Pivot challenge - pivots without cross classification

Hi All,

In a discussion on moving to SAS Visual Analytics I, very foolishly, said “why don’t you do it in a pivot table?” And now I’m faced with eating my hat to the SA VA guys, or finding a solution, using a pivot table.

The data is structured as follows:

ID, Category1, Category2, Category3
1, Yes, Yes, No
2, No, No, Yes
3,,,
4, Yes, Yes, No

The output is the proportion of each category which are “Yes”, to all “Yes” and “No” values. Using the above example:

Category1 Category2 Category3
66% 66% 33%

While I can achieve this using DCOUNTS and formula, I cannot for the life of me work out how to create this simple seeming output using a pivot.

Interestingly, so far the SAS VA guys can only do this by converting the “Yes” and “No” values to 0 and 1s, but even doing this I can only get the denominators, without being able to divide them by the correct numerator in the pivot table.

Any ideas?

Hi,

Welcome to the Forum.

I could achieve the desired result by adding a new column for "No"s in each category. Please see the attached file.

Hi cbatrody, thanks for your solution, it might have some applications if I were building a new dataset.

The data structure I put up is a simplification and if possible a solution which doesn't involve manipulating the data outside of a pivot would be best.

Even the conversion to 0 and 1 values in the same cell is a step further that I would like to take.

P.