topic Re: Sortorder in Keylegend in SAS Programming

Sortorder in Keylegend

Anita_n — Tue, 28 Feb 2023 14:45:55 GMT

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?

Re: Sortorder in Keylegend

PaigeMiller — Tue, 28 Feb 2023 14:54:22 GMT

Are you talking about PROC SGPLOT, or something else?

Re: Sortorder in Keylegend

Anita_n — Tue, 28 Feb 2023 15:01:19 GMT

Yes please

Re: Sortorder in Keylegend

PaigeMiller — Tue, 28 Feb 2023 15:52:23 GMT

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;

Re: Sortorder in Keylegend

Anita_n — Tue, 28 Feb 2023 16:22:58 GMT

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

Re: Sortorder in Keylegend

Anita_n — Wed, 01 Mar 2023 10:38:23 GMT

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

Re: Sortorder in Keylegend

PaigeMiller — Wed, 01 Mar 2023 11:25:06 GMT

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.

Re: Sortorder in Keylegend

Anita_n — Wed, 01 Mar 2023 12:09:22 GMT

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

Re: Sortorder in Keylegend

PaigeMiller — Wed, 01 Mar 2023 12:14:54 GMT

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.

Re: Sortorder in Keylegend

Anita_n — Wed, 01 Mar 2023 12:20:50 GMT

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

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

Re: Sortorder in Keylegend

PaigeMiller — Wed, 01 Mar 2023 12:26:10 GMT

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

Re: Sortorder in Keylegend

Anita_n — Wed, 01 Mar 2023 16:14:26 GMT

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

Re: Sortorder in Keylegend

PaigeMiller — Wed, 01 Mar 2023 16:19:39 GMT

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

Re: Sortorder in Keylegend

Anita_n — Wed, 01 Mar 2023 16:32:11 GMT

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

Re: Sortorder in Keylegend

PaigeMiller — Wed, 01 Mar 2023 17:17:33 GMT

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-legends/

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.

Re: Sortorder in Keylegend

PaigeMiller — Wed, 01 Mar 2023 17:41:11 GMT

I have a feeling that this code could be greatly streamlined, but I think this works, following the ideas in the papers I mentioned.

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;

proc sql;
    create table distinct_drugs as select distinct drug from have3;
    create table distinct_visit as select distinct visit from have3;
    create table distinct_period as select distinct period from have3;
    create table all_distinct as select * from distinct_drugs,distinct_visit,distinct_period;
quit;
data have4;
    set all_distinct have3;
run;

data have5;
set have4;
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;
if sumdosis=. then sumdosis=0;

keep period drug visit SumDosis N_percentage order;
run;

proc summary data=have5 nway;
    class period visit drug;
    var sumdosis;
    id order;
    output out=have6 sum=;
run;
proc sort data=have6;
    by period visit order;
run;

proc sgpanel data = have6 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;

Re: Sortorder in Keylegend

Jay54 — Wed, 01 Mar 2023 19:58:32 GMT

The method described in the blog post Feb 02, 2016 allows you to define group values that may not be in the data, but need to be included in the legend. Note, this same method also controls the order in which the values are displayed in the legend as described below:

https://blogs.sas.com/content/graphicallyspeaking/2017/11/01/legend-order-redux/

I hope you can use this method to get the legend entries in the custom order you need.

Re: Sortorder in Keylegend

Anita_n — Thu, 02 Mar 2023 07:17:57 GMT

@Jay54 Very easy and efficient method, thankyou