• Print |
• E-mail  |
• Bookmark

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 Version 8 and later.

The first examples are for procedures that don't 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-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 below example 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