Format not working in Gchart

[mariosegal]

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?

Thanks.

[MikeZdeb]

hi ... add "discrete" ... produced the attached ...

proc gchart data=a;

vbar group1 / discrete sum sumvar=value ;

format group1 a.;

run;

[mariosegal]

All,

the solution did not work on my data, can someone help?

I added the actual data, the gcgart and format code to the post

[mariosegal]

Mike,

the solution did not work on my real data, I added a dataset and code - could you help me understand what is going on?

[MikeZdeb]

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

proc format;

value clvband

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+'

;

run;

data new;

set z.clv4 (keep=clv_total teal_flag hhs);

newvar = put(clv_total, clvband.);

run;

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;

footnote1 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;

legend1 label=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;

run;

quit;

[mariosegal]

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

Mario

[mariosegal]

Thanks, I will try it on my original dataset right now

Mario