I am having trouble creating a chart in whihc the values in the midpoint axis are defined by a format. It appars the format stops workign properly inside gchart - below is code that simulates the problem, the original exampkle was more complex with real data. The common element is the format has bands below zero, one band precisely at zero and bands above zero.
1. I create a dummy dataset, save the original values and apply the format, also define the format
2. Then i show that tabulate can do it properly using the formatted value or applying the format
3. But then gchart can't either way, it mixes up bands
I know the answer can be, use tabulate redirected to a dataset and then use that for the chart. The reason that does not work in my case, is because then the bands are ordered alhabetically on teh chart (the band names are like '$10 to $20, $20 to $30, ... $200 to 300-> obviosuly I do not want the 200 to be after the 20, ). I can't also use the order on the axis atatement because the chart is quite complex (it has groups and subgroups) and requires the nozero option, which for some reason then stops the order statement (as I said this is a simplification of the problem)
I can also apply the band on a data step with select, but not only is it more typing, and I also have the issu with the alpahbetical order.
Can anyone explain how to fix this, how do I properly define the format (assuming that is the problem whihc i do not believe as tabulate works)
Is this a bug in SAS? if so do I get a prize for finding it?
Hi ... finally had a chance to look at this. I'm not sure why it does not work with just a format, but I got it to work by first creating a variable using the format. The extra spaces in the labels make the bars come out in the correct order. Using the data you posted (I used a different font and added some white space around the chart with titles and a footnote) ...
low-<0 = ' Below Zero'
0 = ' Zero'
0<-<250 = ' Up to $250'
250-<500 = ' $250 to $500'
500-<750 = ' $500 to $750'
750-<1000 = ' $750 to $1,000'
1000-<1500 = '$1,000 to $1,500'
1500-<2500 = '$1,500 to $2,500'
2500-<5000 = '$2,500 to $5,000'
5000-high = '$5,000+'
set z.clv4 (keep=clv_total teal_flag hhs);
newvar = put(clv_total, clvband.);
goptions reset=all ftext='calibri/bo' htext=2 gunit=pct;
title1 h=3 'Total Lifetime Value (Mar 2012)' ls=2;
title2 a=90 ls=1;
title3 a=-90 ls=1;
axis1 minor=none color=black label=(a = 90 "Average Amount") split=" " ;
axis2 split=" " value=(h=1.75) label=none;
axis3 split=" " label=none value=none;
proc gchart data=new;
vbar newvar / type=percent freq=hhs group=newvar inside=subpct g100 subgroup=teal_flag
raxis = axis1 maxis=axis3 gaxis=axis2 legend=legend1 nozeros noframe;
Thanks a lot. I do not know why it does not work. I solved it by creating the zero band to be 0-<1, which happens to be ok for my data
incidentally, as it was it works perfectly in tabulate, so one would expect it to work on gchart, but it does not
SAS has aknowledged this is a defect and say they will work to fix it
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.