How to use the compute in a Proc Report not working

[joney]

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;

[joney]

This does not work either:

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 NOPRINT;
compute 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.20 then
call define(SUM,'style','style=[background=lightgreen]');
endcomp;
;
run;

[ballardw]

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.

[joney]

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.

[joney]

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

[Cynthia_sas]

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

[joney]

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?

[joney]

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.

[Cynthia_sas]

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

[Cynthia_sas]

Hi:
I do not see div_calc in your COLUMN statement. PROC REPORT executes the COMPUTE blocks as items are being placed on the report row. In the code I posted (which you should be able to run) both div_calc and div2 are on the COLUMN statement. As it stands without some sample data and code that can be copied and pasted, it is impossible to make constructive suggestions about your issues.

I suspect that you are somehow violating the "left to right" rule of PROC REPORT so that the highlighting doesn't work. In brief if you had a column statement like this:
column var1 var2 var3 var4;

No matter what usage you have for any of the variables, PROC REPORT will place values on the report row by working one row at a time, placing the report cells starting on the left and working toward the right. In the above column statement, the first value placed on the report row will be VAR1, the second value placed on the report row will be VAR2, etc.

If I want to conditionally compare VAR2 and VAR3-- for example if VAR2 is GT VAR3, then I want to do color-coding. I CANNOT do this in a COMPUTE block for VAR2. That's because at the point in time when VAR2 is being placed on the report row, the values for VAR3 and VAR4 are still unknown to PROC REPORT. The PROC REPORT process only has visibility of the values that have been placed on the report row. So in a COMPUTE block for VAR3, I can compare VAR2 and VAR3 -- but I cannot compare them in a COMPUTE block for VAR2 -- this is because of the left to right rule of PROC REPORT.

And the other complication of the left to right rule comes into play with cells that are under ACROSS items. You have to know the correct absolute numbers to use in order to make any comparisons or changes in the COMPUTE block.

If you run the code I posted, without changing it, you should get the same results as in the screen shot I posted. If you can't post any data so people could run your code, then the only thing I can suggest is to start with a working example, understand how the working example works and then apply those concepts to your code.
Cynthia