How to get crosstab columns (monthly) to total 100%, instead of as a sub-percentage of the entire (year) measure?

[howardn]

I'm trying to produce % calculations within a crosstab report element in which each column (in a multiple column report) totals 100%. For instance, if the x-axis is 12-months in 2014 and y-axis is 5 sales groups, I want each month to total 100% for those 5 groups (e.g., in Jan14 G1=10%,G2=20%,G3=30%,G4=35%,G5=5%, Feb14 ... Etc.). All the frequency counts are correct, but the paired % columns for each month represent the percentage of that monthly count divided by the entire year total, and not just for that month (which is what I want). This is something even our SAS Architect couldn't figure out, though it is the simplest thing to do in Excel. Others in this group must have encountered this same problem and I would hope there is a simple solution that does not involve lines of code in the background or a lot of ETL.

Thanks, Howard

SAS VA 7.1

[howardn]

Here is an example of what I'm asking about. I want the quarterly Frequency Percent columns to total 100% and each cell within the quarter to be a percentage of that quarter's results and add up to 100%:

[MichelleHomes]

Hi Howard,

As you have discovered, column percentages (or row percentage) is not available in the crosstab visualization. I raised this as a feature request in an early VA version. A possible alternative would be coding; to use a stored process with proc tabulate which can produce this report design.

Kind Regards,

Michelle

[howardn]

Michelle,

This is not a problem, it is a product defect.(Apparently not, as there is an excellent solution - hn).

Message was edited by: Howard Newstadt

[howardn]

Seriously. This is one of the most basic of measurements that is expected to be made in almost any business environment. For all the very good functionality in VA, the product FAILS on this feature is absent. SAS VA development needs to get it together in a big hurry.

Which they apparently have. thanks. hn

Message was edited by: Howard Newstadt

[howardn]

Michelle, thought you were from SAS. Appreciate your reply and the fact that you previous raised this feature request. I am just flabergasted that it has not been addressed by the development team.

[MichelleHomes]

Hi Howard,

No problem. I understand your frustration and hopefully this feature becomes a higher priority for the next release. It's wonderful to be able to share our experiences and I hope the feedback from this active community continues to steer future product features. If there are other members that would like this feature or would like to share their work arounds please comment.

Kind Regards,

MIchelle

[skillman]

Howard,

If you add Row Totals, in addition to Column Totals would this give you what you are seeking?

Here is a sample of data with a year, quarter, and month column hierarchy:

Same report with column hierarchy expanded. The total column maintains the % you are looking to see in VA.

-shawn

[howardn]

Skillman, thanks for your reply, but no it doesn't. In the first chart you are only reflecting 1 year's data in the same year column, so you obviously get 100%. In your drill down, the quarterly columns each should add to 100% to be able to compare quarterly rates, but they don't, instead they show the percent each quarter makes up of the total year. This is fine for the totals, but pretty useless for the rows. This gets even worse when you filter for several years..

[RickStyll_SAS · Accepted solution]

Hi Howard,

Here is another alternative to Shawn's that gives you percentages that add up to 100% for each column.  If Event Count is a column in your source table, you should be able to do the following steps to get the desired result.  If Event Count is an aggregated measure data item that you created in the left data panel, we might need to try another approach.

If you right click on the Event Count column header, you will see Create and Add in the context menu that pops up.

When you choose Percent of Subtotals..., a window pops up asking you if you want column subtotals and column totals.  A column total is essentially a subtotal of the grand total.  I didn't have a second category data item on the rows to the right of Facility State, so column subtotals are grayed out in this example.

The result will insert a new column to the right of Event Count that represents the percentage of the column total and the percentages for each column will add up to 100%.

You could shorten the label for that new column heading if that's too long.  Just scroll down in the left data panel and find the new derived aggregated measure with this longer name at the bottom.

Howard, I hope this helps.

Rick Styll

[howardn]

Rick, Thank you. that worked!!! I take back everything I said about the developers. Not I'll have to teach the engineer how to do it.

[MichelleHomes]

Thanks for the walk through response . I wasn't aware of the Percent of Subtotals aggregation. Great to see!

[howardn]

Rick, We do have some reports with only aggregrated % measure columns. So, if you can get me that other approach you mentioned for totalling an aggregated %measure sub-column, it will save redesigning the layout on some reports. This is a great feature though, in what version was it first introduced?

On second thought, I went back and added the second column with the aggregate and did away with all the % only elements. They are another managers reports, so I'll have to see how he likes them, otherwise I'll be looking for the aggregate column only solution.

Great suggestion on the name change as it saves a lot of column space. Also, once I make the name change to the new aggregate in the left hand data set, I just pull it over if I have an empty column or substitute it, if I have to create the new aggregate (then delete that one from the data column). We're cooking with gas now.

Thanks again,

Howard

PA Patient Safety Authority

Message was edited by: Howard Newstadt

[MichelleHomes]

Hi Howard,

I was curious to see when this feature was added too. 😉 I discovered it came in SAS Visual Analytics 6.2. It's mentioned in the SAS(R) Visual Analytics 6.2: User's Guide - What's new section under Report Enhanced Objects for the crosstab object.  There is further documentation on Percent of Subtotals at bottom of the page SAS(R) Visual Analytics 6.2: User's Guide - Percent of Subtotals in CrossTab  I guess my feature request made it into the product. I didn't know!

Kind Regards,

Michelle

[yhuang]

Hi Rick, in your orginal answer you mentioned

"If Event Count is an aggregated measure data item that you created in the left data panel, we might need to try another approach."

That's exactly what I am trying to figure out Can you help?

THANKS