How to set the order of alpha categories on group and subgroup levels in a bar chart

[qaguy1982]

https://communities.sas.com/t5/Graphics-Programming/GCHART-graphics-area-for-Horizontal-bar-charts-c...

I submitted the above question and received an answer that has been mostly workable for me.  I have a followup.

When I reverted back to my original data, the categories for the four pairs of bar charts sort alphabetically into a different order.

My customer wants report categories like :

Preventive Services (including the two detail bar charts for this category and so on for the remaining categories.)

           Benchmark Usage percentage

           My Client usage percentage    

Acute/Emergency Services

           Benchmark Usage percentage

           My Client usage percentage    

Mix of Services

           Benchmark Usage percentage

           My Client usage percentage    

No Services  

           Benchmark Usage percentage

           My Client usage percentage    

You will note that these are not in alpha order as the original data set  (Service Type 1, Service Type 2... Service Type 4)

Is there a way to control the order of both the primary group and the subgroups?

If I try to control the groups using Midpoints, I get stacked bars for the two sets of details.

I don't seem to be able to set midpoints for each level

I just changed the original data set to change the group titles but now they sort in the order of Acute, Mix, No and Preventive which is like Service types 2,3,4,1.  I want to be able to sort them without forcing a number of letter ordering on the display.

I also tried using PROC Format to change the data but that just replaces the original data with the new format and then sorts by the new format.  What I want is the order of the categories to be 1,2,3,4 but display the text descriptions on my chart.

Thanks again for any advice.  This has been a great forum.  

[Reeza]

PROC FORMAT is the right approach, can you show how you tried that option? Did you specify an ORDER option on one of the statements?

[qaguy1982]

My data set looks like this :

data sample_slide4_usage;
infile datalines delimiter= ',';
input
usage_text : $60. source : $45. my_order : $40. GROUP_NUMBER : $10.
COUNT PERCENT comment_text : $80. Service_Type1 Service_Type2 Service_Type3 ;
/* insert some blanks and '\', to use as the 'split' character in the axis */
usage_text='a0'x||'\'||'a0'x||'\'||trim(left(usage_text));
datalines ;
PService Type 1 has a long description,Benchmark,1-Service Type 1,BENCH,3334399,31.468822649, members had this type of service,1,0,0
PService Type 1 has a long description,Your Members' Utilization,1-Service Type 1,0009999,3506,32.705223881, members had this type of service,1,0,0
AService Type 2 medium,Benchmark,2-Service Type 2,BENCH,193769,1.828720047, members had this type of service,0,1,0
AService Type 2 medium,Your Members' Utilization,2-Service Type 2,0009999,247,2.3041044776, members had this type of service,0,1,0
MixService Type 3 medium,Benchmark,3-Service Type 3,BENCH,3150839,29.736451363, members had this type of service,0,0,1
MixService Type 3 medium,Your Members' Utilization,3-Service Type 3,0009999,3598,33.563432836, members had this type of service,0,0,1
NType 4 short,Benchmark,4-Service Type 4,BENCH,3916874,36.966005941, members had this type of service,0,0,0
NType 4 short,Your Members' Utilization,4-Service Type 4,0009999,3369,31.427238806, members had this type of service,0,0,0
;
run;

proc sort data= sample_slide4_usage out=  sample_slide4_usage;
by  my_order source;
run;

I want the report to present in this order but use the Usage_Text as the printed category label to the left of the actual graph.

data my_anno; 
set  sample_slide4_usage;
length function $8 text $100;
/* use data VALUE oriented numbering systems - this will allow percentages to hang on the end of the bar initialize to '2' for both */
xsys='2'; ysys='2';
/* HSYS = 3 MEANS USE PERCENTAGE OF GRAPHICS OUTPUT AREA FOR HEIGHT - I don't think this is being used as there is no size variable */
hsys='3'; when='a';
/* set the initial function and color default  - can be changed later in the step . */
function='label'; color='gray33';

/* Annotate the values at the ends of the bars  -  Set the first bar percentage */
/* this will also print the percentage for the second bar when it comes around*/
/* set the group variable to match grouping on the gchart */
/* This value prints as part of the gaxis  (axis2 definition ) print using the Axis. 
/* There may be another way to print this label that allows me to substitute a different label to the coordinate value */
/* The two slashes in front cause it to skip down and line up in the middle of the two bars */
group=my_order;
yc=source;
x=PERCENT;
/* put the label just at the end of the chart */
function='label'; position='>';
/* build the text label with a leading space */
text='a0'x||put(percent/100,percent7.2);
output;
run;

Then I created the formats to change from my_order to usage_text.  And tried to run the report.  It replaces the my_order text with the usage_text data but then re-sorts the data on the graph to match the new alpha order for the new formatted values.

proc format;                                                                                                                            
   value $usage_text   
'1-Service Type 1'=' \ \PService Type 1 has a long description' 
'2-Service Type 2'=' \ \AService Type 2 medium' 
'3-Service Type 3'=' \ \MixService Type 3 medium' 
'4-Service Type 4'=' \ \NType 4 short' ; 
run; 
 proc format;                                                                                                                            
   value $my_order   
' \ \PService Type 1 has a long description'= '1-Service Type 1' 
' \ \AService Type 2 medium'= '2-Service Type 2'
' \ \MixService Type 3 medium'= '3-Service Type 3'
' \ \NType 4 short'= '4-Service Type 4'; 
run;

goptions device=png xpixels=800 ypixels=500;

goptions gunit=pct htext=9pt;

ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm" style=htmlblue;

axis1 label=none value=none;
axis2 label=none value=(justify=right font='Calibri/bold' h=12pt) split='\' offset=(3,2);
axis3 label=none value=none major=none minor=none style=0 offset=(0,5);

legend1 label=none noframe across=1 position=(top right outside) mode=share value=("Benchmark" "Your Members' Utilization")
shape=bar(.1in,.1in) offset=(-5,8);

pattern1 v=s c=cx48af35;
pattern2 v=s c=gray33;

title1 h=16pt font='Calibri/bold' ls=1.5
box=1 bcolor=cx48af35 color=white 'Member Utilization at a Glance';

proc gchart data= sample_slide4_usage anno=my_anno;
hbar source /  type=sum sumvar=percent nostats
midpoints= "Benchmark" "Your Members' Utilization"
maxis=axis1 
/* commenting this next line should drop the axis label for Usage Type forces the default axis labels */
gaxis=axis2 
raxis=axis3
/*noaxis*/
noframe
/* change group to my_order - originally was usage_text -  modify the order of graphs */
space=0 gspace=4 group=my_order  /* or my_order */
legend=legend1 coutline=same subgroup=source
des='' name="&name";
format my_order $usage_text. ;
run;
/*midpoints =( " \ \PService Type 1 has a long description"  " \ \AService Type 2 medium" " \ \MixService Type 3 medium" " \ \NType 4 short")*/

quit;
ODS HTML CLOSE;
ODS LISTING;

This is sorted alphabetically and I want it sorted in the order by Type numberThis is how I want is sorted but with the text description being usage text.

[Reeza]

The format approach is to recode the values to numeric that will sort accordingly, either a format or IF/THEN statements and then using a format to display what you actually want to see. 

data class;
set sashelp.class;

if sex='F' then sex_order = 1;
else if sex='M' then sex_order = 2;
run;

proc format;
value sex_fmt
1 = 'Female'
2 = 'Male';
run;

proc freq data=class;
table sex_order;
format sex_order sex_fmt.;
run;

Hope that illustrates the usage for you, code is untested at the moment. 

[GraphGuy · Accepted solution]

Here's one way to do it, assigning a numeric value for the bar groups, and then creating a data-driven user-defined-format to control what text gets printed instead of the bar_order numeric values.

data sample_slide4_usage; set sample_slide4_usage;
if index(usage_text,'PService Type 1 has a long description')^=0 then bar_order=1;
if index(usage_text,'AService Type 2 medium')^=0 then bar_order=2;
if index(usage_text,'MixService Type 3 medium')^=0 then bar_order=3;
if index(usage_text,'NType 4 short')^=0 then bar_order=4;
run;

proc sql noprint;
create table control as
select unique bar_order as start, usage_text as label
from sample_slide4_usage;
quit; run;
data control; set control;
fmtname='barfmt';
type='N';
run;
proc format lib=work cntlin=control;
run;

pattern1 v=s c=cx48af35;
pattern2 v=s c=gray33;
axis1 label=none split='\' value=(justify=right);
axis2 label=none value=none;
axis3 label=none minor=none;
legend1 label=none position=(top right) mode=share shape=bar(.15in,.15in) across=1;

proc gchart data= sample_slide4_usage;
format bar_order barfmt.;
hbar source / type=sum sumvar=percent nostats
group=bar_order space=0 subgroup=source
gaxis=axis1 maxis=axis2 raxis=axis3 legend=legend1;
run;

[qaguy1982]

Both of these solutions were correct.  I am still confused as to why proc format from text to text would not do the same thing.  However, I can set the values to numeric and then proc format them to text and it works.   I will mark the more complete answer as a solution but I got most of the way with Reeza's response.

Thanks to both of you.