BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
howardn
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RickStyll_SAS
SAS Employee

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.

VA 7.1 - Percent of Column Total Menu.PNG

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.

VA 7.1 - Percent of Column Total Pop-up.PNG

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%.

VA 7.1 - Percent of Column Total Result.PNG

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.

VA 7.1 - Rename Percent of Column Total.PNG

Howard, I hope this helps.

Rick Styll

View solution in original post

14 REPLIES 14
howardn
Calcite | Level 5

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%:

SAS VA Column % issue.png

MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
howardn
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
skillman
SAS Employee

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:

screenshot1.png

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

screenshot2.png

-shawn

howardn
Calcite | Level 5

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
SAS Employee

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.

VA 7.1 - Percent of Column Total Menu.PNG

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.

VA 7.1 - Percent of Column Total Pop-up.PNG

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%.

VA 7.1 - Percent of Column Total Result.PNG

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.

VA 7.1 - Rename Percent of Column Total.PNG

Howard, I hope this helps.

Rick Styll

howardn
Calcite | Level 5

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
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
howardn
Calcite | Level 5

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
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
yhuang
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 14 replies
  • 10144 views
  • 18 likes
  • 5 in conversation