I'm trying to put a basic bar chart together to show by month the relative percentage split of each category, but am running into difficulty!
I have tried creating a graph and using the grouping style 'Stack' and then using the aggregated measure 'frequency percent', however this is giving me the percentage over the overall time period / dataset. I need it fixed to each month so that it shows the percentage of the total for that specific month. Can someone advise how I can do this, or whether there is a specific aggregated measure I need to create to do this?
It's a hard one to easily explain in SAS VA, but here's an example in Excel, where I have 5 categories by months to show their sales. I want to see the relative split of sales by month by the different categories, but when I try using the 'frequency percent' aggregated measure in SAS VA, it simply looks for the percentage of the overall total over the 6 months. For example, Shoes has 882 sales in January, and there were 2,790 total sales in January across the 5 products, so I want to see 882/ 2,790 (32%). However, SAS VA is calculating it as the percentage of the entire sales which is 882/13,447 (7%).
Here's a screenshot to explain it further:
How about this?
- I am using just a sample of your data;
input grp $ sell month $;
shoes 882 Jan
shoes 280 Feb
shoes 602 Mar
shoes 223 Apr
shoes 221 May
shoes 842 Jun
socks 280 Jan
socks 633 Feb
socks 465 Mar
socks 625 Apr
socks 104 May
socks 403 Jun
tee 763 Jan
tee 637 Feb
tee 141 Mar
tee 102 Apr
tee 663 May
tee 98 Jun
/* Suppress the group axis label and values */
axis1 label=none value=none;
/* Angle the label for the response axis */
axis2 label=(angle=90 'Percent');
proc gchart data=in2;
vbar month / discrete subgroup=grp
group=month g100 nozero
gaxis=axis1 raxis=axis2 ;
Even though your solution works fine, I do not think this is what the author was looking for.
As far as I know, he would like to solve this problem solely using Visual Analytics.
I have tried out a couple of things myself, but nothing seemed to deliver the expected results.
You could use a filter on the month attribute selecting any one month. This way the sum of percentages over the stacked categories on that month sums up to 100%.
To compare two month, just create another chart next to it.
This is quite dirty, but does the trick for the time being
Hi Renato, I need to do exactly the same thing, and your answer sounds very promising. However still I can't figure out how to do it. I'm assuming you use the Custom Graph Builder in VA 6.3, but that's where I get stuck. Could you elaborate a bit more on how you tune the definition of a bar chart (I'm assuming) in Graph Builder to get it to show the % on the Y-axis? Anything else I need to know? What (aggregated) measure do I create or use?
Renato, thank you so much for these instructions! It's really great that you took the effort of pulling these together. This method is a bit of a workaround, but it is suitable if the number of categories on the X-axis is fixed, known and limited. I need to do some more analysis on my side to see if this will work in my client's situation. I'll let you know.
On a related note: is another graph type, or maybe an extension of the properties of the existing bar chart, on the roadmap for VA? This type seems to be fairly common in Excel, and I think many clients find it useful.
Kind regards, Bastiaan
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.