Waterfall Plot: Custom sort order in the legend in PROC TEMPLATE

[KuldeepThakuri]

Dear Community, 

I am having hard time getting the correct legend order for waterfall plot(wf) attached.  The plot is required to be sorted in descending order of variable PCHG(bar height) and grouped by LabScore (bar color). The legend order for bar color we want is "Low", "High" and "Not Available" in that order. Within PROC TEMPLATE, we have SORTORDER options which takes the values (AUTO, ASCENDINGFORMATTED, DESCENDINGFORMATTED). The last 2 SORTORDER option do not work for us because the legend order we want is neither ascending/descending. The first one also does not work because we have no control over the order of LabScore(bar color) value. 

Below is the proc template code that I have, followed by the data. 

Please suggest. 

proc template;
   define statgraph sgplot;
   dynamic _NEGATIVE_ ;
   dynamic _ticklist_;
   begingraph / collation=binary dataColors=( GRAY ORANGE BIGB ) dataContrastColors=( CX000000 ) designwidth=900 ;
    layout overlay /
          xaxisopts=(display=(label) tickvalueattrs=(size=8 family='Arial') label="Individual Patients (n=%left(&bigN))" )
          yaxisopts=( Label="Tumor Size (% Change from Baseline)" labelFitPolicy=Split type=linear
          linearopts=( tickvaluelist=( 100 80 60 40 20 0 -20 -40 -60 -80 -100 ) viewmin=-120 viewmax=100 ))
          y2axisopts=(labelFitPolicy=Split offsetmin=0.1) ;

          barchart x=sbjid y=pchg/group=cps grouporder=data primary=true NAME="VBARPARM" ; ;

          ReferenceLine y=-30 / clip=true Lineattrs=( Pattern=2);
          ReferenceLine y=20 / clip=true Lineattrs=( Pattern=2);

         Layout Gridded / Border=false valign=top halign=right pad=(right=50);
          Entry halign=right "Best Response" /valign=top textattrs=( Size=12);
        endlayout;

       DiscreteLegend "VBARPARM" / Location=Inside across=1 halign=left valign=bottom Title="LabScore" border=false                      sortorder=ascendingformatted;

endlayout;
endgraph;
end;
run;

/*Data*/

data wf;
input pchg $sbjid grpvarn grpvar $14.;

datalines;
11.925 1 9 Not Evaluable
8.333 2 1 High
7.246 3 2 Low
-14.326 4 2 Low
-21.429 5 2 Low
-22.222 6 2 Low
-23.295 7 2 Low
-24.446 8 2 Low
-25.581 9 9 Not Evaluable
-29.167 10 9 Not Evaluable
-36.000 11 2 Low
-38.583 12 2 Low
-42.857 13 2 Low
-49.333 14 1 High
-56.250 15 1 High
-57.143 16 1 High
-57.576 17 2 Low
-64.892 18 2 Low
-66.071 19 1 High
-67.475 20 1 High
-68.750 21 9 Not Evaluable
-70.000 22 2 Low
-74.194 23 1 High
-76.471 24 9 Not Evaluable
-76.526 25 2 Low
-76.848 26 9 Not Evaluable
-78.798 27 2 Low
-80.000 28 1 High
-81.722 29 9 Not Evaluable
-81.915 30 2 Low
-82.701 31 1 High
-83.871 32 9 Not Evaluable
-86.364 33 9 Not Evaluable
-88.679 34 1 High
-89.286 35 9 Not Evaluable
-90.129 36 9 Not Evaluable
-94.068 37 2 Low
-100.000 38 1 High
-100.000 39 9 Not Evaluable
-100.000 40 9 Not Evaluable
-100.000 41 2 Low
-100.000 42 1 High
-100.000 43 2 Low
;
run;

[DanH_sas · Accepted solution]

The way to do this is to use a Discrete Attributes Map (attrmap), but the key is to tell the attrmap to use it's information for the legend. You can then order the legend by what's in the map. This is done by using the SHOW column in the attrmap data set, or by using the DISCRETELEGENENTRYPOLICY option if you embed the attrmap directly in your GTL template. Below, I took your code, removed the colors and legend sorting, and used an attrmap data set. Let me know if you prefer having it directly in the template.

Hope this helps!

Dan

data wf;
input pchg sbjid grpvarn grpvar $14.;

datalines;
11.925 1 9 Not Evaluable
8.333 2 1 High
7.246 3 2 Low
-14.326 4 2 Low
-21.429 5 2 Low
-22.222 6 2 Low
-23.295 7 2 Low
-24.446 8 2 Low
-25.581 9 9 Not Evaluable
-29.167 10 9 Not Evaluable
-36.000 11 2 Low
-38.583 12 2 Low
-42.857 13 2 Low
-49.333 14 1 High
-56.250 15 1 High
-57.143 16 1 High
-57.576 17 2 Low
-64.892 18 2 Low
-66.071 19 1 High
-67.475 20 1 High
-68.750 21 9 Not Evaluable
-70.000 22 2 Low
-74.194 23 1 High
-76.471 24 9 Not Evaluable
-76.526 25 2 Low
-76.848 26 9 Not Evaluable
-78.798 27 2 Low
-80.000 28 1 High
-81.722 29 9 Not Evaluable
-81.915 30 2 Low
-82.701 31 1 High
-83.871 32 9 Not Evaluable
-86.364 33 9 Not Evaluable
-88.679 34 1 High
-89.286 35 9 Not Evaluable
-90.129 36 9 Not Evaluable
-94.068 37 2 Low
-100.000 38 1 High
-100.000 39 9 Not Evaluable
-100.000 40 9 Not Evaluable
-100.000 41 2 Low
-100.000 42 1 High
-100.000 43 2 Low
;
run;

proc template;
   define statgraph sgplot;
   dynamic _NEGATIVE_ ;
   dynamic _ticklist_;
   begingraph / collation=binary designwidth=900 ;
    layout overlay /
          xaxisopts=(display=(label) tickvalueattrs=(size=8 family='Arial') label="Individual Patients (n=%left(&bigN))" )
          yaxisopts=( Label="Tumor Size (% Change from Baseline)" labelFitPolicy=Split type=linear
          linearopts=( tickvaluelist=( 100 80 60 40 20 0 -20 -40 -60 -80 -100 ) viewmin=-120 viewmax=100 ))
          y2axisopts=(labelFitPolicy=Split offsetmin=0.1) ;



          barchart x=sbjid y=pchg/group=grpvar grouporder=data primary=true NAME="VBARPARM" ; ;

          ReferenceLine y=-30 / clip=true Lineattrs=( Pattern=2);
          ReferenceLine y=20 / clip=true Lineattrs=( Pattern=2);



         Layout Gridded / Border=false valign=top halign=right pad=(right=50);
          Entry halign=right "Best Response" /valign=top textattrs=( Size=12);
        endlayout;



       DiscreteLegend "VBARPARM" / Location=Inside across=1 halign=left valign=bottom Title="LabScore" border=false ;

endlayout;
endgraph;
end;
run;


data LabScoreMap;
retain id "myid" show "attrmap" linecolor "black";
length value $ 13 fillcolor $ 6;
input value $ 1-13 fillcolor $;
cards;
Low           orange
High          bigb
Not Evaluable gray
;
run;


proc sgrender data=wf template=sgplot dattrmap=LabScoreMap;
dattrvar grpvar="myid";
run;

[KuldeepThakuri]

Thanks for the feedback, Dan. That certainly helped. 

I was also doing some research and testing the use of DISCRETEATTRMAP statement within PROC TEMPLATE to get similar results.

Many Thanks,

KD