Desktop productivity for business analysts and programmers

Reorder Bargraph made with sgplot

Accepted Solution Solved
Reply
Super Contributor
Posts: 494
Accepted Solution

Reorder Bargraph made with sgplot

I created a number of bar charts using sqplot and would like to reorder the subgroup to fit a non-alphabetic order. I tried the documentation that I found for reordering a gplot graph but it does not appear to work on a sgplot graph.

The syntax I am working with is legend order=('Undergraduate Non-degree Seeking').

There are other categories, however I’m working with just the first category first to see if it works.

The full order statement would be

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');

Do I need to use both legend order() value()?


Accepted Solutions
Solution
‎12-09-2014 04:13 PM
Super Contributor
Posts: 494

Re: Reorder Bargraph made with sgplot

Finally got it.  The examples online are so confusing this should be much cleaner.  I have to use an invalue because my string is long otherwise the invalue is not needed.  Mostly you need to have order and value as the same string e.g. order=(‘1’ ‘2’) value=(‘1’ ‘2’)

proc sql;

  CREATE TABLE testTable(

        ACADEMIC_PERIOD varchar(50),

        STUDENTS_ENROLLED int,

        STATUS varchar(50),

        DEGREE_LEVEL varchar(50)

   );

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 40, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 60, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 2, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

quit;

/*allow ordering*/

PROC Format;

  Invalue $dLvl

        'Doctoral - Research / Scholarship'='1'

  'Baccalaureate' = '2';

  value $dLvl

  '1'='Doctoral - Research / Scholarship'

        '2'='Baccalaureate';

      

RUN;

/*allow ordering*/

DATA testTable;

  SET testTable;

  degreeLevelRank = INPUT(degree_level,$dLvl.);

RUN;

%Stpbegin;

legend1 label=("Test")

/*order=(1 2);*/

order=(1 2) value=('Doctoral - Research / Scholarship' 'Baccalaureate');

/*order=('Baccalaureate' 'Master''s');*/

title1 ls=1.5 "Test";

axis1 label=none;

axis2 label=none;

axis3 label=none;

proc gchart data=testTable;

  hbar ACADEMIC_PERIOD /

  space=0

  gspace=1

  type=sum

  outside=sum

  sumlabel= 'Students Enrolled'

  sumvar=STUDENTS_ENROLLED

  group=Status

  subgroup=degreeLevelRank

  discrete

  gaxis=axis1 maxis=axis2 raxis=axis3

  legend=legend1;

  format degreeLevelRank $dLvl.;

run; Quit;

%Stpend;

View solution in original post


All Replies
Super User
Posts: 11,134

Re: Reorder Bargraph made with sgplot

Which version of SAS as changes are fairly rapid in the SG graphics?

It would help if you post could post some trial data in the form of a data step, dummy is fine, and the code you have tried so far.

Super Contributor
Posts: 494

Re: Reorder Bargraph made with sgplot

I just noticed an error in my wording I stated sgsplot I’m using gchart.

I am using SAS 9.2 and EG 5.1

legend2 label=(“Test”)

           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');

title1 ls=1.5 "Enrollment Headcount Summary";

                axis1 label=none;

                axis2 label=none value=none;

                axis3 label=none;

                proc gchart data=enrollment;

                     vbar &enrRepTyRank /

                           type=sum

                           space=0

                           gspace=1

                           sumvar=STUDENTS_ENROLLED

                           group=ACADEMIC_PERIOD_DESC

                           subgroup=&enrRepTyRank

                           discrete

                           gaxis=axis1 maxis=axis2 raxis=axis3

                           legend=legend2;

                run;

Super Contributor
Posts: 494

Re: Reorder Bargraph made with sgplot

This article is similar to what I am trying to accomplish but it does not work for me.

https://communities.sas.com/message/116761

Super Contributor
Posts: 494

Re: Reorder Bargraph made with sgplot

In this posting I’m noticing that the author uses both order() and value()

legend1 label=('Product Category') order=(&ord) value=(j=l &val); 

Why is the author using both?

http://support.sas.com/kb/41/603.html

Super User
Posts: 19,167

Re: Reorder Bargraph made with sgplot

Sample data and expected output would be useful.

The general method to re-order is to recode to numbers and use formats to display the values of interest.

Super Contributor
Posts: 494

Re: Reorder Bargraph made with sgplot

'Doctoral - Research / Scholarship' should be before 'Baccalaureate'

proc sql;

  CREATE TABLE testTable(

        ACADEMIC_PERIOD varchar(50),

        STUDENTS_ENROLLED int,

        STATUS varchar(50),

        DEGREE_LEVEL varchar(50)

   );

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Doctoral - Research / Scholarship');

quit;

/*allow ordering*/

PROC Format;

  Invalue $dLvl

        'Doctoral - Research / Scholarship'='1'

  'Baccalaureate' = '2';

  value $dLvl

  '1'='Doctoral - Research / Scholarship'

        '2'='Baccalaureate';

      

RUN;

/*allow ordering*/

DATA testTable;

  SET testTable;

  degreeLevelRank = INPUT(degree_level,$dLvl.);

RUN;

%Stpbegin;

legend1 label=("Test")

order=(1 2 3 4 5 6 7 8 9);

/*order=('Baccalaureate' 'Master''s');*/

title1 ls=1.5 "Test";

axis1 label=none;

axis2 label=none;

axis3 label=none;

proc gchart data=testTable;

  hbar ACADEMIC_PERIOD /

  space=0

  gspace=1

  type=sum

  outside=sum

  sumlabel= 'Students Enrolled'

  sumvar=STUDENTS_ENROLLED

  group=Status

  subgroup=degreeLevelRank

  discrete

  gaxis=axis1 maxis=axis2 raxis=axis3

  legend=legend1;

  format degreeLevelRank $dLvl.;

run; Quit;

%Stpend;

Solution
‎12-09-2014 04:13 PM
Super Contributor
Posts: 494

Re: Reorder Bargraph made with sgplot

Finally got it.  The examples online are so confusing this should be much cleaner.  I have to use an invalue because my string is long otherwise the invalue is not needed.  Mostly you need to have order and value as the same string e.g. order=(‘1’ ‘2’) value=(‘1’ ‘2’)

proc sql;

  CREATE TABLE testTable(

        ACADEMIC_PERIOD varchar(50),

        STUDENTS_ENROLLED int,

        STATUS varchar(50),

        DEGREE_LEVEL varchar(50)

   );

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 40, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 60, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 2, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('2', 20, 'fulltime', 'Baccalaureate');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

  INSERT INTO testTable (Academic_period, STUDENTS_ENROLLED, STATUS, DEGREE_LEVEL)

  VALUES ('1', 20, 'fulltime', 'Doctoral - Research / Scholarship');

quit;

/*allow ordering*/

PROC Format;

  Invalue $dLvl

        'Doctoral - Research / Scholarship'='1'

  'Baccalaureate' = '2';

  value $dLvl

  '1'='Doctoral - Research / Scholarship'

        '2'='Baccalaureate';

      

RUN;

/*allow ordering*/

DATA testTable;

  SET testTable;

  degreeLevelRank = INPUT(degree_level,$dLvl.);

RUN;

%Stpbegin;

legend1 label=("Test")

/*order=(1 2);*/

order=(1 2) value=('Doctoral - Research / Scholarship' 'Baccalaureate');

/*order=('Baccalaureate' 'Master''s');*/

title1 ls=1.5 "Test";

axis1 label=none;

axis2 label=none;

axis3 label=none;

proc gchart data=testTable;

  hbar ACADEMIC_PERIOD /

  space=0

  gspace=1

  type=sum

  outside=sum

  sumlabel= 'Students Enrolled'

  sumvar=STUDENTS_ENROLLED

  group=Status

  subgroup=degreeLevelRank

  discrete

  gaxis=axis1 maxis=axis2 raxis=axis3

  legend=legend1;

  format degreeLevelRank $dLvl.;

run; Quit;

%Stpend;

Super Contributor
Posts: 494

Re: Reorder Bargraph made with sgplot

Improving

http://support.sas.com/kb/41/603.html

Two problems, I lost the tooltip when sorting and the processing is a little high when sorting. Any suggestions on how this could be more efficient in 9.2?

/*proc print data=sashelp.shoes double;

  var region sales product;

  run;*/

proc sql;

  CREATE TABLE shoes(

        region varchar(50),

        sales int,

        product varchar(50),

    product_id int

   );

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 10, 'A', 3);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 200, 'B', 2);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 60, 'B', 2);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 20, 'D', 1);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 20, 'D', 1);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 20, 'D', 1);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 20, 'D', 1);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 20, 'D', 1);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('1', 20, 'D', 1);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('2', 20,  'B', 2);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('2', 200, 'B', 2);

  INSERT INTO shoes (region, sales, product, product_id)

  VALUES ('2', 20, 'D', 1);

quit;

                    

proc sort data=shoes;                                                                                                                 

   by region product_id;                                                                                                 

run;

/* Calculate totals for region and product */                                                                                          

proc summary data=shoes noprint;                                                                                                 

   by region product_id;                                                                                                                          

   class product;                                                                                                                      

   var sales;                                                                                                                          

   output out=shoes1(where=(_type_=1)) sum=;                                                             

run;

                                                                                                                                       

/* Within region (the midpoint) make highest sales come first */                                                                       

proc sort data=shoes1;                                                                                                                 

   by region product_id sales;                                                                                                 

run;                                                                                                                                   

/* The code below generates the default results. */

title1 'Default Graph of Sales Totals';

legend1 label=('Product Category') ;

                                                                     

pattern1 color=CX66A5A0 value=l3;      

pattern2 color=CX7C95CA value=solid; 

pattern3 color=CX94BDE1 value=solid; 

pattern4 color=CXDE7E6F value=solid; 

pattern5 color=CXA9865B value=r3; 

pattern6 color=CXBABC5C value=solid; 

pattern7 color=CXB689CD value=solid; 

pattern8 color=CXCD7BA1 value=solid;

                                                                                                                                                                                                                                                                                  

proc gchart data=shoes1;                                                                                                               

   vbar region / sumvar=sales subgroup=product legend=legend1                                                                             

                 maxis=axis1 raxis=axis2 space=.5;                                                                                     

run;                                                                                                                                   

quit;

/* The code below reorders the subgroups. */

goptions reset=all cback=white border htitle=12pt htext=10pt; 

                                                                                                                                       

/* Create a RANK variable.  This will be used as the SUMVAR=  */                                                                       

/* variable to ensure that the product with the highest sales */                                                                       

/* is drawn at the bottom of each bar.                        */                                                                       

                                                                                                                                       

/* Assign a color and pattern value for each product. */                                                                               

                                                                                                                                       

/* Assign color and pattern values to a macro variable.  Each   */                                                                     

/* observation creates one macro variable.  This ensures that   */                                                                     

/* each product has the same color and pattern across midpoints */                                                                     

/* even though the RANK value is different for each product.    */                                                                     

data shoes2;                                                                                                                           

   set shoes1 end=eof;                                                                                                                 

   by region product_id sales;                                                                                                 

   length color $20 value $5;                                                                                                          

                                                                                                                                       

   region = tranwrd(strip(region)," ","/");                                                                                            

   rank + 1;                                                                                                                           

                                                                                                                                       

   select;                                                                                                                             

      when (product='D') do;                                                                                                        

         color="CX66A5A0";                                                                                                             

         value="L3";                                                                                                                   

      end;                                                                                                                             

      when (product='B') do;                                                                                                      

         color="CXB689CD";                                                                                                             

         value="solid";                                                                                                                

      end;                                                                                                                             

      when (product='A') do;                                                                                                  

         color="CXCD7BA1";                                                                                                             

         value="solid";                                                                                                                

  end;

      otherwise;                                                                                                                       

   end;                                                                                                                                

                                                                                                                                       

   if eof then call symput('tot',trim(left(put(_n_,8.))));                                                                             

   call symput('pattern'||trim(left(put(_n_,8.))),'color=' || color || ' value=' || value ||';');                                      

run;                                                                                                                                   

                                                                                                                                       

/* Define and execute the macro to create the PATTERN statements */                                                                    

%macro pattern;                                                                                                                        

   %do j=1 %to &tot;                                                                                                                   

      pattern&j &&pattern&j;                                                                                                           

   %end;                                                                                                                               

%mend pattern;                                                                                                                         

                                                                                                                                       

%pattern;                                                                                                                              

                                                                                                                                       

/* Keep one observation for each product */                                                                                            

proc sort data=shoes2 out=unqshoes nodupkey;                                                                                           

   by product_id;                                                                                                                         

run;                                                                                                                                   

                                                                                                                                       

/* Create macro variables to use in the legend. */                                                                                     

/* ORD contains the list of ranks that will be  */                                                                                     

/* used in the ORDER= option.                   */                                                                                     

/* VAL contains the list of product names that  */                                                                                     

/* will be used in the VALUE= option.           */                                                                                     

data _null_;                                                                                                                           

   set unqshoes end=eof;                                                                                                               

   length ord val $200;                                                                                                                

   retain ord val ' ';                                                                                                                 

                                                                                                                                       

   ord = trim(left(ord)) || ' ' || trim(left(put(rank,8.)));                                                                           

   val = trim(left(val)) || ' "' || product || '"';                                                                                    

                                                                                                                                       

   if eof then do;                                                                                                                     

      call symput('ord', ord);                                                                                                         

      call symput('val', val);                                                                                                         

   end;                                                                                                                                

run;                                                                                                                                   

                                                                                                                                       

title1 "Reorder Subgroups Based on Product Id";                                                                                                                                                                              

legend1 label=('Product Category') order=(&ord) value=(j=l &val);                                                                      

                                                                                                                                                                                                                                                                                                                                                             

proc gchart data=shoes2;                                                                                                               

   vbar region / sumvar=sales subgroup=rank legend=legend1 type=sum space=.5;                                                                                     

run;                                                                                                                                   

quit;

Super User
Posts: 11,134

Re: Reorder Bargraph made with sgplot

If you are going to use PROC SUMMARY with CLASS variables there is no need to sort the data prior to processing and the output data by default will be sorted by the class variables.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 528 views
  • 7 likes
  • 3 in conversation