topic color cells in proc report if value greater than fix value in SAS Programming

color cells in proc report if value greater than fix value

Ronein — Thu, 27 Feb 2025 19:00:13 GMT

Hello

I want to color in red the cells with value greater than 28 in 3 fields: EXPD RCVD RCVD_ALL

what is the way to do it please via proc report?


data test;
input WD DAY $ EXPD RCVD RCVD_ALL NEWC $;                                                                                                                 
cards;    
-1 31JUL 29 49 56 Y
01 3AUG 26 26 77 Y
02 4AUG 77 45 0 N
03 5AUG 71 0 0 N
04 6AUG 44 0 0 N
;
RUN;

proc report data=TEST  nowd;
	column WD DAY EXPD RCVD RCVD_ALL;
	define WD / 'Working Day' display ;
	define DAY / ' Day' display;
	define EXPD / display;
	define RCVD / display;
	define RCVD_ALL / display;
	define NEWC/display;
quit;

Re: color cells in proc report if value greater than fix value

russt_sas — Thu, 27 Feb 2025 21:14:39 GMT

You can do something like this example:

data test;
input WD DAY $ EXPD RCVD RCVD_ALL NEWC $;
cards;
-1 31JUL 29 49 56 Y
01 3AUG 26 26 77 Y
02 4AUG 77 45 0 N
03 5AUG 71 0 0 N
04 6AUG 44 0 0 N
;
RUN;

ods listing close;
ods html file='c:\temp\test.html';
proc report data=TEST nowd;
column WD DAY EXPD RCVD RCVD_ALL newc;
define WD / 'Working Day' display ;
define DAY / ' Day' display;
define EXPD / display;
define RCVD / display;
define RCVD_ALL / display;
define NEWC/display;

compute expd;
if expd > 28 then call define(_col_,"style","style=[background=red]");
endcomp;

quit;
ods html close;
ods listing;

Re: color cells in proc report if value greater than fix value

ballardw — Thu, 27 Feb 2025 23:24:50 GMT

Another approach that may be easier if many variables will have the exact same coloring rule(s) would be a custom format.

Proc format ;
value highlight_28_
28<-high = 'red'
;

proc report data=TEST  nowd;
	column WD DAY EXPD RCVD RCVD_ALL;
	define WD / 'Working Day' display ;
	define DAY / ' Day' display;
	define EXPD / display style=[background=highlight_28_.];
	define RCVD / display style=[background=highlight_28_.];
	define RCVD_ALL / display style=[background=highlight_28_.];
	define NEWC/display;
quit;

I named the format for the single value you referenced in the request just as a reminder.

The advantage of a Format besides the single style reference is that the logic for the boundaries is moved out of the Proc Report and you need not write a bunch of if/then/else compute blocks. The logic in numeric formats is also a bit simpler - read the documentation.

The format also will allow multiple ranges easily.

If the boss changes, or wants a "what if we change the boundary value from X to Y" then you only need to change the format definition.

Character values can also be used in formats but ranges seldom do what is desired easily but a format to highlight values of NEWC could be provided. Note that since your Proc Report Column statement doesn't include NEWC there is a warning and nothing displayed in the result.