How to Display the Counts in the Discrete Legends for Box Plot in Proc Template.

[StudentSASLearn]

I am Learning the proc template. I tried to create a box plot with sashelp.cars. I've got to make it 80% of what I am thinking , I am  stuck at the last 20 % to finish. Looking for help. My code creates  graph correctly but stuck at creating the legend. Present graph only show the group legends text in legends, but

1. I want to add the number of to the legends text to display the counts. like (SUV-Asia (N= 25)). I tried creating the macro variable for counts and attach to the group., but it messing up my graph.

2. Is it possible to  to diplay all the Asia related in one column, USA in another column, and Europe in another columns in Legends

Here is my cars code

*create data;

proc sql;
    create table car_subjects as
    select distinct 
        cats(Make, "-", Model) as USUBJID length=50,
        Type as CarType length=15,
        Origin as Region length=15,
        Horsepower,
        Weight,
        MPG_City,
        MPG_Highway,
        MSRP
    from sashelp.cars
    where Make is not null and Model is not null;
quit;

*get Count to display in legend;

proc sql;
    create table car_counts as
    select CarType, Region, count(*) as N
    from car_subjects
    group by CarType, Region;
quit;

* Add count to dataset;

proc sort data=car_subjects;
    by CarType Region;
run;

proc sort data=car_counts;
    by CarType Region;
run;

/* Step 4: Merge and build final dummy dataset */
*try with few groups first;
data dummy_cars_final ( where = (region in ('USA' 'Asia' 'Europe') and cartype in ('SUV' 'Sedan' 'Truck')));
    merge car_subjects(in=a) car_counts;
    by CarType Region;
    if a;

    /* Derived variables */
    region_type = catx("-", CarType, Region);

    Score = round(40 + ranuni(0)*60, 0.1);

    PowerIndex = round((Horsepower * 0.6 + Weight * 0.0005), 0.1);

    EcoScore = round((MPG_City * 0.4 + MPG_Highway * 0.6), 0.1);

    length LuxuryLevel $10;
    if MSRP > 50000 then LuxuryLevel = "High";
    else if MSRP > 30000 then LuxuryLevel = "Medium";
    else LuxuryLevel = "Low";

    drop Horsepower Weight MPG_City MPG_Highway MSRP;
run;

proc template;
   define statgraph boxplot_template;
      begingraph;

         discreteattrmap name="comboMap" / ignorecase=true;
            value "SUV-Asia" / fillattrs=(color=orange);
            value "SUV-Europe" / fillattrs=(color=orange) ;
			value "SUV-USA" / fillattrs=(color=orange) ;
            value "Sedan-Asia" / fillattrs=(color=magenta) ;
            value "Sedan-Europe" / fillattrs=(color=orange);
            value "Sedan-USA" / fillattrs=(color=magenta);
            value "Truck-Asia" / fillattrs=(color=grey) ;
            value "Truck-USA" / fillattrs=(color=grey);
         enddiscreteattrmap;

         discreteattrvar attrvar=patgroup var=region_type attrmap='comboMap';

         layout lattice / rows=2 columns=1 columndatarange=union ROWWEIGHTS=(.75 .25) ;
            layout overlay / 
               xaxisopts=(label="Region" labelattrs=(size=12pt weight=bold)
                          tickvalueattrs=(size=12pt weight=bold))
               yaxisopts=(offsetmin=0.05 offsetmax=0.05 label="Rating Score"  
                          linearopts=(tickvaluesequence=(start=0 end=100 increment=10)));

               boxplot x=region y=score /
                  name='BoxLegend'
                  group=patgroup 
                  groupdisplay=cluster 
                  boxwidth=0.6 clusterwidth=0.5 
                  display=( median mean caps fillpattern ) 
                  medianattrs=(pattern=1);


            endlayout;

			discretelegend 'BoxLegend' / 
			      border=false 
			      valueattrs=(size=8pt weight = bold) 
			      across=3 location=inside valign=top  ;
            endlayout;

      endgraph;
   end;
run;

proc template;
   define style styles.mypatterns;
      parent=styles.listing;
      style GraphData1 from GraphData1 / fillpattern="R1" ;
      style GraphData2 from GraphData2 / fillpattern="X1" ;
      style GraphData3 from GraphData3 / fillpattern="E" ;
      style GraphData4 from GraphData4 / fillpattern="R1" ;
      style GraphData5 from GraphData5 / fillpattern="X1" ;
      style GraphData6 from GraphData6 / fillpattern="E" ;
      style GraphData7 from GraphData7 / fillpattern="R1" ;
      style GraphData8 from GraphData8 / fillpattern="X1" ;
      style GraphData9 from GraphData9/ fillpattern="E" ;
   end;
run;



options orientation=landscape;
ods rtf file="boxplot_grouped.rtf" style=mypatterns;
ods graphics / reset width=8.5in height=5.5in border=off;

proc sgrender data=Dummy_cars_final template=boxplot_template;
run;

ods rtf close;

Thank you.

[Ksharp]

Could you use PROC SGPLOT ?

proc sql;
    create table car_subjects as
    select distinct 
        cats(Make, "-", Model) as USUBJID length=50,
        Type as CarType length=15,
        Origin as Region length=15,
        Horsepower,
        Weight,
        MPG_City,
        MPG_Highway,
        MSRP
    from sashelp.cars
    where Make is not null and Model is not null;
quit;

*get Count to display in legend;

proc sql;
    create table car_counts as
    select CarType, Region, count(*) as N
    from car_subjects
    group by CarType, Region;
quit;

* Add count to dataset;

proc sort data=car_subjects;
    by CarType Region;
run;

proc sort data=car_counts;
    by CarType Region;
run;

/* Step 4: Merge and build final dummy dataset */
*try with few groups first;
data dummy_cars_final ( where = (region in ('USA' 'Asia' 'Europe') and cartype in ('SUV' 'Sedan' 'Truck')));
    merge car_subjects(in=a) car_counts;
    by CarType Region;
    if a;
call streaminit(123);
    /* Derived variables */
    region_type = catx("-", CarType, Region);

    Score = round(40 + ranuni(0)*60, 0.1);

    PowerIndex = round((Horsepower * 0.6 + Weight * 0.0005), 0.1);

    EcoScore = round((MPG_City * 0.4 + MPG_Highway * 0.6), 0.1);

    length LuxuryLevel $10;
    if MSRP > 50000 then LuxuryLevel = "High";
    else if MSRP > 30000 then LuxuryLevel = "Medium";
    else LuxuryLevel = "Low";

    drop Horsepower Weight MPG_City MPG_Highway MSRP;
run;


proc sql;
create table dummy_cars_final2 as
select region_type,score,region,cats(region_type,'(N=',count(*),')') as region_type2 from dummy_cars_final
 group by region_type
union
/*create a dummy "Truck-Europe"*/
select "Truck-Europe",score,region,region_type from dummy_cars_final
 where region_type='Sedan-Europe'
;
quit;


/*Mask the  dummy "Truck-Europe" box and legend*/
%sganno
data sganno;
 %SGRECTANGLE( X1=52,Y1=4.4,HEIGHT=3,WIDTH=24,DRAWSPACE="GRAPHPERCENT" ,FILLCOLOR="white" ,DISPLAY="FILL",FILLTRANSPARENCY=0 )
run;


ods graphics /attrpriority=none ;
proc sgplot data=dummy_cars_final2 sganno=sganno;
styleattrs  DATAFILLPATTERNS=(R1 X1 L1 R1 X1 L1 R1 X1 L1)
datacontrastcolors=(red blue orange green navy darkred  purple white black);
  vbox score /category=region name='x' 
                  group=region_type2 
                  groupdisplay=cluster 
                  boxwidth=0.6 clusterwidth=0.5 
                  fillpattern nofill
                  medianattrs=(pattern=1) nooutliers;
  keylegend 'x'/title='';
run;

[StudentSASLearn]

Thank you very much for your response. Is it possible to do in proc template? Or impossible?. Also can we make each region same color but different pattern in patterns and contrast color options.You have very great knowledge.

[Ksharp · Accepted solution]

Yes. I believe GTL is able to do that. But I have a limited knowledge of GTL. Maybe @tc  could give you some help.

Anyway, here is something you could start with:

and you need to change the order of region_type2 to make your requirement happen.

proc sql;
    create table car_subjects as
    select distinct 
        cats(Make, "-", Model) as USUBJID length=50,
        Type as CarType length=15,
        Origin as Region length=15,
        Horsepower,
        Weight,
        MPG_City,
        MPG_Highway,
        MSRP
    from sashelp.cars
    where Make is not null and Model is not null;
quit;

*get Count to display in legend;

proc sql;
    create table car_counts as
    select CarType, Region, count(*) as N
    from car_subjects
    group by CarType, Region;
quit;

* Add count to dataset;

proc sort data=car_subjects;
    by CarType Region;
run;

proc sort data=car_counts;
    by CarType Region;
run;

/* Step 4: Merge and build final dummy dataset */
*try with few groups first;
data dummy_cars_final ( where = (region in ('USA' 'Asia' 'Europe') and cartype in ('SUV' 'Sedan' 'Truck')));
    merge car_subjects(in=a) car_counts;
    by CarType Region;
    if a;

    /* Derived variables */
    region_type = catx("-", CarType, Region);

    Score = round(40 + ranuni(0)*60, 0.1);

    PowerIndex = round((Horsepower * 0.6 + Weight * 0.0005), 0.1);

    EcoScore = round((MPG_City * 0.4 + MPG_Highway * 0.6), 0.1);

    length LuxuryLevel $10;
    if MSRP > 50000 then LuxuryLevel = "High";
    else if MSRP > 30000 then LuxuryLevel = "Medium";
    else LuxuryLevel = "Low";

    drop Horsepower Weight MPG_City MPG_Highway MSRP;
run;

proc template;
   define statgraph boxplot_template;
      begingraph/ATTRPRIORITY=NONE ;

         discreteattrmap name="comboMap" / ignorecase=true;
		    value "SUV-Asia(N=25)" / lineattrs=(color=orange ) markerattrs=(color=blue symbol=diamond) ;
            value "SUV-Europe(N=10)" / lineattrs=(color=magenta)  markerattrs=(color=blue symbol=diamond);
			value "SUV-USA(N=25)" / lineattrs=(color=grey)   markerattrs=(color=blue symbol=diamond);
            value "Sedan-Asia(N=94)" / lineattrs=(color= orange) markerattrs=(color=green symbol=circle) ;
            value "Sedan-Europe(N=78)" / lineattrs=(color=magenta) markerattrs=(color=green symbol=circle);
            value "Sedan-USA(N=90)" / lineattrs=(color=grey) markerattrs=(color=green symbol=circle);
            value "Truck-Asia(N=8)" / lineattrs=(color= orange) markerattrs=(color=red symbol=plus);
			value "Truck-Europe(N=0)" / lineattrs=(color=white) markerattrs=(color=white symbol=plus);
			value "Truck-USA(N=16)" / lineattrs=(color=grey) markerattrs=(color=red symbol=plus);

         enddiscreteattrmap;




         discreteattrvar attrvar=patgroup var=region_type2 attrmap='comboMap';

         layout lattice / rows=2 columns=1 columndatarange=union ROWWEIGHTS=(.75 .25) ;
            layout overlay / 
               xaxisopts=(label="Region" labelattrs=(size=12pt weight=bold)
                          tickvalueattrs=(size=12pt weight=bold))
               yaxisopts=(offsetmin=0.05 offsetmax=0.05 label="Rating Score"  
                          linearopts=(tickvaluesequence=(start=0 end=100 increment=10)));

               boxplot x=region y=score /
                  name='BoxLegend'
                  group=patgroup 
                  groupdisplay=cluster 
                  boxwidth=0.6 clusterwidth=0.5 
                  display=( median mean caps fillpattern ) 
                  ;

annotate / id="BAR";

            endlayout;

			discretelegend 'BoxLegend' / 
			      border=false 
			      valueattrs=(size=8pt weight = bold) 
			      across=3 location=inside valign=top  ;
            endlayout;

      endgraph;
   end;
run;

proc template;
   define style styles.mypatterns;
      parent=styles.listing;
      style GraphData1 from GraphData1 / fillpattern="R1" ;
      style GraphData2 from GraphData2 / fillpattern="X1" ;
      style GraphData3 from GraphData3 / fillpattern="L1" ;
      style GraphData4 from GraphData4 / fillpattern="R1" ;
      style GraphData5 from GraphData5 / fillpattern="X1" ;
      style GraphData6 from GraphData6 / fillpattern="L1" ;
      style GraphData7 from GraphData7 / fillpattern="R1" ;
      style GraphData8 from GraphData8 / fillpattern="X1" ;
      style GraphData9 from GraphData9/ fillpattern="L1" ;
   end;
run;


proc sql;
create table dummy_cars_final2 as
select region_type,score,region,cats(region_type,'(N=',count(*),')') as region_type2 from dummy_cars_final
 group by region_type
union
/*create a dummy "Truck-Europe"*/
select "Truck-Europe",score,region,"Truck-Europe(N=0)" from dummy_cars_final
 where region_type='Sedan-Europe'
;
quit;

/*Mask the  dummy "Truck-Europe" box and legend*/
%sganno
data sganno;
 %SGRECTANGLE(ID="BAR", X1=52,Y1=10,HEIGHT=4,WIDTH=15,DRAWSPACE="GRAPHPERCENT" ,FILLCOLOR="white" ,DISPLAY="FILL",FILLTRANSPARENCY=0 )

run;


options orientation=landscape;
ods rtf file="c:\temp\boxplot_grouped.rtf" style=mypatterns;
ods graphics / reset width=8.5in height=5.5in border=off outputfmt=png;

proc sgrender data=Dummy_cars_final2 template=boxplot_template sganno=sganno;
run;

ods rtf close;

[StudentSASLearn]

Hi @Ksharp . Thank you very much. You guys are SAS AIs. Do much faster and perfectly.  I will wait if  @tc  refines or gives any suggestions. Otherwise, I am happy with the output and will mark it as a Solution. However, I have two questions about the code.  If it's not inconvenient, can you please shed some light on it?

1. At the following step, can I create the macro variable for count that you did in SGPLOT and present it (N= &x1.)? Will it be dynamic, or will it cause me any problems?

    value "SUV-Asia(N=25)" / lineattrs=(color=orange ) markerattrs=(color=blue symbol=diamond) ; This is Resolved I was able to do it.

2. I see you using two macros , I don't see these are local macros, are those available everyone with SAS 9.4 like default macros? if not can you please direct a link where I can read about please. thank you again for your time.

%SGRECTANGLE

%sganno

[Ksharp]

I am not expert about GTL. So maybe @tc   could give you better solution.

About your question:

1)Yes. you need to create macro variable for these count to make the code dynamic. Here is :

proc sql;
    create table car_subjects as
    select distinct 
        cats(Make, "-", Model) as USUBJID length=50,
        Type as CarType length=15,
        Origin as Region length=15,
        Horsepower,
        Weight,
        MPG_City,
        MPG_Highway,
        MSRP
    from sashelp.cars
    where Make is not null and Model is not null;
quit;

*get Count to display in legend;

proc sql;
    create table car_counts as
    select CarType, Region, count(*) as N
    from car_subjects
    group by CarType, Region;
quit;

* Add count to dataset;

proc sort data=car_subjects;
    by CarType Region;
run;

proc sort data=car_counts;
    by CarType Region;
run;

/* Step 4: Merge and build final dummy dataset */
*try with few groups first;
data dummy_cars_final ( where = (region in ('USA' 'Asia' 'Europe') and cartype in ('SUV' 'Sedan' 'Truck')));
    merge car_subjects(in=a) car_counts;
    by CarType Region;
    if a;

    /* Derived variables */
    region_type = catx("-", CarType, Region);

    Score = round(40 + ranuni(0)*60, 0.1);

    PowerIndex = round((Horsepower * 0.6 + Weight * 0.0005), 0.1);

    EcoScore = round((MPG_City * 0.4 + MPG_Highway * 0.6), 0.1);

    length LuxuryLevel $10;
    if MSRP > 50000 then LuxuryLevel = "High";
    else if MSRP > 30000 then LuxuryLevel = "Medium";
    else LuxuryLevel = "Low";

    drop Horsepower Weight MPG_City MPG_Highway MSRP;
run;






proc sql;
create table dummy_cars_final2 as
select region_type,score,region,cats(region_type,'(N=',count(*),')') as region_type2 from dummy_cars_final
 group by region_type
union
/*create a dummy "Truck-Europe"*/
select "Truck-Europe",score,region,"Truck-Europe(N=0)" from dummy_cars_final
 where region_type='Sedan-Europe'
;
quit;
proc freq data=dummy_cars_final2 noprint;
table region_type2/out=levels;
run;
data _null_;
set levels;
call symputx('_'||compress(scan(region_type2,1,'()'),,'kad') ,region_type2 );
run;



proc template;
   define statgraph boxplot_template;
      begingraph/ATTRPRIORITY=NONE ;

         discreteattrmap name="comboMap" / ignorecase=true;
		    value "&_SUVAsia" / lineattrs=(color=orange ) markerattrs=(color=blue symbol=diamond) ;
            value "&_SUVEurope" / lineattrs=(color=magenta)  markerattrs=(color=blue symbol=diamond);
			value "&_SUVUSA" / lineattrs=(color=grey)   markerattrs=(color=blue symbol=diamond);
            value "&_SedanAsia" / lineattrs=(color= orange) markerattrs=(color=green symbol=circle) ;
            value "&_SedanEurope" / lineattrs=(color=magenta) markerattrs=(color=green symbol=circle);
            value "&_SedanUSA" / lineattrs=(color=grey) markerattrs=(color=green symbol=circle);
            value "&_TruckAsia" / lineattrs=(color= orange) markerattrs=(color=red symbol=plus);
			value "&_TruckEurope" / lineattrs=(color=white) markerattrs=(color=white symbol=plus);
			value "&_TruckUSA" / lineattrs=(color=grey) markerattrs=(color=red symbol=plus);

         enddiscreteattrmap;




         discreteattrvar attrvar=patgroup var=region_type2 attrmap='comboMap';

         layout lattice / rows=2 columns=1 columndatarange=union ROWWEIGHTS=(.75 .25) ;
            layout overlay / 
               xaxisopts=(label="Region" labelattrs=(size=12pt weight=bold)
                          tickvalueattrs=(size=12pt weight=bold))
               yaxisopts=(offsetmin=0.05 offsetmax=0.05 label="Rating Score"  
                          linearopts=(tickvaluesequence=(start=0 end=100 increment=10)));

               boxplot x=region y=score /
                  name='BoxLegend'
                  group=patgroup 
                  groupdisplay=cluster 
                  boxwidth=0.6 clusterwidth=0.5 
                  display=( median mean caps fillpattern ) 
                  ;

annotate / id="BAR";

            endlayout;

			discretelegend 'BoxLegend' / 
			      border=false 
			      valueattrs=(size=8pt weight = bold) 
			      across=3 location=inside valign=top  ;
            endlayout;

      endgraph;
   end;
run;

proc template;
   define style styles.mypatterns;
      parent=styles.listing;
      style GraphData1 from GraphData1 / fillpattern="R1" ;
      style GraphData2 from GraphData2 / fillpattern="X1" ;
      style GraphData3 from GraphData3 / fillpattern="L1" ;
      style GraphData4 from GraphData4 / fillpattern="R1" ;
      style GraphData5 from GraphData5 / fillpattern="X1" ;
      style GraphData6 from GraphData6 / fillpattern="L1" ;
      style GraphData7 from GraphData7 / fillpattern="R1" ;
      style GraphData8 from GraphData8 / fillpattern="X1" ;
      style GraphData9 from GraphData9/ fillpattern="L1" ;
   end;
run;



/*Mask the  dummy "Truck-Europe" box and legend*/
%sganno
data sganno;
 %SGRECTANGLE(ID="BAR", X1=52,Y1=10,HEIGHT=4,WIDTH=15,DRAWSPACE="GRAPHPERCENT" ,FILLCOLOR="white" ,DISPLAY="FILL",FILLTRANSPARENCY=0 )

run;


options orientation=landscape;
ods rtf file="c:\temp\boxplot_grouped.rtf" style=mypatterns;
ods graphics / reset width=8.5in height=5.5in border=off outputfmt=png;

proc sgrender data=Dummy_cars_final2 template=boxplot_template sganno=sganno;
run;

ods rtf close;

2) Yes. These two macros are sas built-in macro ,no need to download and complie them any more.

The attempt of these two macro is to mask the dummy data ' Truck-Europe', if you don't have it, you would got

Here is doc(it is very powerful tool for statistic graphics)

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/grstatproc/n0eben23mwnl3dn1cm95zbks0eea.htm

p.s. to be honest, it is really not easy for me to solve such kind of GTL question.

[StudentSASLearn]

Thank you for clarifying all my questions. You are diamond filled with sas knowledge. I really appreciate your time .

[StudentSASLearn]

Thank you again @Ksharp . Adding Dan, if we can refine it, I can see a lot of responses from him, while searching here for the answer

@DanH_sas  can you please help.

1. Is there a way I can keep colors of the Bars remains same, but change the patterns based on the 'Cartype' Like Asia (orange)-- and three 'Cartypes' have three different patterns, Europe is Pink and have same pattern as Asian Cartypes.

2. Create a Discreet legend where all Asia comes in one column, Europe comes in one  column and same for USA.

Thank you for your help

[Ksharp]

For you first question, you could change pattern as this:

proc template;
   define style styles.mypatterns;
      parent=styles.listing;
      style GraphData1 from GraphData1 / fillpattern="R1" ;
      style GraphData2 from GraphData2 / fillpattern="R1" ;
      style GraphData3 from GraphData3 / fillpattern="R1" ;
      style GraphData4 from GraphData4 / fillpattern="X1" ;
      style GraphData5 from GraphData5 / fillpattern="X1" ;
      style GraphData6 from GraphData6 / fillpattern="X1" ;
      style GraphData7 from GraphData7 / fillpattern="L1" ;
      style GraphData8 from GraphData8 / fillpattern="L1" ;
      style GraphData9 from GraphData9/ fillpattern="L1" ;
   end;
run;

For your second question, I also would like to know @DanH_sas   and @tc  have more trick way to manipulate the legend item of box plot.

[StudentSASLearn]

Thank you @Ksharp . I think when we try to change the group or color, the values change

. I don't know enough, but the value for the TRUCK-ASIA  seems bar length changed in different versions.

[Ksharp]

That is because you are using simulation data by RAND() funtion:
Score = round(40 + ranuni(0)*60, 0.1);
Therefore, each time you are running code,you would get different data and would see different length of box .

[StudentSASLearn]

Oh completely forgot that. Sorry.  Thank you for point out. Thought it was taking the different grouping into the boxplot for some reason. I can not thank you enough.🙏

[DanH_sas]

As an alternative to using a legend, what about associating the N values directly with the boxes?

proc sgpanel data=sashelp.cars;
    panelby origin / layout=columnlattice onepanel novarname uniscale=row proportional;
    vbox horsepower / category=type displaystats=(n);
run;

[StudentSASLearn]

Thank you @DanH_sas  for your response.  I think @Ksharp  answered a similar question about displaying the N in response to another member.  I was thinking more of using proc template ( as I came so far into this, otherwise I am happy with using SGPLOT or PANEL).  I think at least I am stuck at bringing the same color legends into one column without affecting the graph.

[Ksharp]

DanH,
I would like to know how to manipulate the legend item of box plot with pattern ,and is unable to use LEGENDITEM statement(no pattern legend item).