Solved: Re: using sgpanel to create a bar chart

Anita_n · Posted 09-05-2023 05:27 AM

Dear all I have this dataset:

data mydat;
	infile datalines;
	input year Therapy $15. group_T number_of_cases;
	datalines;
2020 CB_Ara	         1 2
2020 Transplantation 1 87
2020 Operation	     1 30
2020 Transfusion	 1 63
2020 Radiotherapy	 1 18
2020 Chemotherapy	 1 1
2021 Oral_PR	     1 1
2021 CB_Ara	         1 4
2021 Transplantation 1 52
2021 Operation	     1 16
2021 Transfusion	 1 41
2021 Radiotherapy	 1 5
2021 Chemotherapy	 1 3
2022 CB_Ara	         1 8
2022 Transplantation 1 69
2022 PreSur	         1 3
2022 Operation	     1 30
2022 W&W	         1 1
2022 Transfusion	 1 31
2022 Radiotherapy	 1 25
2022 Chemotherapy	 1 3
2020 Oral_PR	     2 4
2020 CB_Ara	         2 6
2020 Transplantation 2 21
2020 PreSur	         2 4
2020 Operation	     2 33
2020 Transfusion	 2 9
2020 Antibodytherapy 2 1
2020 Radiotherapy	 2 8
2020 Chemotherapy	 2 2
2020 Injectibles	 2 5
2021 Oral_PR	     2 3
2021 CB_Ara	         2 3
2021 Transplantation 2 8
2021 PreSur	         2 12
2021 Operation	     2 11
2021 TM-GH	         2 1
2021 Transfusion	 2 3
2021 adjuvant	     2 1
2021 Radiotherapy	 2 6
2021 abortive	     2 2
2021 Injectibles	 2 1
2022 Oral_PR	     2 2
2022 CB_Ara	         2 2
2022 Transplantation 2 7
2022 PreSur	         2 44
2022 Operation	     2 6
2022 W&W	         2 1
2022 TM-GH	         2 6
2022 Transfusion	 2 1
2022 Radiotherapy	 2 10
2022 abortive	     2 2
2022 Chemotherapy	 2 1
2020 Oral_PR	     3 1
2020 CB_Ara	         3 2
2020 Transplantation 3 4
2020 PreSur	         3 8
2020 Operation	     3 12
2020 W&W	         3 1
2020 Transfusion	 3 2
2020 Radiotherapy	 3 5
2020 abortive	     3 1
2020 Radiation	     3 1
2020 Chemotherapy	 3 3
2020 Injectibles	 3 6
2021 Oral_PR	     3 1
2021 CB_Ara	         3 3
2021 Transplantation 3 2
2021 PreSur	         3 11
2021 Operation	     3 4
2021 Transfusion	 3 1
2021 adjuvant	     3 1
2021 Radiotherapy	 3 1
2021 abortive	     3 2
2022 Oral_PR	     3 2
2022 CB_Ara	         3 4
2022 PreSur	         3 16
2022 Operation	     3 10
2022 W&W	         3 2
2022 TM-GH	         3 23
2022 Transfusion	 3 2
2022 adjuvant	     3 3
2022 Antibodytherapy 3 1
2022 Radiotherapy	 3 7
2022 abortive	     3 2
2020 Oral_PR	     4 1
2020 CB_Ara	         4 5
2020 PreSur	         4 4
2020 Operation	     4 10
2020 Radiotherapy	 4 4
2020 abortive	     4 1
2020 Chemotherapy	 4 2
2021 Transplantation 4 1
2021 PreSur	         4 9
2021 adjuvant	     4 2
2021 Radiotherapy	 4 3
2022 Oral_PR	     4 1
2022 PreSur	         4 7
2022 Operation	     4 4
2022 W&W	         4 1
2022 TM-GH	         4 16
2022 adjuvant	     4 7
2022 Radiotherapy	 4 5
2022 abortive	     4 3
2020 Oral_PR	     5 3
2020 CB_Ara	         5 1
2020 PreSur	         5 4
2020 Operation	     5 3
2020 W&W	         5 1
2020 adjuvant	     5 2
2020 Radiotherapy	 5 3
2020 abortive	     5 1
2020 Chemotherapy	 5 1
2021 W&W	         5 1
2021 TM-GH	         5 2
2021 Transfusion	 5 1
2021 adjuvant	     5 1
2021 Radiotherapy	 5 3
2021 abortive	     5 3
2021 Radiation	     5 1
2021 Chemotherapy	 5 2
2021 Injectibles	 5 1
2022 CB_Ara	         5 3
2022 Transplantation 5 1
2022 PreSur	         5 3
2022 Operation	     5 3
2022 TM-GH	         5 17
2022 adjuvant	     5 5
2022 Antibodytherapy 5 1
2022 Radiotherapy	 5 4
2022 abortive	     5 8
2022 Injectibles	 5 1
;
run;

proc sql;

create table sumdat as select year, group_T, sum(number_of_cases) as total  from mydat  group by group_T, year;

create table have as select a.*, b.total,  number_of_cases/total as percentage format percent6.0 from mydat as a, sumdat as b
where a.group_T=b.group_T and a.year=b.year;
quit;

I wish to create a barchart using sgpannel with three columns and display the percentage values. Also at the top of the bars the actual values (absolute values) of each bar should be displayed . Am using an annotate dataset for this labels at the top of the bars. Somehow the values are not displayed. I don't know why. I have being using this method with sgplot and have never had any problem. Probably this could be an issue with sgpanel. Please can someone help?

Here is my code:

*create annotate dataset;
proc sql noprint;
create table annodat as
select year,  group_T, sum(number_of_cases) as overall
from have group by year, group_T;
quit;

data myannodat; 
set annodat;
length label $100 anchor x1space y1space textweight $50;
layer="front";
function="text"; textcolor="black"; textsize=5; textweight='bold';
width=100; widthunit='percent'; 
label=trim(left(put(overall,comma8.0))); 
x1space='GRAPHPERCENT'; 
y1space='GRAPHPERCENT'; 
x1=group_T;
y1=100;
anchor='bottom'; 
run;

*create graph;

proc sgpanel data=have pad=(bottom=5%) sganno=myannodat noautolegend ;

panelby  year/ columns=3 novarname noborder headerattrs=(color=dimgray family=arial weight=bold size=7pt) nowall sort=data ;

vbarparm  category=group_T response=percentage / group=therapy groupdisplay=stack seglabel attrid=therapy missing grouporder=data name='b'; 

rowaxis grid display=(nolabel) valueattrs=(size=7pt family=arial color=dimgray)  offsetmin=0 ;

colaxis  display=(nolabel) discreteorder=data  valueattrs=(size=5pt family=arial color=dimgray) valuesrotate=diagonal2;

keylegend 'b'/linelength=10 position=bottom fillheight=1.5pct title="" valueattrs=(size=0.5pt family=arial) outerpad=(top=0.2cm) noborder;
run;

Ksharp · Posted 09-05-2023 08:34 AM

Here could get you the right result.


proc sql;

create table sumdat as select year, group_T, sum(number_of_cases) as total  from mydat  group by group_T, year;

create table have as select a.*, b.total,  number_of_cases/total as percentage format percent6.0 from mydat as a, sumdat as b
where a.group_T=b.group_T and a.year=b.year;
quit;

*create annotate dataset;
proc sql noprint;
create table annodat as
select *, sum(number_of_cases) as overall
from have group by year, group_T;
quit;

data annodat;
 set annodat;
by year group_t;
if not first.group_t then call missing(overall);
run;

*create graph;

proc sgpanel data=annodat pad=(bottom=5%)  noautolegend ;

panelby  year/ columns=3 novarname noborder headerattrs=(color=dimgray family=arial weight=bold size=7pt) nowall sort=data spacing=0 ; /*<-------*/

vbarparm  category=group_T response=percentage / group=therapy groupdisplay=stack seglabel attrid=therapy missing grouporder=data name='b'; 

rowaxis grid display=(nolabel) valueattrs=(size=7pt family=arial color=dimgray)  offsetmin=0 ; 


colaxistable overall/x=group_t  classdisplay=cluster position=top labelattrs=(color=white);  /*<-------*/


colaxis  display=(nolabel) discreteorder=data  valueattrs=(size=5pt family=arial color=dimgray) valuesrotate=diagonal2;

keylegend 'b'/linelength=10 position=bottom fillheight=1.5pct title="" valueattrs=(size=0.5pt family=arial) outerpad=(top=0.2cm) noborder;
run;

View solution in original post

Ksharp · Posted 09-05-2023 07:56 AM


data myannodat; 
set annodat;
length label $100 anchor x1space y1space textweight $50;
layer="front";
function="text"; textcolor="black"; textsize=8; textweight='bold';
label=trim(left(put(overall,comma8.0))); 
x1space='LAYOUTPERCENT';    /*<-------*/
y1space='LAYOUTPERCENT';  /*<-------*/
x1=_n_*100/16 +2;  /*<-------*/
y1=94;    /*<-------*/
anchor='center';   /*<-------*/
run;

*create graph;

proc sgpanel data=have pad=(bottom=5%) sganno=myannodat noautolegend ;

panelby  year/ columns=3 novarname noborder headerattrs=(color=dimgray family=arial weight=bold size=7pt) nowall sort=data spacing=0 ; /*<-------*/

vbarparm  category=group_T response=percentage / group=therapy groupdisplay=stack seglabel attrid=therapy missing grouporder=data name='b'; 

rowaxis grid display=(nolabel) valueattrs=(size=7pt family=arial color=dimgray)  offsetmin=0 offsetmax=0.05; /*<-------*/

colaxis  display=(nolabel) discreteorder=data  valueattrs=(size=5pt family=arial color=dimgray) valuesrotate=diagonal2;

keylegend 'b'/linelength=10 position=bottom fillheight=1.5pct title="" valueattrs=(size=0.5pt family=arial) outerpad=(top=0.2cm) noborder;
run;

Anita_n · Posted 09-05-2023 08:25 AM

@Ksharp : Thankyou for the reply. Please can you explain why x1=_n_*100/16 +2; and why y1=100 shift the values so high

Ksharp · Posted 09-05-2023 08:28 AM

Your x1 should be percent value like 10 20 30 ..... ,NOT 1 2 3, since x1space='GRAPHPERCENT';

"x1=_n_*100/16 +2;"
I test different value(percent) to get what you want. you can change it as your wish.

But I strongly suggest to use COLAXISTTABLE statement in my below post .

Ksharp · Posted 09-05-2023 08:24 AM

Or better using statement "colaxistable".

data mydat;
	infile datalines;
	input year Therapy $15. group_T number_of_cases;
	datalines;
2020 CB_Ara	         1 2
2020 Transplantation 1 87
2020 Operation	     1 30
2020 Transfusion	 1 63
2020 Radiotherapy	 1 18
2020 Chemotherapy	 1 1
2021 Oral_PR	     1 1
2021 CB_Ara	         1 4
2021 Transplantation 1 52
2021 Operation	     1 16
2021 Transfusion	 1 41
2021 Radiotherapy	 1 5
2021 Chemotherapy	 1 3
2022 CB_Ara	         1 8
2022 Transplantation 1 69
2022 PreSur	         1 3
2022 Operation	     1 30
2022 W&W	         1 1
2022 Transfusion	 1 31
2022 Radiotherapy	 1 25
2022 Chemotherapy	 1 3
2020 Oral_PR	     2 4
2020 CB_Ara	         2 6
2020 Transplantation 2 21
2020 PreSur	         2 4
2020 Operation	     2 33
2020 Transfusion	 2 9
2020 Antibodytherapy 2 1
2020 Radiotherapy	 2 8
2020 Chemotherapy	 2 2
2020 Injectibles	 2 5
2021 Oral_PR	     2 3
2021 CB_Ara	         2 3
2021 Transplantation 2 8
2021 PreSur	         2 12
2021 Operation	     2 11
2021 TM-GH	         2 1
2021 Transfusion	 2 3
2021 adjuvant	     2 1
2021 Radiotherapy	 2 6
2021 abortive	     2 2
2021 Injectibles	 2 1
2022 Oral_PR	     2 2
2022 CB_Ara	         2 2
2022 Transplantation 2 7
2022 PreSur	         2 44
2022 Operation	     2 6
2022 W&W	         2 1
2022 TM-GH	         2 6
2022 Transfusion	 2 1
2022 Radiotherapy	 2 10
2022 abortive	     2 2
2022 Chemotherapy	 2 1
2020 Oral_PR	     3 1
2020 CB_Ara	         3 2
2020 Transplantation 3 4
2020 PreSur	         3 8
2020 Operation	     3 12
2020 W&W	         3 1
2020 Transfusion	 3 2
2020 Radiotherapy	 3 5
2020 abortive	     3 1
2020 Radiation	     3 1
2020 Chemotherapy	 3 3
2020 Injectibles	 3 6
2021 Oral_PR	     3 1
2021 CB_Ara	         3 3
2021 Transplantation 3 2
2021 PreSur	         3 11
2021 Operation	     3 4
2021 Transfusion	 3 1
2021 adjuvant	     3 1
2021 Radiotherapy	 3 1
2021 abortive	     3 2
2022 Oral_PR	     3 2
2022 CB_Ara	         3 4
2022 PreSur	         3 16
2022 Operation	     3 10
2022 W&W	         3 2
2022 TM-GH	         3 23
2022 Transfusion	 3 2
2022 adjuvant	     3 3
2022 Antibodytherapy 3 1
2022 Radiotherapy	 3 7
2022 abortive	     3 2
2020 Oral_PR	     4 1
2020 CB_Ara	         4 5
2020 PreSur	         4 4
2020 Operation	     4 10
2020 Radiotherapy	 4 4
2020 abortive	     4 1
2020 Chemotherapy	 4 2
2021 Transplantation 4 1
2021 PreSur	         4 9
2021 adjuvant	     4 2
2021 Radiotherapy	 4 3
2022 Oral_PR	     4 1
2022 PreSur	         4 7
2022 Operation	     4 4
2022 W&W	         4 1
2022 TM-GH	         4 16
2022 adjuvant	     4 7
2022 Radiotherapy	 4 5
2022 abortive	     4 3
2020 Oral_PR	     5 3
2020 CB_Ara	         5 1
2020 PreSur	         5 4
2020 Operation	     5 3
2020 W&W	         5 1
2020 adjuvant	     5 2
2020 Radiotherapy	 5 3
2020 abortive	     5 1
2020 Chemotherapy	 5 1
2021 W&W	         5 1
2021 TM-GH	         5 2
2021 Transfusion	 5 1
2021 adjuvant	     5 1
2021 Radiotherapy	 5 3
2021 abortive	     5 3
2021 Radiation	     5 1
2021 Chemotherapy	 5 2
2021 Injectibles	 5 1
2022 CB_Ara	         5 3
2022 Transplantation 5 1
2022 PreSur	         5 3
2022 Operation	     5 3
2022 TM-GH	         5 17
2022 adjuvant	     5 5
2022 Antibodytherapy 5 1
2022 Radiotherapy	 5 4
2022 abortive	     5 8
2022 Injectibles	 5 1
;
run;

proc sql;

create table sumdat as select year, group_T, sum(number_of_cases) as total  from mydat  group by group_T, year;

create table have as select a.*, b.total,  number_of_cases/total as percentage format percent6.0 from mydat as a, sumdat as b
where a.group_T=b.group_T and a.year=b.year;
quit;

*create annotate dataset;
proc sql noprint;
create table annodat as
select *, sum(number_of_cases) as overall
from have group by year, group_T;
quit;

*create graph;

proc sgpanel data=annodat pad=(bottom=5%)  noautolegend ;

panelby  year/ columns=3 novarname noborder headerattrs=(color=dimgray family=arial weight=bold size=7pt) nowall sort=data spacing=0 ; /*<-------*/

vbarparm  category=group_T response=percentage / group=therapy groupdisplay=stack seglabel attrid=therapy missing grouporder=data name='b'; 

rowaxis grid display=(nolabel) valueattrs=(size=7pt family=arial color=dimgray)  offsetmin=0 ; 


colaxistable overall/x=group_t  classdisplay=cluster position=top labelattrs=(color=white);  /*<-------*/


colaxis  display=(nolabel) discreteorder=data  valueattrs=(size=5pt family=arial color=dimgray) valuesrotate=diagonal2;

keylegend 'b'/linelength=10 position=bottom fillheight=1.5pct title="" valueattrs=(size=0.5pt family=arial) outerpad=(top=0.2cm) noborder;
run;

Anita_n · Posted 09-05-2023 08:34 AM

The colaxistable is not giving the correct values.

Ksharp · Posted 09-05-2023 08:34 AM

Here could get you the right result.


proc sql;

create table sumdat as select year, group_T, sum(number_of_cases) as total  from mydat  group by group_T, year;

create table have as select a.*, b.total,  number_of_cases/total as percentage format percent6.0 from mydat as a, sumdat as b
where a.group_T=b.group_T and a.year=b.year;
quit;

*create annotate dataset;
proc sql noprint;
create table annodat as
select *, sum(number_of_cases) as overall
from have group by year, group_T;
quit;

data annodat;
 set annodat;
by year group_t;
if not first.group_t then call missing(overall);
run;

*create graph;

proc sgpanel data=annodat pad=(bottom=5%)  noautolegend ;

panelby  year/ columns=3 novarname noborder headerattrs=(color=dimgray family=arial weight=bold size=7pt) nowall sort=data spacing=0 ; /*<-------*/

vbarparm  category=group_T response=percentage / group=therapy groupdisplay=stack seglabel attrid=therapy missing grouporder=data name='b'; 

rowaxis grid display=(nolabel) valueattrs=(size=7pt family=arial color=dimgray)  offsetmin=0 ; 


colaxistable overall/x=group_t  classdisplay=cluster position=top labelattrs=(color=white);  /*<-------*/


colaxis  display=(nolabel) discreteorder=data  valueattrs=(size=5pt family=arial color=dimgray) valuesrotate=diagonal2;

keylegend 'b'/linelength=10 position=bottom fillheight=1.5pct title="" valueattrs=(size=0.5pt family=arial) outerpad=(top=0.2cm) noborder;
run;

Anita_n · Posted 09-05-2023 10:03 AM

Yes, this looks better, how can the space between the colaxisatable and the bars be reduced?

Ksharp · Posted 09-06-2023 08:01 AM

Could try options:
offsetmax=0
pad=0px

rowaxis grid display=(nolabel) valueattrs=(size=7pt family=arial color=dimgray) offsetmin=0 offsetmax=0 ;
colaxistable overall/x=group_t classdisplay=cluster position=top labelattrs=(color=white) pad=0px ;

SAS Innovate 2025: Call for Content

Classroom Training Available!