How to exclude missing values in hbarparam of sgplot

[MFraga]

/*Dear all, how would you exclude missing values in a hbarparam graphic ?*/

/* Here is my data */

data have;

input code region $ 3-16 growth_rate year;

datalines;

0 North America -5.92 2020

0 North America -2.18 2021

0 North America -7.47 2022

0 North America -2.17 2023

0 North America -9.64 2024

1 South America 0.36 2020

1 South America 2.69 2021

1 South America -8.13 2022

1 South America 1.22 2023

1 South America -1.34 2024

2 Asia          -1.53 2020

2 Asia          2.18 2021

2 Asia          5.64 2022

2 Asia          6.80 2023

2 Asia          8.70 2024

3 Europe        -9.68 2020

3 Europe        3.91 2021

3 Europe        5.48 2022

3 Europe        3.52 2023

3 Europe        3.69 2024

4 Africa        3.29 2020

4 Africa        7.97 2021

4 Africa        2.48 2022

4 Africa        7.36 2023

4 Africa        1.31 2024

5 Oceania       8.00 2020

5 Oceania       -6.05 2021

5 Oceania       6.91 2022

5 Oceania       -1.09 2023

5 Oceania       -7.46 2024

;

run;

/* First, I want to determine the order in which the values will be displayed */
proc sort data=have(where=(year=2024)) out=temp;
by descending growth_rate ;
run;

data fmt;
set temp(rename=(region=start));
retain fmtname 'fmt' type 'c';
length label $ 80;
label=repeat(' ',_n_)||strip(start);
keep fmtname type start label;
run;

proc format cntlin=fmt;
run;


data have2;
set have;
_region=put(region,$fmt32.);
run;


/* Now, I create my range attribute map to customize the bar colors */
data dattrmap5;
infile cards truncover;
input id $ value $ fillcolor $ textfamily &$40. textcolor $ textstyle $ textweight $;
datalines;
year1 2020 cxf7ebde
year1 2021 cxefdbc6
year1 2022 cxe1ca9e
year1 2023 cxd6ae6b
year1 2024 cxb4771f
year2 2020 cxdeebf7
year2 2021 cxc6dbef
year2 2022 cx9ecae1
year2 2023 cx6baed6
year2 2024 cx1f77b4
code 0 . Times New Roman Uni red Italic Bold
code 1 . Arial blue Normal Normal
code 2 . Arial blue Normal Normal
code 3 . Arial blue Normal Normal
code 4 . Arial blue Normal Normal
code 5 . Arial blue Normal Normal
;

data have3;
set have2;
if strip(_region)="North America" then do;region1=_region;year1=year;end;
else do;region2=_region;year2=year;end;
run;
proc sort data=have3;
by _region year;
run;

/*Next, I apply different fonts to format the different levels of the '_region'
variable and the values that appear next to each bar by including the option 'datalabelfitpolicy=none'*/

/*However, I did not find a way to exclude to exclude missing values in the keylegend statement*/

/* As I want to just plot the legend for "North America",
I have tried to do this by using NAME option only for "year1"
of the attribute map and the option EXCLUDE=(" ") in keylegend.
However, I still see a gray square with "." (dot) as a legend for
the squares representing the hbarparam of "year1".

I also checked the SAS manual and there is no "nomissinggroup"
option for hbarparm.

I also tried using EXCLUDE = (""), EXCLUDE = ("."), EXCLUDE = (.), or EXCLUDE = (missing), but nothing seems to work.

/* Here is what I have tried*/
proc sgplot data=have3 dattrmap=dattrmap noautolegend;
title "Growth rate";

/*Here I use NAME to create the legend for year1*/

hbarparm category=region1 response=growth_rate /group=year1 attrid=year1

NAME="legend_year1"

groupdisplay=cluster
clusterwidth=0.9
datalabel datalabelfitpolicy=none
datalabelattrs=(size=8.5pt family='Times New Roman Uni' color=red style=Italic weight=bold)
barwidth=1 nooutline
transparency=0 dataskin=none;

/*Here I create the hbarparam for year2, but without the NAME option as I do not want to display values for year2 in the legend*/

hbarparm category=region2 response=growth_rate /group=year2 attrid=year2
groupdisplay=cluster
clusterwidth=0.9
datalabel datalabelfitpolicy=none
datalabelattrs=(size=8.5pt family='Arial' color=blue style=Normal weight=Normal)
barwidth=1 nooutline
transparency=0 dataskin=none;

xaxis display=(nolabel) min=-12 max=12;
yaxis display=(nolabel novalues) colorbands=even ;

yaxistable _region/nolabel y=_region position=left valueattrs=(size=10pt) textgroup=code textgroupid=code;

keylegend "legend_year1"/title="" position=bottom noborder
EXCLUDE=(" ") down=1 titleattrs=(size=8.5pt) valueattrs=(size=8.5pt) autoitemsize;

run;

Does anyone have an idea of how to solve this situation ? Thank you once again for your help!

[DanH_sas]

In this situation, you can take advantage of the SHOW column in the discrete attributes map. If you set it to ATTRMAP, this will make the plot that uses that attrmap to report the attrmap content to the legend instead of the data. This is useful for when you want a legend to always have certain values, regardless of whether all the group values are there.

/* Now, I create my range attribute map to customize the bar colors */
data dattrmap;
infile cards truncover;
input id $ value $ show $ fillcolor $ textfamily &$40. textcolor $ textstyle $ textweight $;
datalines;
year1 2020 attrmap cxf7ebde
year1 2021 attrmap cxefdbc6
year1 2022 attrmap cxe1ca9e
year1 2023 attrmap cxd6ae6b
year1 2024 attrmap cxb4771f
year2 2020 . cxdeebf7
year2 2021 . cxc6dbef
year2 2022 . cx9ecae1
year2 2023 . cx6baed6
year2 2024 . cx1f77b4
code 0 . . Times New Roman Uni red Italic Bold
code 1 . . Arial blue Normal Normal
code 2 . . Arial blue Normal Normal
code 3 . . Arial blue Normal Normal
code 4 . . Arial blue Normal Normal
code 5 . . Arial blue Normal Normal
;
run;

[MFraga]

Thanks for the information about the show column. I will read more about what are the other atributes that I can integrate in my graph.

[Ksharp · Accepted solution]

/* Here is my data */
data have;
input code region $ 3-16 growth_rate year;
datalines;
0 North America -5.92 2020
0 North America -2.18 2021
0 North America -7.47 2022
0 North America -2.17 2023
0 North America -9.64 2024
1 South America 0.36 2020
1 South America 2.69 2021
1 South America -8.13 2022
1 South America 1.22 2023
1 South America -1.34 2024
2 Asia          -1.53 2020
2 Asia          2.18 2021
2 Asia          5.64 2022
2 Asia          6.80 2023
2 Asia          8.70 2024
3 Europe        -9.68 2020
3 Europe        3.91 2021
3 Europe        5.48 2022
3 Europe        3.52 2023
3 Europe        3.69 2024
4 Africa        3.29 2020
4 Africa        7.97 2021
4 Africa        2.48 2022
4 Africa        7.36 2023
4 Africa        1.31 2024
5 Oceania       8.00 2020
5 Oceania       -6.05 2021
5 Oceania       6.91 2022
5 Oceania       -1.09 2023
5 Oceania       -7.46 2024
;
run;

/* First, I want to determine the order in which the values will be displayed */
proc sort data=have(where=(year=2024)) out=temp;
by descending growth_rate ;
run;

data fmt;
set temp(rename=(region=start));
retain fmtname 'fmt' type 'c';
length label $ 80;
label=repeat(' ',_n_)||strip(start);
keep fmtname type start label;
run;

proc format cntlin=fmt;
run;


data have2;
set have;
_region=put(region,$fmt32.);
run;


/* Now, I create my range attribute map to customize the bar colors */
data dattrmap;
infile cards truncover;
input id $ value $ fillcolor $ textfamily &$40. textcolor $ textstyle $ textweight $;
datalines;
year1 2020 cxf7ebde
year1 2021 cxefdbc6
year1 2022 cxe1ca9e
year1 2023 cxd6ae6b
year1 2024 cxb4771f
year2 2020 cxdeebf7
year2 2021 cxc6dbef
year2 2022 cx9ecae1
year2 2023 cx6baed6
year2 2024 cx1f77b4
code 0 . Times New Roman Uni   red Italic Bold
code 1 . Arial   blue Normal Normal
code 2 . Arial   blue Normal Normal
code 3 . Arial   blue Normal Normal
code 4 . Arial   blue Normal Normal
code 5 . Arial   blue Normal Normal
;


data have3;
set have2;
if strip(_region)="North America" then do;region1=_region;year1=year;end;
else do;region2=_region;year2=year;end;
run;
proc sort data=have3;
by _region year;
run;


/*Next, I apply different fonts to format the different levels of the '_region'
variable and the values that appear next to each bar by including the option 'datalabelfitpolicy=none'*/

 

/*However, I did not find a way to exclude to exclude missing values in the keylegend statement*/

 

/* As I want to just plot the legend for "North America",
I have tried to do this by using NAME option only for "year1"
of the attribute map and the option EXCLUDE=(" ") in keylegend.
However, I still see a gray square with "." (dot) as a legend for
the squares representing the hbarparam of "year1".

I also checked the SAS manual and there is no "nomissinggroup"
option for hbarparm.

I also tried using EXCLUDE = (""), EXCLUDE = ("."), EXCLUDE = (.), or EXCLUDE = (missing), but nothing seems to work.

 

/* Here is what I have tried*/
proc sgplot data=have3 dattrmap=dattrmap noautolegend;
title "Growth rate";

 

/*Here I use NAME to create the legend for year1*/

hbarparm category=region1 response=growth_rate /group=year1 attrid=year1

NAME="legend_year1"

groupdisplay=cluster
clusterwidth=0.9
datalabel datalabelfitpolicy=none
datalabelattrs=(size=8.5pt family='Times New Roman Uni' color=red style=Italic weight=bold)
barwidth=1 nooutline
transparency=0 dataskin=none;

/*Here I create the hbarparam for year2, but without the NAME option as I do not want to display values for year2 in the legend*/

hbarparm category=region2 response=growth_rate /group=year2 attrid=year2
groupdisplay=cluster
clusterwidth=0.9
datalabel datalabelfitpolicy=none
datalabelattrs=(size=8.5pt family='Arial' color=blue style=Normal weight=Normal)
barwidth=1 nooutline
transparency=0 dataskin=none;


xaxis display=(nolabel) min=-12 max=12;
yaxis display=(nolabel novalues) colorbands=even ;

yaxistable _region/nolabel y=_region position=left valueattrs=(size=10pt) textgroup=code textgroupid=code;


keylegend "legend_year1"/title="" position=bottom noborder
EXCLUDE=(".") down=1 titleattrs=(size=8.5pt) valueattrs=(size=8.5pt) autoitemsize;

run;