Data visualization with SAS programming

Ordering Gchart Columns

Accepted Solution Solved
Reply
Super Contributor
Posts: 443
Accepted Solution

Ordering Gchart Columns

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


Accepted Solutions
Solution
‎03-27-2015 04:19 PM
SAS Super FREQ
Posts: 861

Re: Ordering Gchart Columns

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;

View solution in original post


All Replies
SAS Super FREQ
Posts: 861

Re: Ordering Gchart Columns

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;

Solution
‎03-27-2015 04:19 PM
SAS Super FREQ
Posts: 861

Re: Ordering Gchart Columns

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;

Super Contributor
Posts: 443

Re: Ordering Gchart Columns

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.

Super Contributor
Posts: 443

Re: Ordering Gchart Columns

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?

Super Contributor
Posts: 443

Re: Ordering Gchart Columns

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.

Super Contributor
Posts: 443

Re: Ordering Gchart Columns

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.

Super Contributor
Posts: 443

Re: Ordering Gchart Columns

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;

SAS Super FREQ
Posts: 861

Re: Ordering Gchart Columns

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.

Super Contributor
Posts: 443

Re: Ordering Gchart Columns

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

Post a Question
Discussion Stats
  • 9 replies
  • 590 views
  • 0 likes
  • 2 in conversation