topic Traffic Lighting with Proc Tabulate in SAS Procedures

Traffic Lighting with Proc Tabulate

Kimberley — Thu, 14 Jun 2012 16:53:50 GMT

Hi,

Once again I am pulling out my hair, trying to figure out how to do Traffic Lighting on a report I created with Proc Tabulate.

Here is what I have:

Proc Tabulate data=work.report;

Var Result ResultAverage;

Class Product Machine;

Table Product*Machine,(Result ResultAverage)*''Mean'=Mean')/;

run;

What I would like to do is highlight is Highlight the Result for a machine in yellow and red based on a calculation of the ratio between the 2. If the ratio is greater then 1.5, then the cell would be red, if the ratio is above 1.25 the cell would be yellow.

The sesulting table would look something like this:

Product	Machine	Result	ResultAverage
A	M1	4 (highlight yellow)	3
	M2	6 (highlight red)	3
B	M1	3 (no highlight)	3
	M2	6 (No highlight)	5

Can anybody help? I have been looking at proc format to do this, but haven't been successful.

Thank you!

Re: Traffic Lighting with Proc Tabulate

MikeZdeb — Thu, 14 Jun 2012 17:20:33 GMT

hi ... here's an example ...

proc format;

value weight

low -<75 = 'yellow'

125-high = 'red'

;

run;

title 'MEAN WEIGHT BY AGE';

proc tabulate data=sashelp.class;

var weight;

class age;

table age*weight=' '*mean=' '*[style=[background=weight.]];

run;

Re: Traffic Lighting with Proc Tabulate

Cynthia_sas — Thu, 14 Jun 2012 19:28:15 GMT

Hi:

Mike has posted the basic way to do trafficlighting with a user-defined format, and TABULATE.

But I don't understand exactly what you say you want. If what you want is to highlight the column that you have marked as RESULT, then you can only apply highlighting in TABULATE based on the values in the column for the RESULT calculations. So I see the calculated values in the column as 4, 6, 3, 6 (reading down the RESULT column). Therefore, I don't see how your criteria of ratio gt 1.5 or ratio gt 1.25 can be applied using TABULATE.

If you used PROC REPORT, then you could highlight one cell based on a condition that came from doing some kind of test based on another cell or another value. Examine the PROC REPORT code carefully to see how the trafficlighting for the HEIGHT column, MEAN statistic in PROC REPORT was based on the value of the RATIO column (see screenshot). The WEIGHT column, MEAN statistic used the simple FORMAT method for highlighting. You could not achieve the HEIGHT column trafficlighting with TABULATE because TABULATE does not have the equivalent of the COMPUTE block.

cynthia

proc format;
value weight
low -<75 = 'yellow'
125-high = 'red'
;
run;

ods listing close;
ods html file='c:\temp\hilite.html' style=htmlblue;

title 'MEAN WEIGHT BY AGE (Mike Example)';
proc tabulate data=sashelp.class;
var weight;
class age;
table age*weight=' '*mean=' '*[style=[background=weight.]];
run;

proc report data=sashelp.class nowd;
title 'PROC REPORT hilite one column based on the value in another column';
title2 'or based on some calculation, such as division';
column sex age height weight ratio;
define sex / group;
define age / group;

** trafficlighting for HEIGHT will happen in COMPUTE block;
** for RATIO.;
define height / mean f=6.2;

** WEIGHT column for MEAN statistic uses simple format;
** for trafficlighting;
define weight / mean f=6.2
         style(column)={background=weight.};

define ratio / computed 'Ratio' /* noprint */;
compute ratio;
    ** trafficlight HEIGHT column, mean statistic based on ;
    ** calculated RATIO (can NOPRINT RATIO if do not want to see it);
    ** use different colors;
    ** also note that can use complex conditions to determine;
    ** trafficlighting.;
     ratio = weight.mean / height.mean;
     if 0 < ratio < 1 then do;
        call define('height.mean','style','style={background=pink}');
     end;
     else if ratio lt 2 and age in (13,14) then do;
        if height.mean gt 62 then
           call define('height.mean','style','style={background=cyan}');
        else if height.mean le 62 then
           call define('height.mean','style','style={background=beige}');
     end;
     else if ratio ge 2 then do;
        call define('height.mean','style','style={background=violet}');
     end;
endcomp;
run;
ods html close;

Re: Traffic Lighting with Proc Tabulate

Kimberley — Fri, 15 Jun 2012 17:29:48 GMT

Thank you Cynthia and Mike for yoru responses.

What I am looking for is the ability to highlight a cell based on a calculation. Cynthia, thanks for enlightening me on teh capabilities of Proc Report versus Tabulate, I will study Proc Report and change my code to that procedure and see how that is going to work.

The joys of learning SAS - So much power, so many different ways, Choosing the right Procedure is not always easy!