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
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.
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.
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.
@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;
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: