SAS script to create stack bar chart

[TienHan83]

Hi experts

, 

I'm new to SAS programming and was tasked to create something close or similar as below based on the sample data attached. 

Tried using proc sgplot but i think it is not so straight forward... >.<

Need guidance on this please, thanks!

[ballardw]

Show the code you tried.

The picture you show is missing any explanation for why there are three groups of vertical bars for what I have to assume are each region. So that needs explanation.

And XLSX is not data, it is a file that we need to convert to SAS data and the way I do that may not make something that is the same your data. Provide data in the form of data step code. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

[Rick_SAS]

It looks like you are trying to create a bar chart where each bar indicates the proportion of subgroups and therefore sums to 100%. See the discussion and example in the article, "Construct a stacked bar chart in SAS where each bar equals 100%"

However, you have an extra classification variable (region_id) so you will need to use PROC SGPANEL and use PANELBY region_id.

Here is a program that should get you started. I used PROC FORMAT to code the score bands. If necessary, you can use the SEGLABEL option on the VBAR statement to add percentages labels to the bars. You can use the XAXISTABLE statement to add counts for the years.

proc format;
value ScoreFmt  
      1 = " 0-20"
      2 = "21-40"
      3 = "41-60" 
      4 = "61-80"
      5 = "81-100"; 
run;


data Have;
format score_bands ScoreFmt.;
length region_id $6;
input region_id	year	score_bands	COUNT_of_score_bands	total	distribution_pct;
datalines;
GLOBAL	2017	01	119	7223	1.65
GLOBAL	2017	02	473	7223	6.55
GLOBAL	2017	03	1514	7223	20.96
GLOBAL	2017	04	3283	7223	45.45
GLOBAL	2017	05	1834	7223	25.39
GLOBAL	2018	01	127	6983	1.82
GLOBAL	2018	02	459	6983	6.57
GLOBAL	2018	03	1448	6983	20.74
GLOBAL	2018	04	3190	6983	45.68
GLOBAL	2018	05	1759	6983	25.19
GLOBAL	2019	01	90	6416	1.40
GLOBAL	2019	02	339	6416	5.28
GLOBAL	2019	03	1249	6416	19.47
GLOBAL	2019	04	3076	6416	47.94
GLOBAL	2019	05	1662	6416	25.90
RGN_1	2017	01	55	1318	4.17
RGN_1	2017	02	167	1318	12.67
RGN_1	2017	03	412	1318	31.26
RGN_1	2017	04	474	1318	35.96
RGN_1	2017	05	210	1318	15.93
RGN_1	2018	01	38	998	3.81
RGN_1	2018	02	114	998	11.42
RGN_1	2018	03	298	998	29.86
RGN_1	2018	04	353	998	35.37
RGN_1	2018	05	195	998	19.54
RGN_1	2019	01	32	856	3.74
RGN_1	2019	02	105	856	12.27
RGN_1	2019	03	235	856	27.45
RGN_1	2019	04	338	856	39.49
RGN_1	2019	05	146	856	17.06
RGN_2	2017	01	1	1188	0.08
RGN_2	2017	02	37	1188	3.11
RGN_2	2017	03	219	1188	18.43
RGN_2	2017	04	692	1188	58.25
RGN_2	2017	05	239	1188	20.12
RGN_2	2018	01	2	1135	0.18
RGN_2	2018	02	30	1135	2.64
RGN_2	2018	03	200	1135	17.62
RGN_2	2018	04	664	1135	58.50
RGN_2	2018	05	239	1135	21.06
RGN_2	2019	01	6	1139	0.53
RGN_2	2019	02	26	1139	2.28
RGN_2	2019	03	169	1139	14.84
RGN_2	2019	04	703	1139	61.72
RGN_2	2019	05	235	1139	20.63
RGN_3	2017	01	46	2579	1.78
RGN_3	2017	02	165	2579	6.40
RGN_3	2017	03	467	2579	18.11
RGN_3	2017	04	1076	2579	41.72
RGN_3	2017	05	825	2579	31.99
RGN_3	2018	01	41	2495	1.64
RGN_3	2018	02	175	2495	7.01
RGN_3	2018	03	499	2495	20.00
RGN_3	2018	04	1044	2495	41.84
RGN_3	2018	05	736	2495	29.50
RGN_3	2019	01	43	2446	1.76
RGN_3	2019	02	143	2446	5.85
RGN_3	2019	03	505	2446	20.65
RGN_3	2019	04	1066	2446	43.58
RGN_3	2019	05	689	2446	28.17
RGN_4	2017	01	6	885	0.68
RGN_4	2017	02	49	885	5.54
RGN_4	2017	03	175	885	19.77
RGN_4	2017	04	384	885	43.39
RGN_4	2017	05	271	885	30.62
RGN_4	2018	01	37	1032	3.59
RGN_4	2018	02	83	1032	8.04
RGN_4	2018	03	208	1032	20.16
RGN_4	2018	04	402	1032	38.95
RGN_4	2018	05	302	1032	29.26
RGN_4	2019	01	7	773	0.91
RGN_4	2019	02	36	773	4.66
RGN_4	2019	03	148	773	19.15
RGN_4	2019	04	315	773	40.75
RGN_4	2019	05	267	773	34.54
RGN_5	2017	02	24	595	4.03
RGN_5	2017	03	94	595	15.80
RGN_5	2017	04	362	595	60.84
RGN_5	2017	05	115	595	19.33
RGN_5	2018	02	23	670	3.43
RGN_5	2018	03	87	670	12.99
RGN_5	2018	04	421	670	62.84
RGN_5	2018	05	139	670	20.75
RGN_5	2019	02	11	610	1.80
RGN_5	2019	03	77	610	12.62
RGN_5	2019	04	413	610	67.70
RGN_5	2019	05	109	610	17.87
RGN_6	2017	01	11	658	1.67
RGN_6	2017	02	31	658	4.71
RGN_6	2017	03	147	658	22.34
RGN_6	2017	04	295	658	44.83
RGN_6	2017	05	174	658	26.44
RGN_6	2018	01	9	653	1.38
RGN_6	2018	02	34	653	5.21
RGN_6	2018	03	156	653	23.89
RGN_6	2018	04	306	653	46.86
RGN_6	2018	05	148	653	22.66
RGN_6	2019	01	2	592	0.34
RGN_6	2019	02	18	592	3.04
RGN_6	2019	03	115	592	19.43
RGN_6	2019	04	241	592	40.71
RGN_6	2019	05	216	592	36.49
;


ods graphics / width=1000px height=500px;
title "100 Stacked Bar Chart Ordered by Percentages";
proc sgpanel data=Have;
panelby region_id / columns=7 onepanel;
vbar year / response=distribution_pct group=score_bands 
              grouporder=data groupdisplay=stack; /* order by counts of 1st bar */
colaxis discreteorder=data;
rowaxis grid values=(0 to 100 by 10) label="Percentage of Total with Group";
run;

[Ksharp · Accepted solution]

proc import datafile='c:\temp\sample.xlsx' out=have dbms=xlsx replace;
run;

data have2;
 set have;
 by region_id year;
 if not first.year then call missing(total);
 format total comma20.;
run;

ods graphics / width=800px height=600px noborder;
title 'Score distribution by region (2017/2018/2019)';
proc sgpanel data=have2  pad=(left=0 right=0 top=0 bottom=0) ;
panelby region_id/layout=COLUMNLATTICE onepanel novarname noborder 
 headerbackcolor=white colheaderpos=bottom noheaderborder spacing=0 sparse ;
styleattrs datacolors=(CXDDDDDD CXEFF3FF CXBDD7E7 CX6BAED6 CX2171B5   ) ;
vbarparm category=year response=distribution_pct /group=score_bands nooutline baselineattrs=(thickness=0)
 seglabel seglabelattrs=(color=black) seglabelformat=percent6.0 seglabelfitpolicy=none barwidth=0.9;
colaxistable total /label=' ' ;
rowaxis display=none ;
colaxis display=none offsetmin=0.2 offsetmax=0.2; 
keylegend /noborder title=' ';
run;