Sortorder in Keylegend

[Anita_n]

Is it possible to sort the entries in my key directory in a way I personally define, instead of using ascending, descending or autoreverse as stated in the documentation?

[PaigeMiller]

Are you talking about PROC SGPLOT, or something else?

[Anita_n]

Yes please

[PaigeMiller]

Example:

data cars;
    set sashelp.cars;
    if origin='Asia' then order=2;
    else if origin='USA' then order=3;
    else if origin='Europe' then order=1;
run;
proc sort data=cars;
    by order;
run;
proc sgplot data=cars;
    scatter x=mpg_city y=mpg_highway/group=origin grouporder=data;
run;

[Anita_n]

I will try that and let you know if it worked. Thanks a lot

[Anita_n]

@PaigeMiller  sorry it's not working the way I want it to.

using this sample data. I want the "other Piriton" to be the last element.

data have;                                          
infile datalines;                                   
input period $10.  drug $15.  visit dosis 2.;
datalines; 
2000-2005	Cefalexin       1 12     
2000-2005	Calcipotriol	1 16     
2000-2005	Folic acid	    1 26     
2000-2005	Piriton         1 3      
2000-2005	Fentanyl	    1 36     
2000-2005	Folic acid      1 26     
2000-2005	Co-beneldopa	1 35     
2000-2005	Folic acid      1 26     
2000-2005	Cefalexin	    1 11     
2000-2005	Fentanyl	    1 35     
2006-2011	Allopurinol     1 33     
2006-2011	Folic acid      1 27     
2006-2011	Cefalexin       1 11     
2006-2011	Allopurinol	    1 33     
2006-2011	Cefalexin       1 11     
2006-2011	Folic acid      1 26     
2006-2011	Piriton	        1 3      
2006-2011	Folic acid	    1 27     
2006-2011	Allopurinol	    1 33     
2006-2011	Piriton	        1 3      
2006-2011	Cefalexin       1 11     
2011-2022	Piriton	        1 3      
2011-2022	Cefalexin	    1 11     
2011-2022	Cefalexin	    1 12     
2011-2022	Cefalexin	    1 11     
2011-2022	Cefalexin	    1 11     
2011-2022	Baclofen	    1 35     
2011-2022	Diclofenac	    1 22     
2011-2022	Diclofenac      1 22     
2011-2022	Co-beneldopa	1 35     
2011-2022	Co-beneldopa	1 3
2011-2022   Other Piriton   1 22 
2000-2005	Co-beneldopa    2 3      
2000-2005	Folic acid      2 15     
2000-2005	Cefalexin		2 14     
2000-2005	Fentanyl	    2 11     
2000-2005	Allopurinol     2 3      
2000-2005	Folic acid      2 3      
2000-2005	Cefalexin       2 5      
2000-2005	Allopurinol	    2 15     
2000-2005	Cefalexin       2 35     
2006-2011	Folic acid  	2 16     
2006-2011	Piriton	        2 22     
2006-2011	Folic acid	    2 38     
2006-2011	Allopurinol	    2 11     
2006-2011	Piriton	        2 7      
2006-2011	Cefalexin       2 11     
2006-2011	Piriton	        2 11     
2006-2011	Cefalexin	    2 15     
2006-2011	Cefalexin	  	2 17     
2006-2011	Cefalexin	    2 11     
2011-2022	Cefalexin	    2 33     
2011-2022	Baclofen	    2 77     
2011-2022	Diclofenac	    2 25     
2011-2022	Acrivastine     2 12     
2011-2022	Acrivastine     2 8     
2011-2022	Acrivastine     2 15     
2011-2022	Acrivastine     2 20     
2011-2022	Acrivastine     2 6     
2011-2022	Acrivastine     2 20     
2011-2022	Acrivastine     2 33
2011-2022   Other Piriton   2 14
2000-2005	Cefalexin       3 14     
2000-2005	Calcipotriol	3 18     
2000-2005	Folic acid	    3 16     
2000-2005	Piriton         3 15     
2000-2005	Fentanyl	    3 19     
2000-2005	Folic acid      3 20     
2000-2005	Co-beneldopa  3 25     
2000-2005	Folic acid    3 52     
2000-2005	Cefalexin	  3 25     
2000-2005	Fentanyl	  3 16     
2006-2011	Allopurinol   3 8       
2006-2011	Folic acid    3 2       
2006-2011	Cefalexin     3 55       
2006-2011	Allopurinol	  3 70       
2006-2011	Cefalexin     3 18       
2006-2011	Folic acid    3 1       
2006-2011	Piriton	      3 20       
2006-2011	Folic acid	  3 11       
2006-2011	Allopurinol	  3 14       
2006-2011	Piriton	      3 7      
2006-2011	Cefalexin     3 33       
2011-2022	Piriton	      3 20       
2011-2022	Cefalexin	  3 15       
2011-2022	Cefalexin	  3 12       
2011-2022	Cefalexin	  3 77       
2011-2022	Cefalexin	  3 6       
2011-2022	Acrivastine	  3 44       
2011-2022	Acrivastine	  3 7       
2011-2022	Acrivastine	  3 25       
2011-2022	Acrivastine	  3 11       
2011-2022	Acrivastine	  3 9   
2011-2022   Other Piriton 3 8
;                                  
run;                               

proc sql;
create table have2 as select period, drug, visit,  sum(dosis) as SumDosis from have group by period, drug, visit;

create table sumtotal as select period,visit, sum(SumDosis) as totals  from have2  group by period, visit;

create table have3 as select a.*, b.totals, SumDosis/totals as percentage format percent8.2 from have2 as 
a, sumtotal as b where a.visit=b.visit and a.period=b.period;

quit;

data have4;
set have3;
length N_percentage $15 ;
N_Percentage=catx(" ", SumDosis, "(", put(percentage, percent8.2), ")");
keep period drug visit SumDosis N_percentage;
run;
proc sort data have4;
by period visit drug;
run;

proc sgpanel data = have4 noautolegend;
styleattrs datacolors=(lightgreen lightred lightblue);
panelby period /columns=3 novarname noborder;
 vbarparm  category=visit response=SumDosis / group=drug grouporder=data  datalabel  groupdisplay=stack name='a';
 keylegend 'a'/ position=bottom across=6 title=" ";
  ;
run;

[PaigeMiller]

I provided an example where I assigned a custom order variable to each record (based upon the variable ORIGIN in SASHELP.CARS) I don't see where you have attempted to order the categories of your variables in your data, as I have done in my code. Please give that a try.

Also:

@Anita_n you have been in this forum for a long time now. Please provide example data in your original question from now on. If you had, then you may have gotten a quicker and better answer.

You stated you were using PROC SGPLOT, but you are not. Please provide correct information.

[Anita_n]

@PaigeMiller sorry, it was sgplot, I later changed it to sgpanel inorder to use the text statement.  I have updated the code

data have4;
set have3;
length N_percentage $15 ;
N_Percentage=catx(" ", SumDosis, "(", put(percentage, percent8.2), ")");

if drug ="Acrivastine" then order=1; if drug ="Allopurinol" then order=2;  if drug ="Baclofen" then order=3;
if drug ="Calcipotriol" then order=4; if drug ="Cefalexin" then order=5;  if drug ="Co-beneldopa" then order=6; 
if drug ="Diclofenac" then order=7;  if drug ="Fentanyl" then order=8; if drug ="Folic acid" then order=9;
if drug ="Piriton" then order=10; if drug ="Other Piriton" then order=11;

keep period drug visit SumDosis N_percentage order;
run;

proc sort data= have4;
by period visit order;
run;

proc sgpanel data = have4 noautolegend;
styleattrs datacolors=(lightgreen lightred lightblue);
panelby period /columns=3 novarname noborder;
vbarparm category=visit response=SumDosis / group=drug grouporder=data seglabel groupdisplay=stack ;
rowaxis values=(0 to 300 by 50);
keylegend / position=bottom across=6 title=" ";
;
run;

[PaigeMiller]

Change your PROC SORT

proc sort data= have4;
    by period order;
run;

You need to sort by Period because of the PANELBY PERIOD command and you need to sort by order to get the drugs in the desired order.

[Anita_n]

I already did that (see above): I need to sort these three variables 

proc sort data= have4;
by period visit order;
run;

[PaigeMiller]

That's not the code I used. I don't see why you need to include VISIT in the sort.

[Anita_n]

I need to sort that because the sortorder changes when I plot the graph

[PaigeMiller]

Did you try it without VISIT in PROC SORT, as in my code? If so, what is wrong with the plot that results?

[Anita_n]

This is what happens if I don't sort the visits. It starts with the first the the fifth  and then the fourth for example. In the real data I have visits 1 to 12. I did not list all here because this will be a lot of work. But it's okay, I will try other means. Thankyou

[PaigeMiller]

Okay, we have gone down the path that produces correct plots in PROC SGPLOT, but apparently not in PROC SGPANEL.

If you read these posts, the method ought to work in PROC SGPANEL

http://saslist.com/blog/2016/02/17/a-simple-trick-to-include-and-order-all-categories-in-sgplot-lege...

https://blogs.sas.com/content/iml/2016/02/17/include-and-order-categories-in-legends.html

According to these posts, you prepend all missing categories, so you have all possible categories for each visit and period. Then sort by the desired order, and then you should get what you want.

I call on @Rick_SAS and @Jay54 to advise if this method will work for PROC SGPANEL with the VBARPARM statement.