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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.