SGPLOT legend order

[swannie]

Hi,

I'm using SAS 9.4 with the attached code and data.  I would like to be able to set the order of the legend items to be something other than alphabetic (which is not meaningful in this context).  It should be Complete Response, Partial Response, Unconfirmed Partial Response, Stable Disease, and Progressive Disease.  My last attempt was to give the responses numeric values with formats, but the legend uses the formats and not the numeric values to determine the order.  There are some workarounds for other types of graphs, but I didn't find anything for a waterfall plot.  Any help vastly appreciated!

The site keeps telling me that the contents of my .sas and .sas7bdat files don't match their file types (????) so I'm pasting in the code and data below.

Regards,

Suzanne

　

* Set today's date*;

data _null_;

tday=put(today(),date9.);

call symput('today',tday);

run;

* Set libname;

libname ads204 'path';

libname bor204 'path';

* Assign a macro variable to the data cut-off date;

%let cutoff=09APR2018;

　

*******************************************************************************************************************************************;

* Assign format for borcd ;

*******************************************************************************************************************************************;

proc format;

value dos 1='IMO2125 04 mg'

2='IMO2125 08 mg'

3='IMO2125 16 mg'

4='IMO2125 32 mg'

;

value rr 1='Complete Response'

2='Partial Response'

2.5='Unconfirmed Partial Response'

3='Stable Disease'

4='Progressive Disease'

5='Not Evaluable'

;

run;

　

proc sort data=ads204.waterfall out=waterfall; by subject; run;

proc sort data=bor204.bor out=bor ; by usubjid; run;

　

ods graphics / reset=all border=off WIDTH=10.0in;

ods powerpoint file = "path\Waterfall_Plot_&today..PPTX" ;

title ' ';

title1 "Maximum Percentage Decrease in Target Lesion Diameters";

title2 "Subjects Treated with IMO-2125 8mg + Ipilimumab " ;

footnote1 j=left height=1 "Data cut-off date: &cutoff Produced on &SYSDATE9." ;

proc sgplot data=waterfall;

format borcd rr.;

styleattrs datacolors=(CXC0C0C0 CX4682B4 cx7edb8b CXA3E5AC CX19BF30 ) datacontrastcolors=(black);

vbar n / response=rschg group=borcd dataskin=sheen;

refline 20 -30 / lineattrs=(pattern=shortdash);

xaxis display=none;

yaxis values=(60 to -100 by -20) grid label="Change from Basline (%)";

keylegend / title='' sortorder=ascending location=inside position=bottomleft across=1 border;

run;

　

ods powerpoint close;

ods graphics off;

proc printto; run;

  n borcd RSCHG

	1	4	40.0
	2	4	21.2
	3	3	16.2
	4	3	12.6
	5	4	12.0
	6	4	11.7
	7	3	10.3
	8	4	9.0
	9	3	0.0
	10	3	-1.1
	11	3	-12.5
	12	2	-39.5
	13	2	-51.5
	14	3	-57.1
	15	2	-58.2
	16	2	-73.3
	17	2	-81.8
	18	2.5	-93.2
	19	1	-100

[WarrenKuhfeld]

https://blogs.sas.com/content/graphicallyspeaking/2016/09/13/legend-order-and-group-attributes/

Use Show='AttrMap' in an attribute map.

[swannie]

Hi, Warren,

I tried the next-to-last method and it didn't work (see code below; it is exactly what's in the example on your blog).  The legend is now in the order of the data, which isn't at all what I need.   Please, what am I doing wrong?

proc format;

 value $test

'CR' = 'Complete Response'

'PR' = 'Partial Response'

'uPR' = 'Unconfirmed Partial Response'

'SD' = 'Stable Disease'

'PD' = 'Progressive Disease'

 ;

run;

data order;

input Value $;

retain ID 'A' Show 'AttrMap' LineStyle 'Solid'; * Set irrelevant attribute to use order to control legend;

datalines;

CR

PR

uPR

SD

PD

;

ods graphics / reset=all border=off WIDTH=10.0in;

title ' ';

title1 "Maximum Percentage Decrease in Target Lesion Diameters";

title2 "Subjects Treated with IMO-2125 8mg + Ipilimumab " ;

footnote1 j=left height=1 "Data cut-off date: &cutoff Produced on &SYSDATE9." ;

proc sgplot data=waterfall dattrmap=order;

format borc $test.;

styleattrs datacolors=(CXC0C0C0 CX4682B4 /*CX4682B4*/ cx7edb8b CXA3E5AC CX19BF30 ) datacontrastcolors=(black);

vbar n / response=rschg group=borc dataskin=sheen attrid=A;

refline 20 -30 / lineattrs=(pattern=shortdash);

xaxis display=none;

yaxis values=(60 to -100 by -20) grid label="Change from Baseline (%)";

keylegend / title='' location=inside position=bottomleft across=1 border;

run;

 　

ods graphics off;

[Jay54]

The key point to understand is the group are assigned the attributes and position in the legend in the order they appear in your data.  So, one simple (old fashioned) way is to make sure your group values appear in your data in the order you want the attributes to be assigned, or order in the legend.

Next, there are ways to control this beyond the order of the group values in the data.  You can use a Discrete Attributes Map to set the attributes you want for each group VALUE.  You can also set the order is which the values are displayed in the legend as the order provided in the Discrete Attributes Map, as described by Warren.

[swannie]

Hi, Sanjay,

Warren's method did not work and I haven't heard back from him.  Can you provide any other suggestion?  And why is it so hard to do this!?  🙂

swannie

[WarrenKuhfeld]

Do the values that you are plotting match the values in the attribute map?

[swannie]

Hi, Warren,

I find your question interesting.  The answer depends on what you mean by 'match'.  The plot is a waterfall plot, which is in decreasing order of the rschg variable.  The group variable is the best response, and those values are not in any particular order in the plot but the values of the group variable are the same as the values in the order dataset (from your example), which I assume is the 'attribute map'.  Does this answer your question?

swannie

[WarrenKuhfeld]

My attribute map example has formatted values of the group variable in the attribute map.  Do you have that?  I am away from my desk, and can't look closely, but it looked like you did not.  I am sure attribute maps work correctly.

[swannie]

Hi, Warren,

I just posted a reply to both you and Sanjay.  Bottom line is using the formatted values didn't help:  the legend is still in the order of the data.  Please advise!  (Code and figure are in my previous post.)

swannie

[Jay54]

The simple way is to add 5 obs with group value 1 - 4 at the top of the data with other values missing.  Note, your colors will be assigned in this order too.  Change their order in the STYLEATTRS too if needed.  The graph is below.

data waterfall;
input n borcd rschg;
datalines;
. 1 .
. 2 .
. 2.5 .
. 3 .
. 4 .
1 4 40.0
2 4 21.2
3 3 16.2
4 3 12.6
5 4 12.0
6 4 11.7
7 3 10.3
8 4 9.0
9 3 0.0
10 3 -1.1
11 3 -12.5
12 2 -39.5
13 2 -51.5
14 3 -57.1
15 2 -58.2
16 2 -73.3
17 2 -81.8
18 2.5 -93.2
19 1 -100
;
run;

Then, remove the SORTORDER on the legend.  Here is the result:

[Jay54]

Now, if you want a better way, create a discrete attr map with the FORMATTED values in the data set in the order you want, and set their attributes.  Include the column "Show" with value "ATTRMAP" in the Discrete Attr Map.  This forces all values defined in the map to be displayed in the order they are in the map.  See this article.

[swannie]

Well, Warren and Sanjay, it still doesn't work.  The legend is still, as it has been consistently, in the order of the data!  Warren, you were correct:  I didn't have the formatted values in the order dataset.  It didn't make any difference when I used them, however.  Here is my code, and I've attached the resulting figure.  The order should be Complete Response, Partial Response, Unconfirmed Partial Response, Stable Disease, Progressive Disease, and Not Evaluable, from top to bottom.  After several hours of effort, am I any nearer to getting what I need?  Any help vastly appreciated!

swannie

　

proc format;

value dos 1='IMO2125 04 mg'

2='IMO2125 08 mg'

3='IMO2125 16 mg'

4='IMO2125 32 mg'

;

value $rrc

'CR' = 'Complete Response'

'PR' = 'Partial Response'

'uPR' = 'Unconfirmed Partial Response'

'SD' = 'Stable Disease'

'PD' = 'Progressive Disease'

'NE' ='Not Evaluable'

;

run;

　

　

data order;

input Value $32.;

retain ID 'A' Show 'AttrMap' LineStyle 'Solid'; * Set irrelevant attribute to use order to control legend;

datalines;

Complete Response

Partial Response

Unconfirmed Partial Response

Stable Disease

Progressive Disease

Not Evaluable

;

run;

　

ods graphics / reset=all border=off WIDTH=10.0in;

title ' ';

ods powerpoint file = "U:\Waterfall_Plot_&today..PPTX";

title2 "Subjects Treated with IMO-2125 8mg + Ipilimumab " ;

footnote1 j=left height=1 "Data cut-off date: &cutoff Produced on &SYSDATE9." ;

proc sgplot data=waterfall dattrmap=order;

styleattrs datacolors=(CXC0C0C0 CX4682B4 /*CX4682B4*/ cx7edb8b CXA3E5AC CX19BF30 ) datacontrastcolors=(black);

vbar n / response=rschg group=borc dataskin=sheen attrid=A;

format borc $rrc.;

refline 20 -30 / lineattrs=(pattern=shortdash);

xaxis display=none;

yaxis values=(60 to -100 by -20) grid label="Change from Baseline (%)";

keylegend / title='' location=inside position=bottomleft across=1 border;

run;

　

ods powerpoint close;

ods graphics off;

[swannie]

Hi, Sanjay, note that the resulting slide has the legend in order of the data, not the order that was supposed to be specified. This doesn't work, sadly. swannie

[Jay54]

Just change the order of the first 5 observations in the order you want in the legend.

Also, set the colors in the STYLEATTRS in the order you want.