Usage Note 23353: Specify the color and the font of a cell based on the cell's value
 |  |  |  |
Selectively coloring cells in a table, based on the cells' values, is called traffic lighting. For example, if AGE<13, you want the cell to be colored red. Here are some examples that work with SAS® 8 and later in the non-LISTING destinations.
The first examples are for procedures that do not have templates, such as REPORT, PRINT, and TABULATE, using the STYLE= option. For procedures that do have templates, go to the DATA step example, which explains how to use the CELLSTYLE statement. The next to the last example shows how to traffic light based on the maximum value of the observation. The final example defines a tagset in the ODS MARKUP destination. See also FAQ 3597 if you want to color every other row of a table.
PROC REPORT
The example below color-codes the cells based on the value of AGE. The CALL DEFINE statement performs the traffic lighting by applying a style with the style attribute. When an AGE value meets the IF criteria, its cell has a red background color.
ods html body='temp.html';
proc report data=sashelp.class nowd;
column name age sex height weight;
define name / display;
define age / display;
define sex / order;
define height / sum;
define weight / sum;
compute age;
if age < 13 then call define(_col_,"style","style={background=red}");
endcomp;
run;
ods html close;
The next example demonstrates how to traffic light based on a cell value with PROC REPORT. The example color-codes an entire row by using the _ROW_ variable in the CALL DEFINE statement. The CALL DEFINE statement is used to assign an attribute to a column, cell, or row.
ods html body='temp.html';
proc report data=sashelp.class nowd;
column name age sex height weight;
define name / display;
define age / display;
define sex / order;
define height / sum;
define weight / sum;
compute age;
if age <= 12 then
call define(_row_,"style","style={background=blue}");
else if 12< age <14 then
call define(_row_,"style","style={background=green}");
else call define(_row_,"style","style={background=red}");
endcomp;
run;
ods html close;
The following example adds a style based on the observation. This is done by adding a counter variable in the COMPUTE block. The style is applied when the condition is met. Here, when COUNT is 5, the background of the row is red.
ods html body='temp.html';
proc report data=sashelp.class nowd;
column name age sex height weight;
define name / display;
define age / order;
define sex / order;
define height / sum;
define weight / sum;
compute age;
count+1;
if count=5 then
call define(_row_,"style","style={background=red}");
endcomp;
run;
ods html close;
PROC PRINT
PROC PRINT uses the STYLE option within the procedure beginning in 8.2. Prior to this release, PROC TEMPLATE was the only way to create styles with PROC PRINT. The below example color-codes the value of the variable AGE by using the BACKGROUND= attribute with the corresponding format.
ods html body='temp.html';
proc format;
value temp 11=red
12=purple
13=orange
14=pink
16=yellow
other=blue;
run;
ods html body='temp.html';
proc print data=sashelp.class ;
var age / style={background=temp.};
var sex height weight;
run;
ods html close;
PROC TABULATE
Beginning with Release 8.1, formats can be used to traffic light in the TABULATE procedure. Use PROC FORMAT to create the ranges or values to be applied to a field. Then apply the format by specifying an in-line style. The below example uses the in-line style on the CLASSLEV statement, which applies the styles to the values of the CLASS variable.
proc format;
value temp 11=red
12=purple
13=orange
14=pink
16=yellow
other=blue;
run;
ods html body='temp.html';
proc tabulate data=sashelp.class ;
class name sex age;
classlev age / style={background=temp.};
var height weight;
table age sex,height weight;
run;
ods html close;
The DATA Step
For procedures that have table templates, use the CELLSTYLE statement within the DEFINE COLUMN definition. You can conditionally assign background colors to cells, change font sizes, change font faces, and the like. Below is an example that uses the CELLSTYLE statement within the column definition. The FLYOVER attribute specifies to display different text based on the value of the cell. View output.
Title 'Changing the color based on the value of a cell';
Title2 'Also adding a flyover based on the value of the field';
proc template;
define table test;
define column col;
generic;
cellstyle _val_ <=11 as data {foreground=red flyover='only 11'},
_val_ <=12 as data {foreground=green flyover='only 12'},
_val_ <=13 as data {foreground=purple flyover='only 13'},
_val_ <=14 as data {foreground=orange flyover='only 14'},
_val_ <=15 as data {foreground=blue flyover='only 15'},
_val_ <=16 as data {foreground=pink flyover='only 16'};
end;
define column col1;
generic;
end;
define column char_var;
generic;
justify=on;
end;
end;
run;
ods html body='temp.html';
data _null_;
set sasuser.class;
file print ods=(template='test'
columns=(
col=age(generic=on)
col1=height(generic=on)
col1=weight(generic=on)
char_var=name(generic=on)
char_var=sex(generic=on)
));
put _ods_;
run;
ods html close;
Maximum Value
The below example traffic lights based on the maximum value of the observation. The PROC SQL code puts the maximum value into a series of macro variables. PROC FORMAT with MACRO builds a format with the maximum value. The table template is used to create the style with the BACKGROUND= attribute. This functionality became available with Release 8.2. This method is a little messy and might not work for you -- if a maximum value from one row appears in any subsequent rows, it will be highlighted there as well. (In this example, there are no maximum values that are repeated later.)
data one;
input c x y z;
cards;
1 7 31 4
4 9 5 10
1 73 3 4
40 9 5 6
8 70 3 4
5 2 90 6
;
run;
proc sql;
select count(c) into: num
from one;
select distinct( max(c,x,y,z)) into : temp1- :temp%left(&num)
from one;
run;
quit;
%put &temp1 &temp2;
%macro test;
proc format ;
value test (multilabel)
%do i=1 %to #
&&temp&i="red"
%end;other="green";
run;
%mend;
%test
proc template;
define table test;
parent=base.datastep.table;
define column c;
style={background=test.};
end;
define column x;
style={background=test.};
end;
define column y;
style={background=test.};
end;
define column z;
style={background=test.};
end;
end;
run;
ods html body='temp.html';
data _null_;
set one;
file print ods=(template='test');
put _ods_;
run;
ods html close;
ODS TAGSET
The final example uses the ODS MARKUP language to color-code the cell values. If the cell value is 11, then the in-line style is applied, and the background color is changed to red.
proc template;
define tagset Tagsets.color;
define event data;
start:
put "<td";
putq " style=""background:red""" / if cmp(value,"11");
trigger rowcol;
trigger align;
put ">";
put VALUE;
finish:
put "</td>" NL;
end;
parent=tagsets.phtml;
end;
run;
ods markup file="temp.html" tagset=tagsets.color;
proc print data=sashelp.class;
run;
ods markup close;
dm "wbrowse 'temp.html'";
See also the full PROC TEMPLATE FAQ and Concepts.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | All | n/a | |
| Type: | Usage Note |
| Priority: | low |
| Topic: | Third Party ==> Output ==> HTML SAS Reference ==> ODS (Output Delivery System) SAS Reference ==> DATA Step |
| Date Modified: | 2005-07-11 17:06:23 |
| Date Created: | 2003-08-08 16:50:17 |