BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Fredrik-Hansson
Fluorite | Level 6

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;

by_bar_size.png

 

 

 

 

 

 

 

 

 


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;

by_category_alphabetically.png

 

 

 


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;

by_size_but_messy.png

 

 


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

I'm using SAS9.4M6

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Fluorite | Level 6

@ballardw wrote:

From the SGPLOT documentation for HBAR:

InteractionsWhen 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
Super User

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

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 7521 views
  • 3 likes
  • 2 in conversation