How can I perform Conditional Highlighting based on Calculated Values?

[deleted_user]

I want to perform conditional highlighting based on the average sales per month. Given a data that contains monthly sales by branch, I want to highlight those figures that exceeds the month's average sales.


.................... JAN...................FEB................MARCH
BRANCH1.....1000..................12000................3000
BRANCH2......2000.................1300..................4000
BRANCH3......6000.................1700.................17000
AVERAGE.....3000.................5000...................8000

Using this example, I want to highlight the values that exceeds the average (6000 for Jan, 12000 for feb and 17000)

[Cynthia_sas]

Hi:
If you let PROC REPORT calculate the report averages, then you can use the PROC REPORT code below to have conditional highlighting show in the report. There are other ways to do this, the method I show is the PROC REPORT method.

Note, that this is NOT the method you would use if you were going to do conditional highlighting in Web Report Studio with an Information Map.

cynthia
[pre]
data branchdata;
infile datalines;
input Branch $ Jan Feb March;
datalines;
BRANCH1 1000 12000 3000
BRANCH2 2000 1300 4000
BRANCH3 6000 1700 17000
;
run;

ods html file='c:\temp\hilite.html' style=egdefault;
proc report data=branchdata nowd;
title 'Proc Report Highlight';
title2 'Data has 1 obs per branch';
column branch jan feb march;
define branch /order;
define jan / mean;
define feb / mean;
define march/mean;
rbreak after / summarize;
compute before;
holdjan = jan.mean;
holdfeb = feb.mean;
holdmar = march.mean;
endcomp;
compute jan;
if jan.mean gt holdjan then
call define(_COL_,'style','style={foreground=red font_weight=bold}');
endcomp;
compute feb;
if feb.mean gt holdfeb then
call define(_COL_,'style','style={foreground=red font_weight=bold}');
endcomp;
compute march;
if march.mean gt holdmar then
call define(_COL_,'style','style={foreground=red font_weight=bold}');
endcomp;
compute after ;
branch = 'Average';
endcomp;
run;
ods html close;
[/pre]

[deleted_user]

Thank you, however, is there a way that we could produce the same output using PROC TABULATE? Message was edited by: Axe

[Cynthia_sas]

Hi:
Yes, there is a method with PROC TABULATE, but it involves making one pass through the data (one way or another) to find out what the overall averages are. That's because the way you do conditional highlighting with PROC TABULATE is with a user-defined format and in order to code the format, you need to know the averages -before- you can use them in PROC TABULATE.

You have 2 choices for how to build the user-defined format -- either you hardcode the averages or you create macro variables with the averages. The program below shows the second method -- with macro variables to hold the averages for each month.

After you have created the user-defined format, then you USE the format in a STYLE= override within PROC TABULATE code. This example uses the same BRANCHDATA as the previous example, with one obs per branch.

cynthia
[pre]
data branchdata;
infile datalines;
input Branch $ Jan Feb March;
datalines;
BRANCH1 1000 12000 3000
BRANCH2 2000 1300 4000
BRANCH3 6000 1700 17000
;
run;

** Make one pass through the data to get;
** averages for each month and create ;
** a macro variable for each average.;

proc sql noprint;
select mean(jan),
mean(feb),
mean(march)
into :m1, :m2, :m3
from work.branchdata;
quit;

** Prove that macro variables hold the;
** numbers of interest.;
%put ---- jan mean is: &m1;
%put ---- feb mean is &m2;
%put ---- mar mean is &m3;


proc format;
** In order to code this format, you have;
** to find out what the average is for;
** each month. Either you hardcode the value;
** or in an advanced solution, you use macro;
** variables as shown here. In this instance,;
** the macro variables are being used as;
** numeric constants, so they are not quoted.;
** The label for the format is the foreground color;
** which should be assigned.;

value janmean &m1-high = 'red'
other = 'black';

value febmean &m2-high = 'red'
other = 'black';

value marmean &m3-high = 'red'
other = 'black';

value allmean other = 'black';
run;

ods listing close;
ods html file='c:\temp\hilite_tab.html' style=egdefault;
proc tabulate data=branchdata f=comma6.;
title 'Proc Tabulate Highlight';
class branch;
var jan feb march;
table
branch*sum=' ' all*mean=' '*{s={foreground=allmean. font_weight=bold}},
(jan*{s={foreground=janmean.}}
feb*{s={foreground=febmean.}}
march*{s={foreground=marmean.}})
/style_precedence=row row=float;
run;
ods html close;
[/pre]

[deleted_user]

Thanks for your help!