BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kimberley
Calcite | Level 5

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:

ProductMachineResultResultAverage
AM14 (highlight yellow)3
M26 (highlight red)3
BM13 (no highlight)3
M26 (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
Cynthia_sas
SAS Super FREQ


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;


pix_report_traffic.png

View solution in original post

3 REPLIES 3
MikeZdeb
Rhodochrosite | Level 12

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;



traffic.png
Cynthia_sas
SAS Super FREQ


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;


pix_report_traffic.png
Kimberley
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3750 views
  • 6 likes
  • 3 in conversation