Getting inconsistent legends with proc sgplot

[vegan_renegade]

Hello all, this one is quite boggling to me. I'm using proc freq and proc sgplot to generate charts and graphs for a survey. The charts are fine- it's the graph legends that are inconsistent even though the code is identical except for the variables. As you will see, the legend in Q9c is correct and in logical order. However in the legend of Q10, the last two items are switched along with the colors. I need it to be just like Q9c's legend. I am using proc sql case when statements and a format to get the likert scale observations in the chart and legend items in the correct order (as shown in Q9c). Any ideas or am I overlooking a difference between both graph codes? In the code, just replace the path.

Thank you.

/*Imports Survey*/
OPTIONS VALIDMEMNAME=EXTEND;
PROC IMPORT OUT=HE_survey DATAFILE="PATH"		
DBMS=XLSX REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

/*Create ranks for likert scale*/
proc sql;
create table HE_survey1 as
select *,
case 

	when Q9c =  ""      	         then ""
	when Q9c =  "Strongly Agree"     then "1"
	when Q9c =  "Agree"              then "2"
	when Q9c =  "Neutral"            then "3"
	when Q9c =  "Disagree"           then "4"
	when Q9c =  "Strongly Disagree"  then "5"
	when Q9c =  "I don't know"       then "9"
	else "Check"
	end as Q9c_rank,

case
	when Q10 = ""                  then ""
	when Q10 = "Strongly Agree"    then "1"
	when Q10 = "Agree"             then "2"
	when Q10 = "Neutral"           then "3"
	when Q10 = "Disagree"          then "4"
	when Q10 = "Strongly Disagree" then "5"
	when Q10 = "I don't know"      then "9"
	else "Check"
	end as Q10_rank

	from HE_survey;
quit;

/*Creates likert scale format*/
proc format;
	value $likert

		""      = ""
		"1"     = "Strongly Agree"
		"2"     = "Agree"
		"3"     = "Neutral"
		"4"     = "Disagree"
		"5"     = "Strongly Disagree"
		"9"     = "I don't know"
		"Check" = "Check";
run;

/*********************************************************/
/*Q9c
/*********************************************************/

proc sort data=HE_survey1;
by Q9c_rank;
run;

/*Create freq chart*/
title "Q9c";
proc freq data=HE_survey1;
label Q1_Division = "Division";
label Q9c_rank = "Response";
tables Q1_Division*Q9c_rank / nocum norow nocol;
format Q9c_rank $likert.;
run;
title;

/*Create graph*/
title "Q9c";
proc sgplot data=HE_survey1;
vbar Q1_Division 
/
group=Q9c_rank
groupdisplay=cluster;
xaxis label="Division";
yaxis label="Number of Responses";
label Q9c_rank = "Response";
format Q9c_rank $likert.;
run;
title;

/*****************************************************************************/
/*Q10
/*****************************************************************************/

proc sort data=HE_survey1;
by descending Q10_rank;
run;

/*Create freq chart*/
title "Q10";
proc freq data=HE_survey1;
label Q1_Division = "Division";
label Q10_rank = "Response";
tables Q1_Division*Q10_rank / nocum norow nocol;
format Q10_rank $likert.;
run;
title;

/*Create graph*/
title "Q10";
proc sgplot data=HE_survey1;
vbar Q1_Division 
/
group=Q10_rank
groupdisplay=cluster;
xaxis label="Division";
yaxis label="Number of Responses";
label Q10_rank = "Response";
format Q10_rank $likert.;
run;
title;

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

(Not to mention that Sep 18 and 19 2020 such files are as I read this tagged with "Virus scan in progress" and can't be downloaded or viewed).

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Try adding a KEYLEGEND statement with the option SORTORDER=ASCENDING. Might work but character values sometimes don't quite order as expected.