Proc Report Across and color cells if exceeds UCL

Ronein — Wed, 22 Jul 2020 07:42:11 GMT

Hello

I have the following problem.

I have a wide summary report.

I want to display this report and color cells IF their limit exceeds Upper control limit.

I am trying to use use proc report b change the structure of table from wide to long and then using across to display the table and it is 100% good.

My problem is how to tell SAS to color cells IF their value exceeds UCL.

Moreover in real world there are many columns so it is better to write condtion for all columns (all columns with values )

Data WideReport;
input Subject UCL V_1901 V_1902 V_1903 V_1904 V1905 V1906;
cards;
1 12 10 11 12 13 14 15  
2 15 12 14 16 18 20 22
3 16 13 14 15 16 17 18 
4 17 18 17 16 15 14 13
5 14 17 14 19 12 11 14
;
run;


proc transpose data=WideReport out=LongReport(rename=(value1=value)) prefix=value;
by Subject;
var V_:;
Run;

title;
proc report data=LongReport ;
    columns Subject _NAME_, value;
    define Subject / group "General_Topic_Num";
    define _NAME_ / across "YYMM";
	define value/ SUM  "value";
run;

Re: Proc Report Across and color cells if exceeds UCL

andreas_lds — Wed, 22 Jul 2020 11:17:21 GMT

Hopefully someone with a better understanding of compute-blocks in proc report has a better idea:

proc sql noprint;
   select count(Name)
      into :numOfVars trimmed
      from sashelp.vcolumn
         where LibName = 'WORK' and MemName = 'WIDEREPORT' and upcase(Name) like "V^_%" escape '^'
   ;
quit;

data work.Subject2UCL;
   set work.WideReport(
         keep= Subject UCL
         rename= (Subject=Start UCL=Label)
   );
   
   retain FmtName "Subject2UCL" Type "i";
run;

proc format cntlin=work.Subject2UCL;
run;


proc report data=LongReport ;
   columns Subject _NAME_, value;
   define Subject / group "General_Topic_Num";
   define _NAME_ / across "YYMM";
   define value / SUM  "value";
   
   compute value;
      length ucl cellValue 8 var $ 32;
      
      ucl = input(cats(Subject), Subject2UCL.);
      
      do i = 2 to &numOfVars. + 1;
         var = cats('_c', i, '_');
         cellValue = input(vvaluex(var), best.);
         
         if cellValue > ucl then call define(var,"style","style={background=lightred}");
         else call define(var,"style","style={background=lightgreen}");
      end;
   endcomp;   
run;

When using across you have to use the column-numbers to access the (computed) values. This is somewhat annoying, because the number of columns created by proc recport depends on the data provided.

topic Re: Proc Report Across and color cells if exceeds UCL in SAS Programming

Proc Report Across and color cells if exceeds UCL

Re: Proc Report Across and color cells if exceeds UCL