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()?
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;
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.
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;
This article is similar to what I am trying to accomplish but it does not work for me.
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?
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.
'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;
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.