topic Re: 100% Stacked Bar Charts in SAS Visual Analytics

100% Stacked Bar Charts

AleCla — Fri, 22 Nov 2013 09:36:18 GMT

Hi All,

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?

Cheers!

Alex

Re: 100% Stacked Bar Charts

data_null__ — Fri, 22 Nov 2013 12:33:12 GMT

Show your work.

Re: 100% Stacked Bar Charts

AleCla — Mon, 25 Nov 2013 12:57:56 GMT

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:

Re: 100% Stacked Bar Charts

AncaTilea — Mon, 25 Nov 2013 14:41:40 GMT

How about this?

- I am using just a sample of your data;

data in2;

input grp $ sell month $;

datalines;

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

freq=sell type=percent

inside=percent width=20

gaxis=axis1 raxis=axis2 ;

run;

quit;

Good luck.

Anca.

Re: 100% Stacked Bar Charts

thees — Mon, 25 Nov 2013 14:57:33 GMT

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.

Re: 100% Stacked Bar Charts

AleCla — Mon, 25 Nov 2013 15:04:54 GMT

Yes, I'm trying to do this using solely SAS Visual Analytics,

Cheers,

Alex

Re: 100% Stacked Bar Charts

Renato_sas — Mon, 25 Nov 2013 15:14:22 GMT

In VA 6.3 (release planned for December 2013), you will be able to do that by combining aggregated measures with the ability to define new chart templates (this is new in 6.3). I've added a screenshot.

Re: 100% Stacked Bar Charts

AleCla — Mon, 25 Nov 2013 16:43:26 GMT

Thanks a lot for this Renato, good to know this is very shortly going to be available!

Cheers,

Alex

Re: 100% Stacked Bar Charts

thees — Mon, 25 Nov 2013 18:32:08 GMT

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

Re: 100% Stacked Bar Charts

Bastiaan — Thu, 24 Apr 2014 16:04:10 GMT

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?

Regards, Bastiaan

Re: 100% Stacked Bar Charts

Renato_sas — Mon, 28 Apr 2014 14:22:11 GMT

Hi Bastiaan,

I'll put some instructions together and share it here, hopefully today.

Stay tuned!

Re: 100% Stacked Bar Charts

Renato_sas — Mon, 28 Apr 2014 21:10:56 GMT

It's attached.

Sorry it took so long, but I wanted to make sure I covered every aspect of it.

Let me know if you have questions.

Renato.

Re: 100% Stacked Bar Charts

Bastiaan — Mon, 28 Apr 2014 21:26:01 GMT

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

Re: 100% Stacked Bar Charts

justin_sas — Wed, 30 Apr 2014 01:43:26 GMT

Hey Bastiaan,

The 100% stacked bar chart is on the roadmap for VA, we will let you know when we know for sure which release it will be in.

Regards,

Justin

Re: 100% Stacked Bar Charts

EO13 — Tue, 06 May 2014 13:02:16 GMT

Stacked bar charts can be done in VA, even with 6.2. (Without custom chart types).

What you need to do is convert your variables in to aggregated measures in VA (as percentages).

So if I have measures called A, B, C and "total" measure, create "New Aggregated Measure" for A/B/C.

THEN

This creates a new "aggregated measure" which I called %A. If I do B and C, I can drop them onto a bar chart with "stacked" chosen to get a graph like this:

We recently upgraded to VA 6.4, but the above screenshot was actually taken from 6.2. The category at the bottom that I call module can be anything that the data supports.

Re: 100% Stacked Bar Charts

Renato_sas — Tue, 06 May 2014 16:52:07 GMT

I really like it! It's much simpler than my approach.

Just expanding on your solution, I'd like to show how to derive A,B,C, etc. as mentioned in your example (in case they don't exist) for any existing measure, which makes this solution even more flexible.

I have 3 columns of interest:

Facility Region
Product Line (four distinct values that I'll use to derive four new calculated items equivalent to A,B,C,D)
Revenue (that's the reference measure)

I want to show % Revenue for Product Lines per Facility Region.

To calculate Revenue for Product Line A:

Repeat that for all Product Lines, and then calculate their respective percentages (aggregated measures) as indicated before:

And that's the final chart:

The same approach can be used to combine multiple Product Lines (and call them Others), if we are mostly interested on A and B only:

Thanks EO13!

Re: 100% Stacked Bar Charts

AleCla — Wed, 07 May 2014 13:25:51 GMT

Hi,

Thanks for both of these notes, it is useful to see this solution I can see how it will work when you have a distinct set of values (product lines) in the above example, however if you have a dynamic set of values you need to create an aggregated measure for each one, which will require constant maintenance to completely add up to 100% for each if you are interested in the detail.

The limitation to this is you need to repeat this process for each individual measure you choose which can become quite time consuming and prone to error!

Cheers,

Alex

Re: 100% Stacked Bar Charts

MichelleHomes — Thu, 12 Jun 2014 07:06:23 GMT

Hi Justin,

Do you know what release the 100% stacked bar chart will be in?

Also I was wondering whether having this aggregation would also provide a 100% within a category group for a cross tab so that rather than a percent of total measure all adding up to 100% in a crosstab it will be 100% within each column for instance. (I think I had raised a tech support feature request for this in the past.)

Thanks,

Michelle

Re: 100% Stacked Bar Charts

Renato_sas — Thu, 13 Nov 2014 17:12:24 GMT

Just an update... VA 7.1 released in the beginning of October supports 100% Stacked Bar Charts. It is just a Bar Chart property called Grouping Scale and the option is called "Normalize groups to 100%", like the screenshot.

Thanks,

Renato

Re: 100% Stacked Bar Charts

mlogan — Tue, 04 Aug 2015 19:07:45 GMT

Hi Anca,

Your code is working for one of my analysis, but can you please tell me how do I create 100% stack bar on character variable. Let say grp (% of type of grp) on month in this case.

I would much appreciate if you can help.

Thanks,