topic Re: proc report-color cells in SAS Programming

proc report-color cells

Ronein — Mon, 26 Oct 2020 12:21:37 GMT

Hello

I want to color values if the exceed UCL1 or UCL2 (As you can see in the code).

My question:

What is the way to write one compute statements for multiple columns?


Data RedLights;
Input Subject UCL1 UCL2 Val2009 Val2006 Val2003 Val1912;
cards;
1 15 20 10 8 9 18
2 13 14 15 10 9 8
3 70 80 50 45 90 30
4 25 35 30 16 15 12
5 30 40 17 20 25 30
;
run;


proc report data=RedLights nowd;
column Subject UCL1 UCL2 Val2009 Val2006 Val2003 Val1912;
define Subject / display  'Subjecy Numner';
define UCL1 / display  "Upper Control Limit1";
define UCL2 / display  "Upper Control Limit2";
define Val2009 / display 'Sep-2020';
define Val2006 / display 'JUN-2020';
define Val2003 / display 'MAR-2020';
define Val1912 / display 'DEC-2019';
compute Val2009;
if Val2009>UCL1 and Val2009>UCL2 then call define(_col_,"style","style={background=red}");
else if Val2009>UCL1 and Val2009<=UCL2 then call define(_col_,"style","style={background=lightpink}");
else if Val2009<=UCL1 and Val2009>UCL2 then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute Val2006;
if Val2006>UCL1 and Val2006>UCL2 then call define(_col_,"style","style={background=red}");
else if Val2006>UCL1 and Val2006<=UCL2 then call define(_col_,"style","style={background=lightpink}");
else if Val2006<=UCL1 and Val2006>UCL2 then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute Val2003;
if Val2003>UCL1 and Val2003>UCL2 then call define(_col_,"style","style={background=red}");
else if Val2003>UCL1 and Val2003<=UCL2 then call define(_col_,"style","style={background=lightpink}");
else if Val2003<=UCL1 and Val2003>UCL2 then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute Val1912;
if Val1912>UCL1 and Val1912>UCL2 then call define(_col_,"style","style={background=red}");
else if Val1912>UCL1 and Val1912<=UCL2 then call define(_col_,"style","style={background=lightpink}");
else if Val1912<=UCL1 and Val1912>UCL2 then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;
run;

/***My try to do it****/
/***My try to do it****/
/***My try to do it****/
%let vector =Val2009+Val2006+Val2003+Val1912;
%let k=4;

%macro mmacro1; 
%do j=1 %to &k.;
%let Val=%scan(&vector1.,&j.,+);
compute &val.;
if &val.>UCL1 and &val.>UCL2 then call define(_col_,"style","style={background=red}");
else if &val.>UCL1 and &val.<=UCL2 then call define(_col_,"style","style={background=lightpink}");
else if &val.<=UCL1 and &val.>UCL2 then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;
%end;
%mend mmacro1;


proc report data=RedLights nowd;
column Subject UCL1 UCL2 Val2009 Val2006 Val2003 Val1912;
define Subject / display  'Subjecy Numner';
define UCL1 / display  "Upper Control Limit1";
define UCL2 / display  "Upper Control Limit2";
define Val2009 / display 'Sep-2020';
define Val2006 / display 'JUN-2020';
define Val2003 / display 'MAR-2020';
define Val1912 / display 'DEC-2019';
%mmacro1;
run;

Re: proc report-color cells

PaigeMiller — Mon, 26 Oct 2020 12:29:56 GMT

Don't make us guess. What is wrong with the macro solution you provided?

Re: proc report-color cells

shweta_d_singh — Mon, 26 Oct 2020 13:19:59 GMT

You have used vector1 call in %let I think that is a typo error but you macro works well if that is corrected.

%let vector =Val2009+Val2006+Val2003+Val1912;
%let k=4;

%macro mmacro1; 
%do j=1 %to &k.;
%let Val=%scan(&vector1.,&j.,+);

Re: proc report-color cells

RichardDeVen — Mon, 26 Oct 2020 15:05:14 GMT

Yes.

Add a dummy right most column that is NOPRINT and have a compute block for it. Because the dummy column is right most, all the values of the columns to the left are available for examination and manipulation. Create an array of the columns that you want to highlight in the same manner and loop over that array for evaluating the criteria and applying the style.

Example:

Tip: Default VAL: to lightgreen background means fewer CALLD DEFINE statements coded

proc report data=RedLights nowd;
  column Subject UCL1 UCL2 Val2009 Val2006 Val2003 Val1912 last;

  define Subject / display  'Subject/Number';

  define UCL1 / display  "Upper/Control/Limit #1";
  define UCL2 / display  "Upper/Control/Limit #2";

  define Val2009 / display 'SEP-2020' style(column)=[background=lightgreen];
  define Val2006 / display 'JUN-2020' style(column)=[background=lightgreen];
  define Val2003 / display 'MAR-2020' style(column)=[background=lightgreen];
  define Val1912 / display 'DEC-2019' style(column)=[background=lightgreen];

  define last / noprint;

compute last;

  array vals val2009 val2006 val2003 val1912;

  do index = 1 to dim (vals);
    val = vals(index);
    vname = vname(vals(index));

    if val <  UCL1 and val <  UCL2 then continue;

*    call execute('%put NOTE: ' || catx(' ', vname , val));  * debug;

    if val <= UCL1 and val >  UCL2 then
      call define(trim(vname),"style","style={background=pink}");
    else
    if val >  UCL1 and val <= UCL2 then
      call define(trim(vname),"style","style={background=lightpink}");
    else
      call define(trim(vname),"style","style={background=red}");
  end;

endcomp;

Re: proc report-color cells

Ronein — Tue, 27 Oct 2020 06:19:54 GMT

Thanks!

Some questions please:

1-Is it necessary to write the statement

if val < UCL1 and val < UCL2 then continue;

What is it used for?

Re: proc report-color cells

RichardDeVen — Tue, 27 Oct 2020 09:48:13 GMT

The continue statement skips to the next iteration of the loop.

When an iteration is skipped the default background color according to the define <column> / style(column)= [background=<color>] will be in effect.