turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Traffic Lighting with Proc Tabulate

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2012 12:53 PM

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!

Accepted Solutions

Solution

06-14-2012
03:28 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2012 03:28 PM

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;**

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2012 01:20 PM

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;**

Solution

06-14-2012
03:28 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-14-2012 03:28 PM

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;**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-15-2012 01:29 PM

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!