topic Proc Report-color cell If exceeds LCL or UCL in SAS Programming

Proc Report-color cell If exceeds LCL or UCL

Ronein — Sun, 24 Jul 2022 06:12:44 GMT

Hello

I want to color cells in column "Wealth" by following rules:

If value of wealth is higher than UCL or lower than LCL then color it in light Red.

otherwise color it in light green.

The problem is that as you see all cells are in light red so it is not as I want.

Data have;
Input CustID month welath LCL UCL;
cards;
111 1 900 330 895
111 2 700 330 895
111 3 800 330 895
111 4 300 330 895
111 5 400 330 895
111 6 800 330 895
111 7 800 330 895
111 8 750 330 895
111 9 100 330 895
111 10 200 330 895
111 11 700 330 895
111 12 900 330 895
;
Run;
PROC REPORT DATA=have nowd split='*' style(header)={background=lightgrey font_size=10pt} style(column)={font_size=10pt} 
style(report)={vjust=c just=c cellpadding=0pt};   
COLUMN  CustID month welath LCL UCL;
DEFINE  CustID/Display;
DEFINE  month/Display;
DEFINE  welath/Display;
DEFINE  LCL/Display;
DEFINE  UCL/Display;
compute welath; 
if    _C4_ <=_C3_<=_C5_  then call define (_COL_,'style','style={background=lightgreen}'); 
else call define (_COL_,'style','style={background=lightred}'); 
endcomp;
Run;

Re: Proc Report-color cell If exceeds LCL or UCL

Kurt_Bremser — Sun, 24 Jul 2022 07:29:20 GMT

Your condition is an AND condition. Both comparisons must be satisfied. To get an OR, you need to split the condition and use OR.

Re: Proc Report-color cell If exceeds LCL or UCL

Ronein — Sun, 24 Jul 2022 09:13:04 GMT

Sorry,I didn't understand.

This code is also not working well- I get all cells of column wealth in red color and only few cells should be in red.

PROC REPORT DATA=have nowd split='*' style(header)={background=lightgrey font_size=10pt} style(column)={font_size=10pt} 
style(report)={vjust=c just=c cellpadding=0pt};   
COLUMN  CustID month welath LCL UCL;
DEFINE  CustID/Display;
DEFINE  month/Display;
DEFINE  welath/Display;
DEFINE  LCL/Display;
DEFINE  UCL/Display;
compute welath; 
IF _C3_<_C4_  OR  _C3_> _C5_ then call define (_COL_,'style','style={background=lightred}'); 
else call define (_COL_,'style','style={background=lightgreen}'); 
endcomp;
Run;

Re: Proc Report-color cell If exceeds LCL or UCL

PaigeMiller — Sun, 24 Jul 2022 11:00:38 GMT

PROC REPORT works left to right, using the order in the COLUMN statement. You cannot do a comparison, such as _C3_<_C4_ using variables to the right of _C3_ (and _C4_ is obviously to the right of _C3_). So you need to have a version of LCL and UCL to the left of WEALTH, but which doesn't print.

Data have;
Input CustID month wealth LCL UCL;
cards;
111 1 900 330 895
111 2 700 330 895
111 3 800 330 895
111 4 300 330 895
111 5 400 330 895
111 6 800 330 895
111 7 800 330 895
111 8 750 330 895
111 9 100 330 895
111 10 200 330 895
111 11 700 330 895
111 12 900 330 895
;

PROC REPORT DATA=have;   
COLUMN  CustID month lcl=lcl1 ucl=ucl1 wealth LCL UCL;
DEFINE  CustID/Display;
DEFINE  month/Display;
DEFINE  wealth/Display;
DEFINE  LCL/Display;
DEFINE  UCL/Display;
define lcl1 / noprint;
define ucl1 / noprint;
compute wealth; 
IF wealth<lcl1 or wealth>ucl1 then call define (_col_,'style','style={background=lightred}'); 
else call define (_col_,'style','style={background=lightgreen}'); 
endcomp;
Run;

People at SAS also recommend what I will call the "DUMMY technique" where you create a variable named DUMMY as the last variable in the COLUMN statement, which of course is to the right of all other variables since it is the last one, and then COMPUTE DUMMY works as well.

PROC REPORT DATA=have;   
COLUMN  CustID month wealth LCL UCL dummy;
DEFINE  CustID/Display;
DEFINE  month/Display;
DEFINE  wealth/Display;
DEFINE  LCL/Display;
DEFINE  UCL/Display;
define dummy / noprint;
compute dummy; 
if wealth<lcl or wealth>ucl then call define (3,'style','style={background=lightred}'); 
else call define (3,'style','style={background=lightgreen}'); 
endcomp;
run;

Of course, even the DUMMY isn't necessary if you use COMPUTE UCL, because everything is to the left of UCL, nothing is to the right.

PROC REPORT DATA=have;   
COLUMN  CustID month wealth LCL UCL;
DEFINE  CustID/Display;
DEFINE  month/Display;
DEFINE  wealth/Display;
DEFINE  LCL/Display;
DEFINE  UCL/Display;
compute ucl; 
IF wealth<lcl or wealth>ucl then call define (3,'style','style={background=lightred}'); 
else call define (3,'style','style={background=lightgreen}'); 
endcomp;
Run;

Re: Proc Report-color cell If exceeds LCL or UCL

Ksharp — Sun, 24 Jul 2022 10:28:38 GMT

PROC REPORT is calculating from left to right, so you need put LCL and UCL before weight. Like this :

Data have;
Input CustID month welath LCL UCL;
cards;
111 1 900 330 895
111 2 700 330 895
111 3 800 330 895
111 4 300 330 895
111 5 400 330 895
111 6 800 330 895
111 7 800 330 895
111 8 750 330 895
111 9 100 330 895
111 10 200 330 895
111 11 700 330 895
111 12 900 330 895
;
Run;
PROC REPORT DATA=have nowd split='*' style(header)={background=lightgrey font_size=10pt} style(column)={font_size=10pt} 
style(report)={vjust=c just=c cellpadding=0pt};   
COLUMN  CustID month LCL UCL welath LCL=_LCL UCL=_UCL;
DEFINE  CustID/Display;
DEFINE  month/Display;
DEFINE  welath/Display;
DEFINE  LCL/noprint;
DEFINE  UCL/noprint;
DEFINE  _LCL/display 'LCL';
DEFINE  _UCL/display 'UCL';

compute welath; 
if    _C3_ <=_C5_<=_C4_  then call define (_COL_,'style','style={background=lightgreen}'); 
else call define (_COL_,'style','style={background=lightred}'); 
endcomp;
Run;