I cannot get the compute block to change the background color of the SUM of the METRIX variable. I have created a temp variable t_METRIX that equals the METRIX sum/mean to give me a percentage that should change the background color in the compute block. Unfortunately it is not working so I know that I must be doing something wrong. Any help would be appreciated.
proc format;
value cback
0 - .00 = 'lightyellow'
1 - 100000 = 'white';
VALUE ratio
0 - 0.20 = 'red'
1.20 - HIGH = 'green';
run;
PROC REPORT DATA=ETLFW.ALL_TABLES_DAILY_LOAD_STATUS NOWD;
TITLE "Summary Tables for ETLFW.SCORECARD_TABLES_LOAD_STATUS";
COLUMN SOURCE_SYSTEM_NAME SCHEMA_NM TABLE_NM LOG_DT,(METRIC,(SUM)) METRIC,(MEAN);
DEFINE SOURCE_SYSTEM_NAME / GROUP;
DEFINE SCHEMA_NM / GROUP;
DEFINE TABLE_NM / GROUP;
DEFINE LOG_DT / ACROSS;
DEFINE METRIC / ANALYSIS SUM MEAN;
DEFINE SUM / format=comma6. style(COLUMN)={background=cback.} 'Row count';
DEFINE MEAN / 'Average Row count' format=comma6.;
compute METRIC;
t_METRIC = SUM / MEAN;
if t_METRIC > 0.20 and t_METRIC < 1 then
call define(_col_,'style','style=[background=lightred]');
else if t_METRIC > 1.20 and t_METRIC < 1 then
call define(_col_,'style','style=[background=lightgreen]');
endcomp;
;
run;
Doesn't work is awful vague.
Are there errors in the log?: Post the code and log in a code box opened with the <> to maintain formatting of error messages.
No output? Post any log in a code box.
Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.
I changed the code to :
proc format;
value cback
0 - .00 = 'lightyellow'
1 - 100000 = 'white';
VALUE ratio
0 - 0.20 = 'red'
1.20 - HIGH = 'green';
run;
PROC REPORT DATA=ETLFW.ALL_TABLES_DAILY_LOAD_STATUS NOWD;
TITLE "Summary Tables for ETLFW.SCORECARD_TABLES_LOAD_STATUS";
COLUMN SOURCE_SYSTEM_NAME SCHEMA_NM TABLE_NM LOG_DT,(METRIC,(SUM)) METRIC,(MEAN);
DEFINE SOURCE_SYSTEM_NAME / GROUP;
DEFINE SCHEMA_NM / GROUP;
DEFINE TABLE_NM / GROUP;
DEFINE LOG_DT / ACROSS;
DEFINE METRIC / ANALYSIS SUM MEAN;
DEFINE SUM / format=comma6. style(COLUMN)={background=cback.} 'Row count';
DEFINE MEAN / 'Average Row count' format=comma6.;
DEFINE t_METRIC / COMPUTED;
compute t_METRIC;
SUM=t_METRIC;
t_METRIC = SUM / MEAN;
if t_METRIC > 0.20 and t_METRIC < 1 then
call define(SUM,'style','style=[background=lightred]');
else if t_METRIC > 1.19 then
call define(SUM,'style','style=[background=lightgreen]');
endcomp;
;
run;
It runs and gives me output but the style statements in the compute block are not working. i have checked the values of the sum and mean from the output and calculated values that fall within the If statement. There is no errors in the log.
I guess I am trying to change this statement:
DEFINE SUM / format=comma6. style(COLUMN)={background=cback.} 'Row count'; this check to see if the value is 0
In the calculate block to change the SUM background color based on SUM / MEAN this is a range of percentage
Hi:
Without data, nobody can test your code. However, this is an ACROSS and color-coding example that I use. The color coding is done 2 different ways -- entirely with CALL DEFINE (for the ACROSS cells) and with a combo of format and CALL DEFINE (the TOTAL cells which are NOT ACROSS cells):
What you see at the bottom of the output, with the red arrows are the "internal" column names for all the columns placed in the REPORT page. Notice that for the ACROSS items, I have absolute column numbers. In my output, I have 2 possible values for the YEAR item, which is an ACROSS item. I also have SUM, MEAN and a calculated column under each possible year. So when I go to do my calculation in a COMPUTE block and my color coding, I have to know the absolute column numbers for the calculation and the color coding. You can manually calculate the absolute columns, or you can do what I do in my program and use OUT= with PROC REPORT to reveal the internal names to you. Notice that my column statement is slightly different than your column statement and uses ACROSS for the crossing with YEAR, but then uses aliases for the TOTAL columns on the far right.
Here's the code. I make the fakedata from a subset of sashelp.prdsale:
proc sort data=sashelp.prdsale out=fakedata;
where (actual le 400 and year = 1993 ) or (actual gt 600 and year = 1994);
by country region prodtype year;
run;
proc format;
value d2fmt 0-32='lightgreen'
33-42='peachpuff'
43-high='lightcyan';
run;
PROC REPORT DATA=fakedata NOWD out=work.rpout;
TITLE "Color Code under ACROSS items";
column country region prodtype year,((actual,SUM)(actual,MEAN) div_calc) ('Total' actual=asum actual=amean div2);
define country / group;
define region / group;
define prodtype / group;
define year / across;
define actual / analysis;
define SUM / 'Sum' format=comma8. ;
define MEAN / 'Average' format=comma8.;
define div_calc / "Calc" computed;
define asum / sum;
define amean/mean;
define div2 / "Actual Calc" computed
style(column)={background=d2fmt.};
compute div_calc;
_c6_ = _c4_ / _c5_;
_c9_ = _c7_ / _c8_;
if _c6_ > 25 then do;
call define('_c4_','style','style={background=pink}');
call define('_c6_','style','style={background=pink}');
end;
if _c9_ > 25 then do;
call define('_c7_','style','style={background=lightyellow}');
call define('_c9_','style','style={background=lightyellow}');
end;
endcomp;
compute div2;
length stylval $100;
div2 = asum / amean;
** use div2 value to set colors for the total sum and total mean;
** 3rd argument to call define can be a variable that resolves to the style string;
stylval = catt('style={background=',put(div2,d2fmt.),'}');
call define('asum','style',stylval);
call define('amean','style',stylval);
endcomp;
run;
title;
proc print data=work.rpout noobs;
title 'what are the internal column numbers';
format _numeric_ comma12.;
run;
title;
This paper goes into more detail.https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf
Cynthia
Thank you for the resources Cynthia, the problem that I am having is indeed the setting background colors for the ACROSS values.
My code is:
I am also trying to take each individual value(METRIC) SUM and divide it by the value(METRIC) MEAN to determine what color the background for the ACROSS values(METRIC). Tried the code to color the background light yellow but it did not work. Do you have any idea what I may be doing wrong?
I do not understand why I cannot get my compute block to calculate. I ran 2 proc reports with and without a compute block. When using the compute block, Actual Sales disappears and the compute variable is empty.
Hi:
This looks like an example from my paper. But the titles look wrong. Here's the complete program. I just tested it and it still works for me.
ods html file='c:\temp\demo4_start.html';
proc report data=sashelp.prdsale nowd;
title '1) Starting Report';
column country division product,(actual predict);
define country / group;
define division / group;
define product / across;
define actual / sum;
define predict / sum;
run;
ods html close;
title;
ods html file='c:\temp\demo4_change_hdr.html';
proc report data=sashelp.prdsale nowd;
title '2a) Using Spanning Headers and changing style ';
column ('Group Variables' country division) product,(actual predict);
define country / group style(header)={background=lightgreen};
define division / group style(header)={background=lightgreen};
define product / across ' ' style(header)={background=lightyellow};
define actual / sum style(header)={background=lightyellow};
define predict / sum style(header)={background=lightyellow};
run;
proc report data=sashelp.prdsale nowd
style(header)={background=lightgreen};
title '2b) Using Spanning Headers and changing style ';
title2 'The usual way to change if you want all headers to be the same color';
column ('Group Variables' country division) product,(actual predict);
define country / group;
define division / group;
define product / across ' ' style(header)={background=lightyellow};
define actual / sum style(header)={background=lightyellow};
define predict / sum style(header)={background=lightyellow};
run;
proc report data=sashelp.prdsale nowd
style(header)={background=lightgreen};
title '2c) Using Multiple Spanning Headers and changing style ';
title2 'The usual way to change if you want all headers to be the same color';
column ('Three Values' country) ('Current' division) product,(actual predict);
define country / group;
define division / group;
define product / across ' ' style(header)={background=lightyellow};
define actual / sum style(header)={background=lightyellow};
define predict / sum style(header)={background=lightyellow};
run;
ods html close;
title;
data prdsale;
length x y fakevar fakediv $25;
set sashelp.prdsale;
x='Three Values';
y='Current';
fakevar = country;
fakediv = division;
run;
ods html file='c:\temp\demo4_change_hdr_with_across.html';
proc report data=prdsale nowd out=abscol;
title '3) Alternate way to change spanning headers using ACROSS items and changing headers ';
column fakevar fakediv x,(country) y,(division) product,(actual predict);
define fakevar / group noprint ' ';
define fakediv / group noprint ' ';
define x / across ' ' style(header)={background=lightgreen};
define country / group style(header)={background=lightgreen};
define y / across ' ' style(header)={background=peachpuff};
define division / group style(header)={background=peachpuff};
define product / across ' ' style(header)={background=lightyellow};
define actual / sum style(header)={background=lightyellow};
define predict / sum style(header)={background=lightyellow};
run;
proc report data=prdsale nowd out=abscol;
title '3b) Fix values for Country and Division using Absolute Column Numbers';
column fakevar fakediv x,(country) y,(division) product,(actual predict);
define fakevar / group noprint ' ';
define fakediv / group noprint ' ';
define x / across ' ' style(header)={background=lightgreen};
define country / group style(header)={background=lightgreen};
define y / across ' ' style(header)={background=peachpuff};
define division / group style(header)={background=peachpuff};
define product / across ' ' style(header)={background=lightyellow};
define actual / sum style(header)={background=lightyellow};
define predict / sum style(header)={background=lightyellow};
compute country;
_c3_ = fakevar;
endcomp;
compute division;
_c4_ = fakediv;
endcomp;
run;
ods html close;
title;
What the output should look like:
1) Starting Report:
2a and 2b)
2c)
3 and 3b)
But this example only shows color coding the column headers. It does NOT show color-coding the report data cells. So I'm not sure any more what you want to accomplish because I thought you wanted to do traffic lighting on the report cells.
In the ACROSS paper, there's a different traffic-lighting example that shows this:
where some values are changed to green based on the values under an across, but other cell colors are changed based on a different variable value. The program that produced the above output is here:
** Highlight with multiple items For COUNTRY and PRODUCT, which is ACROSS;
** so trafficlight for CANADA where PRODUCT is CHAIR or DESK;
proc format;
value ovrpct 1.00-<1.075 = 'lightpink'
1.075-<1.10='lavender'
1.10-<1.50 = 'peachpuff'
1.50-high='verylightgreen'
.,other = 'white';
run;
ods _all_ close;
ods html file='c:\temp\demo5_change_style.html';
proc report data=sashelp.prdsale nowd;
title '1) Highlight based on ACROSS item and GROUP item';
column country division product,(actual predict pctcalc);
define country / group f=$char10.;
define division / group;
define product / across;
define actual / sum;
define predict / sum;
define pctcalc / computed 'Pct of Predict' f=percent9.2;
compute before country;
length altcountry $10;
altcountry = country;
endcomp;
compute pctcalc;
length svar8 svar11 $50 altcountry $10;
_c5_ = divide(_c3_ , _c4_);
_c8_ = divide(_c6_ , _c7_);
_c11_ = divide(_c9_ , _c10_);
_c14_ = divide(_c12_ , _c13_);
_c17_ = divide(_c15_ , _c16_);
if _c5_ gt 1.0 then do; call define('_c5_','style','style={foreground=green background=white fontweight=bold}'); end;
else do; call define('_c5_','style','style={foreground=black background=white fontweight=medium}'); end;
if _c14_ gt 1.0 then do; call define('_c14_','style','style={foreground=green background=white fontweight=bold}'); end;
else do; call define('_c14_','style','style={foreground=black background=white fontweight=medium}'); end;
if _c17_ gt 1.0 then do; call define('_c17_','style','style={foreground=green background=white fontweight=bold}'); end;
else do; call define('_c17_','style','style={foreground=black background=white fontweight=medium}');end;
if altcountry in ('GERMANY', 'U.S.A.') then do;
if _c8_ gt 1.0 then call define('_c8_','style','style={foreground=green background=white fontweight=bold}');
else call define('_c8_','style','style={foreground=black background=white fontweight=medium}');
if _c11_ gt 1.0 then call define('_c11_','style','style={foreground=green background=white fontweight=bold}');
else call define('_c11_','style','style={foreground=black background=white fontweight=medium}');
end;
else if altcountry = 'CANADA' and (_c8_ gt 1.0 or _c11_ gt 1.0) then do;
** only want to highlight CHAIR and DESK for Canada as shown in previous report;
svar8 = 'style={background=' || put(_c8_,ovrpct.)||'}';
svar11 = 'style={background=' || put(_c11_,ovrpct.)||'}';
call define('_c6_','style',svar8);
call define('_c7_','style',svar8);
call define('_c8_','style',svar8);
call define('_c9_','style',svar11);
call define('_c10_','style',svar11);
call define('_c11_','style',svar11);
end;
endcomp;
run;
ods html close;
ods html file='c:\temp\demo5_show_abs.html';
proc report data=sashelp.prdsale nowd out=show_abs;
title '2) How to Reveal Absolute Column Numbers';
column country division product,(actual predict pctcalc);
define country / group f=$char10.;
define division / group;
define product / across;
define actual / sum;
define predict / sum;
define pctcalc / computed 'Pct of Predict' f=percent9.2;
compute pctcalc;
_c5_ = divide(_c3_ , _c4_);
_c8_ = divide(_c6_ , _c7_);
_c11_ = divide(_c9_ , _c10_);
_c14_ = divide(_c12_ , _c13_);
_c17_ = divide(_c15_ , _c16_);
endcomp;
run;
proc print data=show_abs noobs;
title 'What Are Absolute Columns';
run;
ods html close;
title;
I just tested both programs and they produced the same output as that shown in my paper. I hope these working programs help you get the color coding you want.
Cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: