topic Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia in ODS and Base Reporting

Color a table cell using PROC REPORT, using a numeric variable that is different from cell variable

Dan16 — Fri, 28 May 2021 19:46:14 GMT

Hello,

I would like to color a cell of a table using PROC REPORT, but using a variable that is different from the variable that is in the cell. The problem is reproduceable from this following simple program, and described in greater detail following the simple program.

Sample program:

data test;
input a b c;
cards;
1 2 3
1 2 4
1 2 4
2 4 5
2 4 1
2 4 6
;

proc print;
var a b c;
run;


proc report data=test nowindows headline headskip;
columns a b c ;
define a / display ' Level ' width =6;
define b / display ' Home ' width =6;
define c / display ' color' width = 6;
compute b;
if b > 3 then call define(_col_,"style","style={background = red");
endcompute;
*compute a;
* if c > 4 then call define(_col_,"style","style={background = green");
* endcompute;
compute a;
if _c3_ > 4 then call define(_col_,"style","style={background = green");
endcompute;
run;

In the above program, the first compute statement colors the Home column cells red for those cells where the Home column is greater than 3. This works fine.

However, I would really like to color the cells of Level green, based on the values of the color column.

The second compute statement (that is commented out) does the obvious, which does not work.

The third compute statement uses the _Cn_ approach to identifying the column to create the color values. No error or warnings are produced, but the colored cells do not appear on the table.

The resulting table should have two cells in the Level column colored green. The 4th and 6th cell.

Note that the reason wanting to do this is not clear from the test data, but makes sense in the table I am creating.

Any thoughts from would be greatly appreciated. (Note I could not find anything on this in my searching of the archives).

Sincerely,

Dan

Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia

Reeza — Fri, 28 May 2021 19:50:04 GMT

proc report data=test nowindows headline headskip;
columns a b c ;
define a / display ' Level ' width =6;
define b / display ' Home ' width =6;
define c / display ' color' width = 6;

compute c;
 if c > 4 then call define(a,"style","style={background = green");
 endcompute;

run;

Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia

ballardw — Fri, 28 May 2021 19:57:41 GMT

Here's one way: change the column order.

proc report data=test nowindows headline headskip;
columns c a b  ;
define a / display ' Level ' width =6;
define b / display ' Home ' width =6;
define c / display ' color' width = 6;
compute b;
if b > 3 then call define(_col_,"style","style={background = red");
endcompute;
compute a;
  if c > 4 then call define(_col_,"style","style={background = green");
endcompute;
run;

Why do this? Proc Report builds the report from left to right. So while construction the column for A, when the column statement is A B C, the value of C (to the Right of A) is not "available" yet.

Same problem with the _cn_ approach, column 3 not available for A (_c1_)

Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia

JeffMeyers — Fri, 28 May 2021 21:51:04 GMT

There's actually no need to change the column order. The variable you put in the compute block is rather arbitrary to what is in the compute block, so the easiest way to do this is to always put the last variable in the COLUMNS statement in the compute statement. This way all the variables are always initialized and you can do whatever style changes are needed (even if they are on a different column than the one in the compute statement and even if there are multiple changes). I think @Reeza was touching on this in their post.

proc report data=test nowindows headline headskip;
columns a b c;
define a / display ' Level ' width =6;
define b / display ' Home ' width =6;
define c / display ' color' width = 6;
compute c;
if b > 3 then call define('b',"style","style={background = red");
if c > 4 then call define('a',"style","style={background = green");
endcompute;
run;

Re: Color a table cell using PROC REPORT, using a numeric variable that is different from cell varia

Cynthia_sas — Sat, 29 May 2021 21:06:59 GMT

Hi:

Thanks for posting data. Using your test data and @Reeza's suggestion:

As you can see, I get the correct color coding in columns A and B as you intended with your original code.

I explain in the screen shot that the COMPUTE block for A cannot test the value of C because of the PROC REPORT "left-to-right" rule and a timing issue. At the point in time when PROC REPORT is placing A on the report row, it has not yet placed C on the report row. So the COMPUTE block for A cannot test the value of C. However, you can test the value for C in the COMPUTE block for C. At that point in time, the cell for column A has been placed on the report row, so based on the result of the IF in the COMPUTE block a CALL DEFINE in the COMPUTE block for C can "touch" a variable that appears before C in the COLUMN statement. The only trick is that the variable name you want to change with the call define must be quoted, as I show in my code.

Just a few other comments. HEADLINE, HEADSKIP and WIDTH= are ignored by ODS destinations that would use your STYLE. These are LISTING only options and ignored by ODS, so I removed them from my code. I also changed the colors to lightgreen and lightred to make the numbers in the cells more readable.

Hope this helps,

Cynthia