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;
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;
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;
@Ksharp : Thankyou for the reply. Please can you explain why x1=_n_*100/16 +2; and why y1=100 shift the values so high
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;
The colaxistable is not giving the correct values.
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;
Yes, this looks better, how can the space between the colaxisatable and the bars be reduced?
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.