topic Re: Traffic Lighting Cells in Proc Report in SAS Procedures

Traffic Lighting Cells in Proc Report

Kimberley — Mon, 23 Jul 2012 14:23:39 GMT

Hi, this should be easy - or so I thought.

I want to compare 2 cells, and highlight one of them if it is larger then the second.

For example, I want to highlight the weight if it is higher then the max weight.

Product	Weight	MaxWeight
A	4	5
A	6	5
B	3	4
3	4	3
C	7	3

Here is what I have as code, that does not work - I'm not sure how to use the compute block to create a different style:

proc report data=work.data nowd;

column product weight maxweight;

define weight / computed ;

compute weight;

if weight < maxweight then do;

call define('Weight','style','style={background=White}');

end;

else if weight> maxweight then do;

call define('Weight','style','style={background=yellow}');

end;

endcomp;

run;

I think I need to take a class on Proc Report. Thanks for your help!

Re: Traffic Lighting Cells in Proc Report

Tim_SAS — Mon, 23 Jul 2012 15:15:05 GMT

Try this. By default numeric variables such as "weight" are analysis variables and so have statistics associated with them. In a compute block you would refer to the statistic. For example "weight.sum". In this case, however, you don't want statistics, you just want to display the value, so you should specify "weight" as a display variable.

proc report data=sashelp.class nowd;

column name weight;

define name--weight / display;

compute before;

maxweight = 0;

endcomp;

compute weight;

if weight < maxweight then do;

call define('Weight','style','style={background=green}');

end;

else do;

if weight > maxweight then do;

call define('Weight','style','style={background=yellow}');

maxweight = weight;

end;

endcomp;

run;

Re: Traffic Lighting Cells in Proc Report

Cynthia_sas — Mon, 23 Jul 2012 15:27:03 GMT

Hi:

As Tim recommends, defining WEIGHT would allow you to do the comparisons easier. I am confused about one thing though. Does the variable MAXWEIGHT exist on every observation in the dataset or are you computing MAXWEIGHT?

Just curious. If MAXWEIGHT already exists in your data, you might have to change your code slightly from what Tim shows.

cynthia

And, we do offer a report class that covers ODS, PROC TABULATE and PROC REPORT.

https://support.sas.com/edu/schedules.html?ctry=us&id=284#outline

Re: Traffic Lighting Cells in Proc Report

Kimberley — Mon, 23 Jul 2012 16:47:03 GMT

Hi Cynthia, maxweight does already exist in my dataset, maybe that's why I can't seem to make Tim's code work.

How woudl I need to change the code?

Thank you for your help, I will look into taking the class, thanks for the info.

Kim

Re: Traffic Lighting Cells in Proc Report

Cynthia_sas — Mon, 23 Jul 2012 17:53:17 GMT

Hi:

SASHELP.CLASS did not have a variable called MAXWEIGHT, so Tim's code creates that variable as a "temp" variable. If you are going to do a comparison between WEIGHT and MAXWEIGHT, then you will need your COMPUTE block to be for MAXWEIGHT. PROC REPORT places variables on the report from LEFT to RIGHT. So, if this is your COLUMN statement:

column product weight maxweight;

then, at the point in time when WEIGHT is being placed on the report row, PROC REPORT has not yet placed MAXWEIGHT on the report row. So if you did your test in the COMPUTE block for WEIGHT, I suspect that every row would be yellow because MAXWEIGHT does not have a value yet. If MAXWEIGHT exists in the data, then the proper place for you to do your test is in a COMPUTE block for MAXWEIGHT.

The code below shows 3 different methods:

1) Method 1: has DISPLAY usage for WEIGHT and MAXWEIGHT

2) Method 2: has SUM usage for WEIGHT and MAXWEIGHT (notice how this changes the reference method in the COMPUTE block and CALL DEFINE statement

3) Method 3: has MEAN usage for WEIGHT and MAXWEIGHT (because I wanted to show how the summary line for Averages could be created with an RBREAK statement)

I changed the logical operators to be LE and GT (which I prefer over <, > or <= or >=), so that the equal situation (if WEIGHT=MAXWEIGHT) would be covered.

cynthia

data fakedata;
length product $10;
infile datalines;
input product $ weight maxweight;
return;
datalines;
A 4 5
A 6 5
B 3 4
3 4 3
C 7 3
;
run;

ods _all_ close;
title; footnote;

ods html file='c:\temp\hilight.html';
proc report data=work.fakedata nowd;
title 'Method 1: All items are DISPLAY usage';
column product weight maxweight;
define product / display;
define weight / display ;
define maxweight / display;
    ** use LE to take care of equal situation instead of just LT;
    compute maxweight;
       if weight le maxweight then do;
         call define('Weight','style','style={background=White}');
       end;
       else if weight gt maxweight then do;
         call define('Weight','style','style={background=yellow}');
       end;
     endcomp;

run;

proc report data=work.fakedata nowd;
title 'Method 2: All numeric items are SUM usage (default)';
column product weight maxweight;
define product / display;
define weight / sum ;
define maxweight / sum;

    compute maxweight;
       if weight.sum le maxweight.sum then do;
         call define('Weight.sum','style','style={background=White}');
       end;
       else if weight.sum gt maxweight.sum then do;
         call define('Weight.sum','style','style={background=yellow}');
       end;
     endcomp;

run;

proc report data=work.fakedata nowd;
title 'Method 3: Numeric items are MEAN usage with summary line at bottom';
column product weight maxweight;
define product / display;
define weight / mean ;
define maxweight / mean;
rbreak after / summarize;
    compute after;
       product='Averages';
    endcomp;
    compute maxweight;
       if weight.mean le maxweight.mean then do;
         call define('Weight.mean','style','style={background=White}');
       end;
       else if weight.mean gt maxweight.mean then do;
         call define('Weight.mean','style','style={background=yellow}');
       end;
     endcomp;

run;
ods html close;

Re: Traffic Lighting Cells in Proc Report

ArtC — Mon, 23 Jul 2012 18:19:26 GMT

As an aside notice in Cynthia's code that the compute block is for MAXWEIGHT and not WEIGHT. In the original posted code you are attempting to use the variable MAXWEIGHT in the WEIGHT compute block. Since MAXWEIGHT is to the right of WEIGHT in the COLUMN statement, MAXWEIGHT cannot yet be addressed. Remember that for a compute block associated with a given report item you may only address report items to the left of it in the COLUMN statement.

Re: Traffic Lighting Cells in Proc Report

Kimberley — Thu, 26 Jul 2012 11:59:57 GMT

Thank you everyone for your help, I understand a lot more about how the compute block works now, and how to traffic light better.

Cynthia, you were absolutely right - the whole column did turn yellow. I'm looking into taking the class, I wish it was available on e-learning, so much easier to convince my manager to take e-learning classes then having to travel to the US (I'm in Canada). The web class would be great, but the schedule is so disruptive to our daily operations, but is still probably our best option.