Traffic Lighting in Proc Tabulate (highlighting by row)

QingJi — Fri, 06 Jul 2012 15:14:17 GMT

When comes to traffic lighting in Proc Tabulate, SAS only allows you to highlight certain cells by columns using the option "style=(foreground=)" (or background). But what if I want to highlight a entire row base on the value of one column (or multiple columns)? I did some research on my own and it turns out there is no easy way to do such thing, other than doing it manually. Proc Report does highlighting by row but the two procedures don't work the same.

Generally Speaking, "Do It Yourself" is good enough for most of situation. However, it could be tough in some cases such as generating tables with a same format over and over again or a very long table.

I came up with a solution which utilizes Proc Format and its “cntlin=” option. Below is an example:

A data set “estimates” contains six variables, varA, varB, var1, var2, var3 and var4. I want to generate a table and it looks like this in Proc Tabulate:

table varA*varB, (var1 var2 var3 var4)*(mean sum ……);

How to highlight a row based a certain statistic of var4? To understand the following part, first, you should be familiar with cntlin option in Proc Format and foreground/background in style option of Proc Tabulate, especially the fact that foreground/background can accompany a format.

First of all, use “proc tabulate out= estimates” to store the desired table as a SAS data set.

proc format;

value highlight

0-0.05='Red' /*Color to use*/

0.05-high='Black';

run;

data temp; /*Create a new data set based on the data set of the table*/

set estimates;

length fmtname $20.;

length label $20;

if var4_stat <= 0.05 then do; /*Selecting rows to highlight based on a statistics of var4*/

/*Creat a format for var1*/

fmtname="nameA"; /*Format Name*/

start=var1_stat; /*Start contains the value to give the special formatted value*/

label="red"; /*label contains formatted value which is the name of color to use for highlighting*/

output;

/*Creat a format for var2*/

fmtname="nameB";

start=var2_stat;

label="red";

output;

/*Creat a format for var3*/

fmtname="nameC";

start=var3_stat;

label="red";

output;

end;

keep fmtname start label;

run;

proc sort data=temp;

by fmtname;

run;

proc format cntlin=temp;

run;

proc tabulate data=estimates;

var var1_stat var2_stat var3_stat var4_stat;

class varA varB;

table varA*varB, (var1_stat *[style=[foreground=nameA.]] var2_stat *[style=[foreground=nameB.]]

var3_stat *[style=[foreground=nameC.]] var4_stat *[style=[foreground=highlight.]])*sum="";

run;

Any question, comment or suggestion is welcome at jiq@muohio.edu

The following part is an example for testing

/**********************************************************************************************/

data test;

input vara $ varb $ var1 var2 var3 var4;

datalines;

a a 1 2 3 0.01

a b 4 5 6 0.02

a a 7 8 9 0.03

a b 10 11 12 0.04

b a 13 14 15 0.05

b b 16 17 18 0.06

b a 19 20 21 0.07

b b 22 23 24 0.08

;

run;

proc tabulate data=test out=estimates;

class vara varb;

var var1 var2 var3 var4;

table vara*varb, (var1 var2 var3 var4)*(mean);

run;

proc format;

value highlight

0-0.05='Red'

0.05-high='Black';

run;

data temp;

set estimates;

length fmtname $20.;

length label $20;

if var4_mean <= 0.05 then do;

fmtname="nameA";

start=var1_mean;

label="red";

output;

fmtname="nameB";

start=var2_mean;

label="red";

output;

fmtname="nameC";

start=var3_mean;

label="red";

output;

end;

keep fmtname start label;

run;

proc sort data=temp;

by fmtname;

run;

proc format cntlin=temp;

run;

proc tabulate data=estimates;

var var1_mean var2_mean var3_mean var4_mean;

class varA varB;

table varA*varB, (var1_mean*[style=[foreground=nameA.]] var2_mean*[style=[foreground=nameB.]]

var3_mean*[style=[foreground=nameC.]] var4_mean*[style=[foreground=highlight.]])*sum="";

run;

Re: Traffice Lighting in Proc Tabulate (highlighting by row)

Cynthia_sas — Fri, 06 Jul 2012 16:37:31 GMT

Hi:

Just for comparison purposes, here's the solution for trafficlighting your WORK.TEST data, directly from PROC REPORT, without creating an output dataset first and, without using CNTLIN. You are correct that the REPORT syntax is different, but you can ask for the MEAN statistic directly, using VARA and VARB as GROUP report items. If you wanted the entire ROW to be red, including VARA and VARB, then the first argument to the CALL DEFINE statement could change to _ROW_. But I thought it looked better with VARA and VARB using Header style -- so I just highlighted VAR1-VAR4 separately in the COMPUTE block.

cynthia

data test;
input vara $ varb $ var1 var2 var3 var4;
return;
datalines;
a a 1 2 3 0.01
a b 4 5 6 0.02
a a 7 8 9 0.03
a b 10 11 12 0.04
b a 13 14 15 0.05
b b 16 17 18 0.06
b a 19 20 21 0.07
b b 22 23 24 0.08
;
run;

ods html file='c:\temp\rephilite.html' style=egdefault;

** proc report alternative;
** data does not need to be pre-summarized tabulate first;
proc report data=test nowd;
column vara varb var1 var2 var3 var4;
define vara / group style(column)=Header;
define varb / group style(column)=Header;
define var1 / mean f=6.2;
define var2 / mean f=6.2;
define var3 / mean f=6.2;
define var4 / mean f=6.2;
compute var4;
    if var4.mean le 0.05 and var4.mean ne . then do;
       call define('var1.mean','style','style={color=red}');
       call define('var2.mean','style','style={color=red}');
       call define('var3.mean','style','style={color=red}');
       call define('var4.mean','style','style={color=red}');
    end;
endcomp;
run;

ods html close;

topic Traffic Lighting in Proc Tabulate (highlighting by row) in Statistical Procedures

Traffic Lighting in Proc Tabulate (highlighting by row)

Re: Traffice Lighting in Proc Tabulate (highlighting by row)