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?