sgplot hbar - Is it possible to sort categories by size and groups alphabetically?

[Fredrik-Hansson]

Hi.

I'ld like to sort horizontal bars in descending order, but I want the groups within the bars to be sorted alphabetically. I that possible? I hope my examples can explain what I mean.

title1 "Sorting by bar-size works fine...";
title2 "categoryorder=respdesc";

proc sgplot data=sashelp.heart;
hbar Smoking_Status / categoryorder=respdesc;
yaxistable Smoking_Status / position=left location=inside ;
run;

The groups appear in the same order as in the yaxistable. Perfect!

title1 "Sorting groups alphabetically also works fine...";
title2 "grouporder=ascending";

proc sgplot data=sashelp.heart;
hbar Smoking_Status / group=sex grouporder=ascending ;
yaxistable Smoking_Status / position=left location=inside ;
run;

When I try to sort the bars by size, and the groups alphabetically, the groups get ordered by size:

title1 "But if we combine the two, groups get ordered by size, not alphabetically";
title2 "categoryorder=respdesc + grouporder=ascending";

proc sgplot data=sashelp.heart; 
hbar Smoking_Status / group=sex grouporder=ascending categoryorder=respdesc;
yaxistable Smoking_Status / position=left location=inside ;
run;

Is there a way to sort the bars/categories by total width and sort the groups alphabetically?

I'm using SAS9.4M6

[Fredrik-Hansson]

I just realized my question might be a duplicate of https://communities.sas.com/t5/Graphics-Programming/Stacked-Box-Plot-sorted-by-Y-axis-variable-and-a...

Will have a closer look at that solution tomorrow.

[ballardw · Accepted solution]

From the SGPLOT documentation for HBAR:

Interactions

When a group variable is used with the CATEGORYORDER= option, the response values for each group segment become the sorting key. CATEGORYORDER sorts first by the response statistic and then displays the GROUP values sorted within each category.

Get the values in the correct order and HBARPARM will work

proc summary data=sashelp.heart nway;
   class smoking_status;
   output out=work.summary2 ;
run;
proc sort data=work.summary2;
   by descending _freq_;
run;
data work.summary2;
  set work.summary2;
  order=_n_;
run;
 
proc sql;
   create table work.plot as
   select a.order,b.*
   from work.summary2 as a
      left join
      work.summary as b
      on a.smoking_status=b.smoking_status
   order by a.order, b.sex
   ;
quit;


proc sgplot data=work.plot;
   hbarparm category=Smoking_status response=_freq_/
         group=sex ;
run;

I didn't bother with the axistable stuff or labels for the category variable. Should be routine.

[Fredrik-Hansson]

---

@ballardw wrote:

From the SGPLOT documentation for HBAR:

Interactions

When a group variable is used with the CATEGORYORDER= option, the response values for each group segment become the sorting key. CATEGORYORDER sorts first by the response statistic and then displays the GROUP values sorted within each category.

Thank you for a fast and good solution/workaround!

I guess my problem is expected behaviour then.

But I don't really understand why category order has to interfere with grouporder. If I wanted categoroes and groups orderede by size I would expect to use something like categoryorder=respdesc grouporder=respdesc

Do you think I can talk SAS support into filing it as a bug or change request?

Your solution works like a charm, but I'm going to make around 20 graphs like that one in a single report. The code will not be easy to maintain. I have to choose between writing a lot of code or writing a semi complicated macro. Performance will probably be an issue to. Subsetting of data will be parameterized, so summarization must be done during report execution.

p.s. I changed your solution a little to minimize reading/writing datasets (your code was more explanatory):

proc summary data=sashelp.heart missing completetypes;
   class smoking_status sex;
   types smoking_status smoking_status*sex;
   output out=work.summary ;
run;

	
proc sql;
   create table work.plot as
   select order_data.category_total_freq, plot_data.*
   from work.summary(where=(_type_=3)) as plot_data
      left join
		work.summary(where=(_type_=2) rename=(_FREQ_=category_total_freq)) as order_data
      on plot_data.smoking_status=order_data.smoking_status
   order by order_data.category_total_freq desc, plot_data.sex
   ;
quit;


proc sgplot data=work.plot;
	hbarparm category=Smoking_Status response=_FREQ_ / group=sex;
	yaxistable _FREQ_ / class=sex position=left  location=inside ;
run;

[ballardw]

---

@Fredrik-Hansson wrote:

---

@ballardw wrote:

From the SGPLOT documentation for HBAR:

Interactions

When a group variable is used with the CATEGORYORDER= option, the response values for each group segment become the sorting key. CATEGORYORDER sorts first by the response statistic and then displays the GROUP values sorted within each category.

Thank you for a fast and good solution/workaround!

I guess my problem is expected behaviour then.

But I don't really understand why category order has to interfere with grouporder. If I wanted categoroes and groups orderede by size I would expect to use something like categoryorder=respdesc grouporder=respdesc

Do you think I can talk SAS support into filing it as a bug or change request?

SAS has a fairly robust suggestion program. Look at the community SASWARE Ballot Ideas.

I doubt that it would be accepted as a bug as the behavior is as documented.

If you have many similar plots from the same data with the same group variables then Proc summary will likely summarize all of them at one time. Remove the NWAY and you will get summaries of combinations of variables and you subset on the _TYPE_ variable.

Which is one reason I seldom use Proc SQL for a simple summary.

Though you likely need to watch sort order carefully. With the correct _types_ kept in the data you might be surprised how simple that might be. SGPLOT with the correct where _type_= would be useful.

Or possibly even restructuring the data so that you have a single "value" variable plus a source variable and do the plot by that source variable and use a BY in the proc sgplot.