PROC SGPANEL - group colour issues when using a BY STATEMENT

[Mombi]

First of all, I apologise if a similar question has already been posted. I have tried searching answer to my problem in the available posts but couldn’t so here we go:

I have a simple dataset structured as follows:

data my_data;
input food_group energy_type $ sex select_var percent;
datalines;
111  EIEA  1  0  6.2
111  EIEA  1  1  2.7
111  EIEA  2  0  4.9
111  EIEA  2  1  1.5
111  TEI   1  0  5.8
111  TEI   1  1  2.6
111  TEI   2  0  4.8
111  TEI   2  1  1.5
112  EIEA  1  0  2.3
112  EIEA  1  1  0.9
112  EIEA  2  0  2.9
112  EIEA  2  1  2.4
...
;
run; 

I am using PROC SGPANEL to make some graphs. I would like to make the same graphs for the whole population and by sex. The PROC SGPANEL works fine for the whole population but I have issues with the group colours when I add a BY Statement.

As you can see on the graphs below, when I add a BY statement, it looks as if SAS runs out of colours and uses the same colours again for the remaining groups.

Here is the code I am using:

•  Without BY statement:

proc sgpanel data = my_data;
	format food_group RanksFmt. 	nutrient NutrientsFmt. 	select_var ynfmt. 	mean F4.1;
	panelby energy_type/ NoVarName;
	vbar select_var / response = mean 	group = food_group 	stat = sum 	DataLabel 	DataLabelAttrs = (weight = bold);
	colaxis display = (NoLabel);
	rowaxis grid label = "Percents";
run; 

Here is the output (no issues - each food group has its own colour 😞

•  With BY statement:

proc sort data  = my_data; by sex; run; 
proc sgpanel data = my_data; 
	by sex; 
	format food_group RanksFmt. 	select_var ynfmt. 	percent F4.1 	sex sexfmt.;
	panelby energy_type/ NoVarName;
	vbar select_var / response = percent 	group = food_group 	stat = sum 	DataLabel 	DataLabelAttrs = (weight = bold);
	colaxis display = (NoLabel);
	rowaxis grid label = "Percents";
run; 

Here is the output for sex = Male (same colours used for different groups - the same problem occurs for sex = Female):

I have tried sorting the data differently before using PROC SGPANEL with the BY statement (by food_group select_var sex or by sex select_var food_group, for instance) but it didn't change the outcome. I can probably solve all this by creating an attribute map but I would like to understand what is happening here. Any idea? 

I have attached the data, if needed.  I am on SAS 9.4M5.

[ballardw]

Please provide example data in the form of a working data step. Your data step has two issues. First the DATALINES statement needs a semicolon to end it, other wise the lines of data are part of the DATALINES statement and generate errors.

Second is the use of "smart quotes" or curly quotes as highlighted below:

111  ‘EIEA’  1  0  value

The curly quotes are treated as characters not delimiters as the quote from the key board is. So the above generates values with the quotes in them.

Third your input statement doesn't include instructions to read "value" as character so you get missing numeric values for the variable Percent. Even with dummy data the variable type and values should match.

Also to test your code, and possibly solve the problem, we need the definitions of the custom formats used for the variables Food_group, Nutrient and Select_var.

[Mombi]

 @ballardw, thank you for having a look at my problem. Sorry for the typo, I have edited my original post to add the missing semi-colon at the end of the DATALINES statement. I have also added some rounded numeric values for variable Percent so that the variable type and values match. If need be, the complete dataset is attached to my original post.

Here are the formats you requested:

proc format; 
	/** Yes / No **/
	value ynfmt .= "Missing data"  0 = "No"  1 = "Yes";
	/** Energy type **/
	value NutrientsFmt  2 = "Total Energy Intake"   3 = "Energy Intake w/o Alcohol" ;
	/** Food groups **/
	value RanksFmt	. = "Missing data" 
			111 = "red meat" 112 = "poultry" 113 = "game" 114 = "offals" 115 = "delicatessen" 116 = "fish" 
			121 = "milk" 122 = "yoghurt" 123 = "cheese" 124 = "other dairy" 
			130 = "eggs"
			200 = "other plant-based foods" 
			211 = "refined grains" 212 = "whole grains" 
			220 = "legumes" 
			230 = "nuts" 
			240 = "spices" 241 = "potatoes" 242 = "fruits" 243 = "vegetables";
run; 	

Thank you again for your help and do not hesitate if you need anything else 🙂