Conditional formatting

[deleted_user]

Hello all..

Is there a way, in ODS via Proc Report, to conditionally format the background color for a cell depending on the value for that cell? For example, 0-60%-red 6t0-90% yellow, 91-100% green? Any advice most appreciated.

[Cynthia_sas]

Hi:
There are 2 ways to do what you want to do:
1) Use a user-defined format with the STYLE= option to change the background color of the cell
[pre]
define myvar / sum
style(column)={background=bfmt.};
[/pre]
where the BFMT format listed the colors to be used for various values:
[pre]
proc format;
value BFMT 0-.6 = 'red'
.6-.9 = 'yellow'
.91-1.00 = 'green';
run;
[/pre]

OR
2) you could use a CALL DEFINE statement in a COMPUTE block.
[pre]
COMPUTE MYVAR;
IF ....
call define (_COL_,'style',
'style={background=green}');
ELSE IF ...
call define (_COL_,'style',
'style={background=yellow}');
ENDCOMP:
[/pre]

The advantage of the user-defined format is that if you only want to change the background color of a cell based on the value in the cell, this is an easy way to implement the change. On the other hand, if you needed to use more complex logic, like check the value of 2 variables in order to do the traffic lighting, then call define is the only way to implement that kind of traffic lighting:
[pre]
if region = 'Asia' and someother = 1 then
call define ....;
[/pre]

This paper has some examples:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf
http://support.sas.com/kb/23/353.html (this shows both approaches and although it discusses ODS HTML, the techniques also work for RTF and PDF)

You may find other examples on the Tech Support site at http://support.sas.com. There are also examples in the PROC REPORT documentation. Or, once you decide on your technique after consulting various resources, you can also contact Tech Support for more help.

cynthia