Data visualization with SAS programming

Stacked Bar Chart

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Stacked Bar Chart

I am trying to create a stacked bar chart using SAS SGPLOT for the dataset provided below. 
The expected outcome is the picture pasted here. The graph in the picture is
created using excel. The chart is created using column stack chart.
Please note the expectation is to have
x axis lable only for 1, 2,...24 even though there are 12 data points for each hour.
Stacked_Bar_chart.PNG
data graph_data; input x Y1 Y2; datalines; 1 93.01828928 -49.12499772 1.083333333 81.51368342 -118.5650966 1.166666667 57.51179395 -114.3767311 1.25 3.796244473 -210.1883655 1.333333333 0 -230.050416 1.416666667 77.43773133 -120.6666667 1.5 104.4709437 -85.33333333 1.583333333 185.843853 0 1.666666667 134.793753 -53.260256 1.75 188.9908664 -56.34480864 1.833333333 173.311695 -84.51721296 1.916666667 109.0207485 -101.1586515 2 165.3462253 -66.71978568 2.083333333 200 -100.124685 2.166666667 112.2122633 -123.8807147 2.25 128.3758837 -189.9403573 2.333333333 47.804872 -256 2.416666667 59.69797413 -186.8513159 2.5 184.4853884 -117.7026317 2.583333333 200 -98.5539476 2.666666667 171.6048108 -51.95411318 2.75 200 -105.3542788 2.833333333 200 -108.7544443 2.916666667 198.1148427 -50.03905033 3 200 -44.921998 3.083333333 200 -82.60826233 3.166666667 165.2073641 -107.0721749 3.25 179.9075016 -181.5360874 3.333333333 163.868308 -206 3.416666667 74.600189 -216.0201858 3.5 200 -175.3314572 3.583333333 200 -174.392917 3.666666667 105.1657883 -108.6077947 3.75 200 -113.770452 3.833333333 200 -76.040084 3.916666667 165.325491 -121.8122523 4 180.205979 -98.818323 4.083333333 151.501761 -110.85364 4.166666667 92.56088922 -157.3548118 4.25 50.68162811 -206.6774059 4.333333333 12.836369 -256 4.416666667 56.86264336 -207.6619466 4.5 97.47967572 -159.3238933 4.583333333 200 -160.9858399 4.666666667 200 -153.0039436 4.75 170.1881293 -106.3015477 4.833333333 105.0195884 -178.9594016 4.916666667 153.2505996 -155.1465704 5 64.67605379 -131.3337392 5.083333333 170.09577 -57.520908 5.166666667 83.97260733 -157.0139387 5.25 53.08306367 -206.5069693 5.333333333 19.282344 -256 5.416666667 48.350897 -206 5.5 11.66946478 -256 5.583333333 42.341886 -256 5.666666667 32.46557893 -256 5.75 45.185345 -252.895553 5.833333333 53.040626 -239.874917 5.916666667 35.4823312 -223.6356688 6 85.6571844 -191.2713376 6.083333333 149.0899466 -108.9070064 6.166666667 61.0836574 -191.2713376 6.25 54.6178632 -223.6356688 6.333333333 50.836272 -206 6.416666667 64.48990821 -204.9424198 6.5 112.0766304 -153.8848396 6.583333333 157.1458656 -152.8272594 6.666666667 151.9412796 -169.7522174 6.75 200 -142.214135 6.833333333 174.4445147 -153.6021333 6.916666667 78.51153573 -203.6108803 7 60.59528479 -153.6196272 7.083333333 29.22129885 -153.6283741 7.166666667 0 -154.11661 7.25 133.9347704 -132.8718467 7.333333333 113.360222 -147.493583 7.416666667 161.8740057 -117.3143689 7.5 164.8855122 -87.13515483 7.583333333 191.678104 -64.196919 7.666666667 200 -14.936186 7.75 200 -62.20702527 7.833333333 200 -14.83256753 7.916666667 200 -26.55504502 8 200 -38.27752251 8.083333333 200 -50 8.166666667 200 -135.3333333 8.25 200 -220.6666667 8.333333333 200 -242.463072 8.416666667 200 -261.584031 8.5 200 -273.406471 8.583333333 200 -278.076633 8.666666667 200 -259.0950417 8.75 200 -212.1900833 8.833333333 200 -165.285125 8.916666667 200 -126.85675 9 200 -88.428375 9.083333333 200 -50 9.166666667 200 -135.3333333 9.25 200 -217.533904 9.333333333 200 -262.84551 9.416666667 200 -273.571598 9.5 200 -298.8632791 9.583333333 200 -300.88129 9.666666667 200 -285.5877954 9.75 200 -265.1755909 9.833333333 200 -244.7633863 9.916666667 200 -179.8422576 10 200 -114.9211288 10.08333333 200 -50 10.16666667 200 -122.368101 10.25 200 -194.736202 10.33333333 200 -267.104303 10.41666667 200 -280.0695353 10.5 200 -293.0347677 10.58333333 200 -306 10.66666667 200 -306 10.75 200 -306 10.83333333 200 -306 10.91666667 200 -296.6408821 11 200 -287.2817643 11.08333333 200 -277.9226464 11.16666667 200 -287.2817643 11.25 200 -296.6408821 11.33333333 200 -306 11.41666667 200 -306 11.5 200 -306 11.58333333 200 -306 11.66666667 200 -306 11.75 200 -306 11.83333333 200 -306 11.91666667 200 -306 12 200 -306 12.08333333 200 -306 12.16666667 200 -306 12.25 200 -306 12.33333333 200 -306 12.41666667 200 -306 12.5 200 -306 12.58333333 200 -306 12.66666667 200 -306 12.75 200 -306 12.83333333 200 -306 12.91666667 200 -306 13 200 -306 13.08333333 200 -306 13.16666667 200 -306 13.25 200 -306 13.33333333 200 -306 13.41666667 200 -306 13.5 200 -306 13.58333333 200 -306 13.66666667 200 -306 13.75 200 -306 13.83333333 200 -306 13.91666667 200 -306 14 200 -306 14.08333333 200 -306 14.16666667 200 -306 14.25 200 -306 14.33333333 200 -306 14.41666667 200 -306 14.5 200 -306 14.58333333 200 -306 14.66666667 200 -306 14.75 200 -306 14.83333333 200 -306 14.91666667 200 -306 15 200 -306 15.08333333 200 -306 15.16666667 200 -306 15.25 200 -306 15.33333333 200 -306 15.41666667 200 -306 15.5 200 -306 15.58333333 200 -306 15.66666667 200 -305.1681674 15.75 200 -304.3363349 15.83333333 200 -303.5045023 15.91666667 200 -304.3363349 16 200 -305.1681674 16.08333333 200 -306 16.16666667 200 -306 16.25 200 -306 16.33333333 200 -306 16.41666667 200 -306 16.5 200 -306 16.58333333 200 -306 16.66666667 200 -299.0594641 16.75 200 -292.1189282 16.83333333 200 -285.1783923 16.91666667 200 -190.5606837 17 200 -95.94297498 17.08333333 200 -1.325266311 17.16666667 200 15.67901713 17.25 200 32.68330056 17.33333333 200 49.687584 17.41666667 200 17.13390133 17.5 200 -15.41978134 17.58333333 200 -47.97346402 17.66666667 200 17.0014711 17.75 200 81.97640622 17.83333333 200 146.9513413 17.91666667 200 81.30089422 18 200 15.65044711 18.08333333 200 -50 18.16666667 200 22.33336667 18.25 200 94.66673333 18.33333333 200 167.0001 18.41666667 200 140.1367364 18.5 200 113.2733727 18.58333333 200 86.41000907 18.66666667 200 102.1462527 18.75 200 117.8824964 18.83333333 200 133.61874 18.91666667 200 85.60642267 19 200 37.59410533 19.08333333 200 -10.418212 19.16666667 200 11.28407467 19.25 200 32.98636133 19.33333333 200 54.688648 19.41666667 200 73.79275267 19.5 200 92.89685733 19.58333333 200 112.000962 19.66666667 200 130.000608 19.75 200 148.000254 19.83333333 200 165.9999 19.91666667 200 150.3333 20 200 134.6667 20.08333333 100 119.0001 20.16666667 0 64.36962976 20.25 66.66666667 9.739159527 20.33333333 100 -44.89131071 20.41666667 100 -13.42456047 20.5 100 18.04218976 20.58333333 100 49.50894 20.66666667 100 23.866651 20.75 100 -7.738016 20.83333333 100 -18.26593021 20.91666667 100 30.406079 21 33.33333333 49.22293333 21.08333333 0 59.96670367 21.16666667 0 61.077654 21.25 0 61.18846722 21.33333333 0 61.799349 21.41666667 0 2.315380812 21.5 0 35.57541027 21.58333333 0 -27.60874285 21.66666667 0 -74.83465876 21.75 0 -147.0328752 21.83333333 0 -184.7476491 21.91666667 0 -164.5752323 22 0 -144.4028155 22.08333333 0 -124.2303987 22.16666667 0 -172.5841888 22.25 0 -220.9379789 22.33333333 0 -269.291769 22.41666667 0 -211.715434 22.5 0 -154.1390991 22.58333333 0 -96.56276408 22.66666667 0 -96.49470039 22.75 0 -96.42663669 22.83333333 0 -96.358573 22.91666667 0 -103.7984077 23 0 -111.2382423 23.08333333 0 -118.678077 23.16666667 0 -181.118718 23.25 0 -215.080921 23.33333333 0 -232.3095366 23.41666667 0 -241.111422 23.5 0 -226.89157 23.58333333 50 -236.913355 23.66666667 100 -217.1064527 23.75 100 -219.149805 23.83333333 100 -210.26803 23.91666667 133.3333333 -184.4838259 24 177.7777778 -151.6689512 24.08333333 200 -132.9154177 24.16666667 200 -130.5913181 24.25 200 -128.2672186 24.33333333 200 -125.943119 24.41666667 200 -166.3804107 24.5 200 -206.8177024 24.58333333 200 -247.2549941 24.66666667 200 -256.8061596 24.75 200 -266.3573251 24.83333333 200 -275.9084907 24.91666667 200 -212.6745807 ; run;

 


Accepted Solutions
Solution
‎01-15-2016 06:20 PM
SAS Super FREQ
Posts: 3,236

Re: Stacked Bar Chart

OK, I hear what you are saying. As shown, the plot you want is not technically a stacked bar chart. A stacked bar chart would have the Y2 bars begin at the top of the Y1 bars. The distance from the X axis to the tip of the Y2 bar would represent Y1 + Y2, regardless of the sign for Y2. 

 

I've seen this kind of chart to represent "net income" chart, when Y1 and Y2 are incomes/losses from two stores/assets.

There is an interesting discussion at Stephen Few's web site.

 

Regardless of what we call it, here's my suggestion: in the DATA step, create a new variable

if Y2<0 then Z=Y2; else Z = Y1+Y2;

Now plot Z first, so that it is in the background. Plot Y1 next, which will put it in the foreground and "cover up" the portion of Z that you want to hide.  You can either use the VBAR statements that @mohamed_zaki proposes (but use Z for the first VBAR statement), or  consider a needle plot, since the time points are taken along a continous variable.

 

title 'Stacked Bar Chart';
proc sgplot data=work.have;
 needle x=x y=Z / legendlabel="Y2" lineattrs=GraphData2;/* in background */
 needle x=x y=y1 / lineattrs=GraphData1;       /* overlay in foreground */
 yaxis label="Y" grid;
run;

SGPlot7.png 

View solution in original post


All Replies
SAS Super FREQ
Posts: 1,044

Re: Stacked Bar Chart

I was not able to see your attached graph.  However, you can do this using SGPLOT.  I transposed your data into a group format from the two column.  Values will be summarized for each hour, and stacked by Y1 & Y2.  Negative values are stacked below the axis.

 

BarByHour.png

 

data graph;
  keep X Group Value;
  set graph_data;
  x=int(x);
  Group='Y1'; Value=y1; output;
  Group='Y2'; Value=y2; output;
run;

 

title 'Y1 and Y2 by Hour';
proc sgplot data=graph;
  vbar x / response=value group=group nostatlabel;
  xaxis display=(nolabel);
run;

Regular Learner
Posts: 1

Re: Stacked Bar Chart

[ Edited ]

A stacked bar chart is a bar chart in which a variable from the dataset is calculated with respect to another variable.

 

The below script will create a stacked bar-chart where the length of the cars are calculated for each car type. We use the group option to specify the second variable.

 

Example: 

 

proc SGPLOT data=work.cars1;
vbar length /group = type ;
title ‘Lengths of Cars by Types’;
run;
quit;

 

When we execute the above code, we get the following output:

 

barchart2.jpg

 

Super Contributor
Posts: 490

Re: Stacked Bar Chart

 proc sgplot data=work.have;
 title 'Stacked_bar_Chart';
  vbar x / response=y2 ;
 vbar x / response=y1 ;
  xaxis display=All FITPOLICY=thin;
  run;

download.png

SAS Super FREQ
Posts: 3,236

Re: Stacked Bar Chart

You didn't give us any context for your data, but the X variable seems to be continuous rather than categorical, so it doesn't seem like a bar chart is appropriate.

 

If your goal is to visualize the sum of Y1 and Y2, then consider a SERIES plot. You can just plot the sum, or you can include a series plot of Y1 and Y2 as well.  For example, add

Sum = Y1 + Y2 to the original data step and then plot the following:

 


proc sgplot data=work.have;
  band x=x upper=y1 lower=0 / transparency=0.85 legendlabel="Y1" fillattrs=(color=red);
  band x=x lower=y2 upper=0 / transparency=0.85 legendlabel="Y2" fillattrs=(color=blue);
  series x=x y=Sum;
  refline 0 / axis=y;
run;
New Contributor
Posts: 3

Re: Stacked Bar Chart

Rick,

 

the data I presented in my question had 12 data points were hour and I need to plot data for entire day. So I need to plot data 288 distinct data points. For each intervals I want to plot a bar Y1 and Y2. If Y1 is positive and Y2 is negative then both bars start from zero. If Y1 and Y2 are in the same direction, I need them stacked. Based on your response, I could plot the sum = (Y1+Y2), however, this would not give me the information i need ( I need to see visually the impact of both Y1 and Y2). I am attaching the chart here that I created with excel.

Attachment
Solution
‎01-15-2016 06:20 PM
SAS Super FREQ
Posts: 3,236

Re: Stacked Bar Chart

OK, I hear what you are saying. As shown, the plot you want is not technically a stacked bar chart. A stacked bar chart would have the Y2 bars begin at the top of the Y1 bars. The distance from the X axis to the tip of the Y2 bar would represent Y1 + Y2, regardless of the sign for Y2. 

 

I've seen this kind of chart to represent "net income" chart, when Y1 and Y2 are incomes/losses from two stores/assets.

There is an interesting discussion at Stephen Few's web site.

 

Regardless of what we call it, here's my suggestion: in the DATA step, create a new variable

if Y2<0 then Z=Y2; else Z = Y1+Y2;

Now plot Z first, so that it is in the background. Plot Y1 next, which will put it in the foreground and "cover up" the portion of Z that you want to hide.  You can either use the VBAR statements that @mohamed_zaki proposes (but use Z for the first VBAR statement), or  consider a needle plot, since the time points are taken along a continous variable.

 

title 'Stacked Bar Chart';
proc sgplot data=work.have;
 needle x=x y=Z / legendlabel="Y2" lineattrs=GraphData2;/* in background */
 needle x=x y=y1 / lineattrs=GraphData1;       /* overlay in foreground */
 yaxis label="Y" grid;
run;

SGPlot7.png 

New Contributor
Posts: 3

Re: Stacked Bar Chart

Rick_SAS,

I believe this will do for what I am looking for. I do have a follow-up question though: Why do SAS charts not allow to have stacked Needle charts? Is that some thing being planned for future release. Sometimes, charts are not only being used to draw inference but simply being used a picture we need need some free form abilities as sas analysts.
SAS Super FREQ
Posts: 3,236

Re: Stacked Bar Chart

A needle plot, by definition, drops a line to the axis. The flexibility that you are asking for is available in the HIGHLOW plot. You can use the TYPE=BAR option to mimic the behavior of a stacked bar chart.



You might want to acquaint yourself with the many varieties of plots that PROC SGPLOT can produce. One of the easiest ways to do that is to browse the ODS Graphics Gallery: http://support.sas.com/sassamples/graphgallery/PROC_SGPLOT.html

Each graph in the gallery has a link to the complete SAS program that generates it.


Post a Question
Discussion Stats
  • 8 replies
  • 701 views
  • 1 like
  • 5 in conversation