I'd like some help and advice on how to dynamically change colors in sgplot depending on the value of the variable (variance_for), i.e. if the value is negative then the bar is red, otherwise green. I use SAS 9.4 third maintenance release, I tried various options in sgplot-waterfall but I can't get it to work.
My dataset and code are below:
data chart;
input month monyy5. variance_for;
format month monyy5.;
cards;
jan15 41.4
feb15 -89.4
mar15 374
apr15 -90.2
may15 -592.2
jun15 -48
jul15 -68.8
aug15 -121.9
sep15 38.2
oct15 80.1
;
run;
proc sgplot data=chart;
waterfall category=month response=variance_for
/ stat=mean datalabel DATALABELATTRS=(Size=9 Family=Verdana) FINALBARTICKVALUE= 'YTD Variance' dataskin=pressed
name='YTD Plan';
format variance_for f6.0;
run;
The best way to do this is to use an attrbutes map and the COLORGROUP option. In the code below, I created a small data step that creates a group column based on positive/negative. I use that color in the WATERFALL chart on the COLORGROUP option. I bound the attrmap to the proc using DATTRMAP and ATTRID. Let me know if you have any questions.
data posneg;
retain id "posneg";
length fillcolor $ 5;
input value $ fillcolor $;
cards;
p green
n red
;
run;
data grouped;
set chart;
if (variance_for < 0.0) then group="n";
else group="p";
run;
proc sgplot data=grouped dattrmap=posneg;
waterfall category=month response=variance_for
/ stat=mean datalabel DATALABELATTRS=(Size=9 Family=Verdana) FINALBARTICKVALUE= 'YTD Variance' dataskin=pressed
name='YTD Plan' colorgroup=group attrid=posneg;
format variance_for f6.0;
run;
The best way to do this is to use an attrbutes map and the COLORGROUP option. In the code below, I created a small data step that creates a group column based on positive/negative. I use that color in the WATERFALL chart on the COLORGROUP option. I bound the attrmap to the proc using DATTRMAP and ATTRID. Let me know if you have any questions.
data posneg;
retain id "posneg";
length fillcolor $ 5;
input value $ fillcolor $;
cards;
p green
n red
;
run;
data grouped;
set chart;
if (variance_for < 0.0) then group="n";
else group="p";
run;
proc sgplot data=grouped dattrmap=posneg;
waterfall category=month response=variance_for
/ stat=mean datalabel DATALABELATTRS=(Size=9 Family=Verdana) FINALBARTICKVALUE= 'YTD Variance' dataskin=pressed
name='YTD Plan' colorgroup=group attrid=posneg;
format variance_for f6.0;
run;
Thanks Dan, that worked great!
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.