Turn on suggestions

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

Showing results for

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

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 06-14-2012 12:53 PM
(4036 views)

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!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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!

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.