The SAS Output Delivery System and reporting techniques

Traffic Ligts Cell Based On Another Cells Value

Reply
Super Contributor
Posts: 358

Traffic Ligts Cell Based On Another Cells Value

Hi all.

This may have come up before but I've got someone breathing down my neck to see if this works.

My sample code:

proc tabulate data=report01 missing order=data out=tabdata1;
by company group;
class group res_group w_date priority / preloadfmt;
var benchmark duration walltime;

table group='Group'*(priority='Priority') all='Group Mean:' ,
benchmark='Target'*mean=''*f=time8.
w_date=' ' * (duration='#'*n=''*f=4. duration='Sum'*sum=''*f=time10. duration='Mean'*mean=''*f=time10.)
/ MISSTEXT=' ' RTS=40;


Can I set a traffic light on the mean of Duration based on what is in Benchmark? I want Duration to be in RED if it is greater than Benchmark.

I normallly use a PROC FORMAT to set the traffic lighting for the variables, but I don't think that will work here....
SAS Super FREQ
Posts: 8,642

Re: Traffic Ligts Cell Based On Another Cells Value

Hi:
What you want to do (traffic light one cell based on a comparison to another cell) is not something that is easy to do with PROC TABULATE, but can be done with PROC REPORT. Especially, since I suspect you want the BENCHMARK for a particular row combination of group and priority to be compared to the DURATION mean for the same row combination of group and priority.

PROC TABULATE only allows you to traffic light based on one cell's value. And your user-defined format method is the only method that is available to TABULATE -- you'd have to apply the format to the entire column for DURATION*MEAN.

PROC REPORT, on the other hand, allows you to traffic light cells based on the values in another cell using CALL DEFINE in a COMPUTE block. So you could traffic light the DURATION*MEAN value based on the BENCHMARK value on the same row. The (untested) syntax would be something like this:
[pre]
compute durmean;
if durmean gt benchmark.mean then do;
call define(_col_,'style','style={foreground=red}');
end;
endcomp;
[/pre]

For a more concrete example, consider the program below that uses SASHELP.CLASS and highlights several columns based on the values found in other columns.

There is too much about PROC REPORT to go into a lot of detail on the differences between REPORT and TABULATE here, but using this program as a starting point, and these papers,
http://www2.sas.com/proceedings/sugi25/25/hands/25p147.pdf
http://www2.sas.com/proceedings/sugi29/242-29.pdf
http://www2.sas.com/proceedings/sugi30/244-30.pdf

you should be able to find documentation and other user group papers to get you going down the PROC REPORT road.

cynthia
[pre]
ods html file='c:\temp\class_style.html' style=sasweb;
proc report data=sashelp.class nowd;
column age sex ('Height' height height=htavg);
define sex / group;
define age / group ;
define height / min 'Min' f=5.1;
define htavg / mean 'Mean' f=5.1;
compute sex;
if sex = 'F' and age = 13 then do;
call define(_row_,'style','style={background=yellow}');
end;
endcomp;
compute htavg;
if htavg = height.min then do;
call define (_col_,'style','style={foreground=green font_weight=bold font_size=14pt}');
call define ('height.min','style','style={foreground=green font_weight=bold font_size=14pt}');
end;
if round(htavg,.1) = 66.8 then do;
call define (_row_,'style','style={background=cxccccff}');
end;
endcomp;
run;
ods html close;
[/pre]
Super Contributor
Posts: 358

Re: Traffic Ligts Cell Based On Another Cells Value

Cynthia:

That (unfortunately) is what I thought. I'm using TABULATE because it is easier for me to do the SUM and MEAN of my data. I'm familiar with PROC REPORT but I hate to have to rewrite the code up to this point in order to use it.

I might just have to bite the bullet because I can't get the column and row headers to sort in the right order no matter what I do (even with a PRELOADFMT). Either the columns are sorted and the rows aren't, or vise versa (I know the nature of this problem and it can't be fixed).

Thanks for the help.
SAS Super FREQ
Posts: 8,642

Re: Traffic Ligts Cell Based On Another Cells Value

Hi:
If you can get the data in order the way you want (both rows and columns) outside of the procedure, both REPORT and TABULATE have ORDER=DATA -- REPORT on the DEFINE statement and TABULATE on the CLASS statement. That should ensure that you get your data in the order you want. If you PRELOADFMT -- that will not necessarily guarantee the order of the values -- merely that some value will be placed on the report -- for example, your data has values A, B and D for variable TYPE, but you want C to appear on the report, even though you have no C data -- PRELOADFMT ensures that C will get on the report even if it's not in the data. ORDER= is the controlling factor for the order of the CLASS variables in TABULATE and the ORDER/GROUP variables in REPORT.

There are 2 ways to get statistics from PROC REPORT, one is what I call "direct" and the other is called "alias". The "direct" method would be something like this:
[pre]
column grp subgrp salary,(min mean max sum);
[/pre]

where the syntax looks more TABULATE-like (with the comma operator and the parentheses used to cross salary with min mean max and sum. the "alias" method is the one I like to use because I have more control over the item names for use in a COMPUTE block:
[pre]
column grp subgrp salary salary=salavg salary=salmax salary=salsum;
define salary/ min 'Min';
define salavg / mean 'Avg';
define salmax / max 'Max';
define salsum / sum 'Sum';
[/pre]

If you do not have a heavily nested structure in the column dimension, PROC REPORT across variables can work quite nicely.

cynthia
Grand Advisor
Posts: 9,315

Re: Traffic Ligts Cell Based On Another Cells Value

Hi.
Yes. proc print , proc tabulate , proc report can all use traffic light.


[pre]
proc format;
value style
low-99='green'
100-high='red';
run;
ods html file='c:\temp\x.html' style=sasweb;
proc tabulate data=sashelp.class;
class sex;
var weight;
table sex*weight*mean*{style={background=style.}};
run;
ods html close;
[/pre]


Ksharp Message was edited by: Ksharp
Super Contributor
Posts: 358

Re: Traffic Ligts Cell Based On Another Cells Value

Cynthia:

I finally decided to convert my PROC TABULATE to a PROC REPORT following your example, but I've run into a little problem.

The reason that I used the TABULATE was because my "across" variable is a date and I am reporting a N, MEAN, and SUM for each value of the date.

I can't seem to emulate this with the PROC REPORT - i have my date as a ACROSS varaiable and code (in part):

column date,(duration duration=durn duration=durm duration=durs)
define date / across 'date';
define durn / n 'count';
define durm / mean 'Mean';
define durs / sum 'Sum';

but the resulting report is a - well, a mess to put it bluntly.

how can I get multiple statistics on an ACROSS variable?
SAS Super FREQ
Posts: 8,642

Re: Traffic Ligts Cell Based On Another Cells Value

Hi:
In your TABULATE example, you had GROUP and PRIORITY in the ROW dimension. I didn't see those on the PROC REPORT step, but would have expected to see something like this (based on your TABULATE -- untested code):
[pre]
** assume data is sorted by group, priority and date;

proc report data=report01 nowd;
by company group;
column group priority benchmark w_date,(duration duration=durm duration=durs);
define group / group;
define priority / group;
define benchmark / mean;
define w_date/ across order=data f=monyy5.;
define duration / n;
define durm /mean;
define durs / sum;
break after group / summarize;
compute after group;
group = 'Group Mean';
endcomp;
run;
[/pre]

Are you really reporting on N, MEAN and SUM for each value of DATE or for each value of DURATION for each DATE????

You had an ALL in your TABULATE, this would convert to either a BREAK or an RBREAK statement.

Without knowing what you mean by a "mess" -- I assume you mean that you only got 1 report row???? That's because PROC REPORT needed some variables to "march down the rows" if you wanted to really duplicate the TABULATE output. Just having w_date on the COLUMN statement wouldn't necessarily put any "group" variables on each row.

Can you define "mess" or provide a LOG??? Also, trafficlighting DURMEAN will be a little more complicated with an ACROSS variable than I originally illustrated. You will still need a COMPUTE block, but you will have to use absolute column numbers (such as _c5_, _c8_) instead of the simple name DURMEAN -- that's because in an ACROSS situation, you need to tell REPORT -exactly- which report column you want to trafficlight and REPORT knows that it has multiple columns for DURMEAN -- one for the first date, one for the second date, etc, etc. So REPORT internally identifies those simple item names (the aliases) with absolute column numbers.

Well, without more information, I don't want to jump the gun. Trafficlighting should come second after the report stops being a "mess".

cynthia
Super Contributor
Posts: 358

Re: Traffic Ligts Cell Based On Another Cells Value

Cynthia:

Thanks for all the help - I did manage to find out the way to get multiple variables in the ACROSS by looking at this thread:

http://support.sas.com/forums/thread.jspa?threadID=11724

My report is being produced correctly and the dates across the top are in order using the ORDER=DATA setting.

Last thing (I promise) - getting back to the original problem - is there a way to now apply the traffic lighting to the variables that are under the ACROSS variable? For example, I would like to traffic light the 'mean duration' if it is below a target variable.

I looked at using absolute column names ("_C3_" etc.) but I don't know how many values are in my ACROSS variable.

Here what I have so far (I calculate N mean and sum in a prior step):

proc report data=report02 nowd split='*' missing;
by group title_group;

column group
res_group
priority
benchmark
w_date, (res_group_count res_group_sum res_group_mean);


define group / group 'Group' noprint;
define res_group / group 'Resolve Group';
define priority / group 'Priority';
define benchmark / group 'Target' format=time5.; order=data;
define res_group_count / analysis ' # ' format=3.;
define res_group_sum / analysis 'Sum' format=time12.;
define res_group_mean / analysis 'Mean' format=time12.;

If I use _C6_ to traffic light as in:

compute res_group_mean;
if _c6_ > benchmark then call define(_col_,'style','style={background=yellow}');
end;
endcomp;

then it will traffic light ALL of the mean columns (_C6_, _C9_, _C12_, etc) when the condition is met.
SAS Super FREQ
Posts: 8,642

Re: Traffic Ligts Cell Based On Another Cells Value

Hi:
You correctly defined the problem when you said you didn't know how many values there were for the ACROSS variables. But, the ACROSS items will fall into a pattern. For example, if you have 1, 2 or 3 items BEFORE the ACROSS item and 3 items under each ACROSS unique value, then the pattern is:
[pre]

**ONE vars before ACROSS;
<--ACROSS 1 --> <--ACROSS 2 -->
first _c2_ _c3_ _c4_ _c5_ _c6_ _c7_ ...repeat 3 absolute for every "unique across"


** TWO vars before ACROSS;
<--ACROSS 1 --> <--ACROSS 2 -->
first second _c3_ _c4_ _c5_ _c6_ _c7_ _c8_ ...repeat 3 absolute for every "unique across"


** THREE vars before ACROSS;
<--ACROSS 1 --> <--ACROSS 2 -->
first second third _c4_ _c5_ _c6_ _c7_ _c8_ _c9_ ...repeat 3 absolute for every "unique across"
[/pre]

So it is possible to write a little macro routine to generate the appropriate CALL DEFINE statements for you...in order for trafficlighting to work correctly, you'd need to have multiple IF tests for EACH absolute column and then reference the absolute column number in the condition and in the CALL DEFINE:
[pre]
compute durmean;
if _c6_ > benchmark.mean then call define ("_c6_", ...);
if _c9_ > benchmark.mean then call define ("_c9_",...);
if _c12_ > benchmark.mean then call define ("_c12_",...);
endcomp;
[/pre]

Such a macro program, to figure out the correct number of ACROSS variables and generate the repetitive IF statements is shown on pages 12-14 of this paper:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

Of course, if your BENCHMARK variable is also under an ACROSS variable, then you would have to refer to it with an absolute column number. If BENCHMARK is a regular item, not under an ACROSS item, then you would need to refer to it by its compound name, such as BENCHMARK.SUM or BENCHMARK.MEAN (depending on what statistic was used in the DEFINE statement). I worry that you show BENCHMARK as a GROUP item????? Is it character or numeric??? Remember that the normal behavior for GROUP or ORDER variables is for the repetitious values of a GROUP or ORDER variable to be suppressed -- so depending on your data, it might be possible for BENCHMARK to be blank on some rows -- if BENCHMARK is defined as a GROUP usage. On the other hand, you know your data, so if BENCHMARK is collapsing correctly to just 1 row, then you'd be OK to compare as:
[pre]
if _c6_ > BENCHMARK...
[/pre]

cynthia
Super Contributor
Posts: 358

Re: Traffic Ligts Cell Based On Another Cells Value

Cynthia:

Thanks to you my report now looks spectacular! Which (or course) means that I am going to have to keep adding things (hyperlinks, images, images, etc.) to the point it breaks again .... But that will be another day.

Finding the macro code to make the traffic lights work made all the difference - I adapted my code following that example and everything work great.

Thanks again.
SAS Super FREQ
Posts: 8,642

Re: Traffic Ligts Cell Based On Another Cells Value

That is a good paper, isn't it?? It is full of good tips and techniques. And this followup paper has even more good stuff:
http://support.sas.com/resources/papers/proceedings10/133-2010.pdf

cynthia
Occasional Contributor
Posts: 13

Re: Traffic Ligts Cell Based On Another Cells Value

I am relativily new to PROC REPORT. If you want to traffic light a row based on the the value of two cells having a particular value, do you have to define them as something other than display?

SAS Super FREQ
Posts: 8,642

Re: Traffic Ligts Cell Based On Another Cells Value

No, but really, it doesn't make sense for you to add your question to a 4 year old post. Generally, it is better for you to start a new post and if you need to, then provide a link to the older post. There is an example of exactly what you ask in this paper on pages 13 and 14 when the row color is changed http://support.sas.com/resources/papers/proceedings13/366-2013.pdf and the conditional trafficlighting is based on the values of 2 variables.

If you do NOT put a usage of display for a numeric variable, you might need to read about how to use a compound name when you reference a numeric variable. A compound name is a special reference for a numeric variable that has an analytic usage and, so if you had something like this:
define age / 'Student Age';
the correct compound name would be age.sum since the default usage for a numeric variable is analysis with a default statistic of SUM.

cynthia
Post a Question
Discussion Stats
  • 12 replies
  • 1469 views
  • 0 likes
  • 4 in conversation