I posted a thread that was similar to this before on ordering subgroups with gchart. (https://communities.sas.com/thread/63666) I am trying to do simple ordering of gchart columns using a non-alphabetical ordering. With other chart types like sgpanel I can just use format commands and in values. With gchart this does not seem to work. Do I need to use the order command? Is there an easier explanation of this than http://support.sas.com/kb/41/603.html. it’s really hard to follow.
The code I am playing with is.
proc sql;
CREATE TABLE testTable(
Units int,
Name varchar(50)
);
INSERT INTO TestTable (Units, Name)
VALUES (11, 'a last');
INSERT INTO TestTable (Units, Name)
VALUES (23, 'b first');
INSERT INTO TestTable (Units, Name)
VALUES (4, 'c middle');
quit;
%Stpbegin;
title1 ls=1.5 "Test";
axis1 label=none;
axis2 label=none;
axis3 label=none;
proc gchart data=testTable;
vbar Name /
space=0
gspace=1
type=sum
outside=sum
sumvar=Units
discrete
gaxis=axis1 maxis=axis2 raxis=axis3
legend=legend1;
run; Quit;
%Stpend;
The columns should display b first, c middle, a last
Yes, ORDER can be used as well:
data test;
do g=1 to 3;
do c=1 to 3;
r=ranuni(123) * 100;
output;
end;
end;
run;
axis1 order=(2 3 1);
proc gchart data=test;
vbar c / sumvar=r discrete group=g gaxis=axis1;
run;
To create an arbitrary order for gchart, use a numeric "tag" columns and a format to turn the values back to the real group values. Here is a simple example:
proc format;
value gfmt 1="b"
2="a"
3="c"
;
run;
data test;
do g=1 to 3;
do c=1 to 3;
r=ranuni(123) * 100;
output;
end;
end;
run;
proc gchart data=test;
format g gfmt.;
vbar c / sumvar=r discrete group=g;
run;
Yes, ORDER can be used as well:
data test;
do g=1 to 3;
do c=1 to 3;
r=ranuni(123) * 100;
output;
end;
end;
run;
axis1 order=(2 3 1);
proc gchart data=test;
vbar c / sumvar=r discrete group=g gaxis=axis1;
run;
It looks like invalue does not work with gchart. The order clause is more practical for me. Dan, thanks for your reply to my post I was really having a hard time with the formatting.
When I try to assign the order clause to
order=(
"Baccalaureate" "Master''s" "Doctoral - Research / Scholarship" "Doctoral - Professional Practice (First Professional)" "Undergraduate Post-Baccalaureate Certificate" "Graduate Post-Baccalaureate Certificate" "Graduate Post-Master''s Certificate" "Undergraduate Non-degree Seeking" "Graduate Non-degree Seeking");
Every column with a ‘ – ( / ) displays a zero value. Is there a work around for this?
It looks like if SAS can’t find the column it displays the column with a zero not sure why these characters are causing SAS to be unable to find the column.
Some how
order=("Professional Practice (First professional)")
is considered to not the spelled the same as
Professional Practice (First professional)
due to the ‘spelling miss match the column is left blank. I've never run into an interpretation of ( ) before.
The order statement seems to be capped at a set number of characters per column. I’m not sure that that the special characters were the issue.
proc sql;
CREATE TABLE testTable(
Units int,
Name varchar(50)
);
INSERT INTO TestTable (Units, Name)
VALUES (11, 'a last');
INSERT INTO TestTable (Units, Name)
VALUES (23, 'Professiona (First professional)');
INSERT INTO TestTable (Units, Name)
VALUES (4, 'c middle');
quit;
%Stpbegin;
title1 ls=1.5 "Test";
axis2 order=("Professiona (First professional)" "c middle" "a last"); /*works*/
axis2 order=("Professional (First professional)" "c middle" "a last"); /does not work too long*/
proc gchart data=testTable;
vbar Name /
gaxis=axis1 maxis=axis2 raxis=axis3
legend=legend1;
run; Quit;
%Stpend;
David, I tried this in SAS 9.2 and got the same result as you. However, I tried this in SAS 9.3 and it worked fine. There might have been an issue that was fixed or a limit removed. You might want to contact Technical Support to get more details on what changed. The tag column/format trick I posted earlier should still work for you if cannot work around this string situation.
Dan, after finding out about the character limitation I added numeric counter Ids to all of my columns to go with the first solution you mentioned. I think the processing will be a little slower but the display should produce the desired output. Thanks for your help on this
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.