topic Re: Ordering Gchart Columns in Graphics Programming

Ordering Gchart Columns

DavidPhillips2 — Fri, 27 Mar 2015 19:26:03 GMT

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

Re: Ordering Gchart Columns

DanH_sas — Fri, 27 Mar 2015 19:42:27 GMT

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;

run;

proc gchart data=test;

format g gfmt.;

vbar c / sumvar=r discrete group=g;

run;

Re: Ordering Gchart Columns

DanH_sas — Fri, 27 Mar 2015 20:19:32 GMT

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;

run;

axis1 order=(2 3 1);

proc gchart data=test;

vbar c / sumvar=r discrete group=g gaxis=axis1;

run;

Re: Ordering Gchart Columns

DavidPhillips2 — Mon, 30 Mar 2015 12:51:05 GMT

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.

Re: Ordering Gchart Columns

DavidPhillips2 — Mon, 30 Mar 2015 14:49:26 GMT

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?

Re: Ordering Gchart Columns

DavidPhillips2 — Mon, 30 Mar 2015 14:58:25 GMT

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.

Re: Ordering Gchart Columns

DavidPhillips2 — Mon, 30 Mar 2015 16:49:56 GMT

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.

Re: Ordering Gchart Columns

DavidPhillips2 — Mon, 30 Mar 2015 17:20:40 GMT

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;

Re: Ordering Gchart Columns

DanH_sas — Mon, 30 Mar 2015 17:48:03 GMT

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.

Re: Ordering Gchart Columns

DavidPhillips2 — Mon, 30 Mar 2015 18:02:30 GMT

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