PROC REPORT: ODS EXCEL column and row colors based on other variable values

[WendyT]

SAS folks-

I’m trying to produce a visually coded spreadsheet in PROC REPORT.

I would like to change the background colors displayed when values change, and I need to do it on two variables: DATE and PERSON (grouped within date). I added two columns to the dataset using the MOD() function to determine when the values of DATE (DATE_MOD) and PERSON (PERSON_MOD) change. There is no pattern to the changes in either variable.

I would like to have the visual change for DATE only in that column, and the rest of the row change according to PERSON_MOD.  

As close as I have been able to get is to use _row_ on PERSON_MOD to color the rows, then change the font to bold for DATE_MOD with style/merge, and then use two extra compute blocks to “remove” the color from the first two columns, AREA and INV.

We have version 9.4M3 on a Windows server, and this will likely be run both live and in batch.

Any help you can give me would be very much appreciated!

Wendy T

%let outdir=&OUTDIR. ;

ods listing close ;

ods excel file="&OUTDIR.\REPORT_HAVE.xlsx"

               options(autofilter="all"

                       frozen_headers="on"

                       ) ;

proc report data=sas_example ;

column area inv date person size lbs other date_mod person_mod ;

define area--person_mod / display ;

compute date_mod ;

   if mod(date_mod,2) then do;

     call define(_row_, "style", "style=[fontweight=bold]");

     end ;

endcomp ;

compute person_mod ;

   if mod(person_mod,2) then do;

     call define(_row_, "style/merge", "style=[background=lightblue]");

   end ;

   else do;

     call define(_row_, "style/merge", "style=[background=white]");

   end ;

endcomp ;

compute area ;

      call define(_col_, "style", "style=[background=white]");

endcomp ;

compute inv ;

       call define(_col_, "style", "style=[background=white]");

endcomp ;

run ;

ods excel close ;

ods listing ;

Have                                                                                                                  Want

[Cynthia_sas · Accepted solution]

Hi:

  Rather then use _ROW_ and then try to remove the colors as you do, it might be easier to take another approach. Especially since I believe that you can only "touch" the row one time.

  A little known fact about COMPUTE blocks and PROC REPORT is that the last item on the report row can "touch" every cell on the row, especially for purposes of changing the style or format. So code like this works:

data newcars;
  set sashelp.cars;
  where make in ('Honda','Toyota', 'Volvo') and
        type in ('Hybrid', 'Sports', 'Wagon', 'Truck');
  if mpg_city ge 25 the  type_mod=1;
  else type_mod = 0;
  if invoice le 20000 then othr_mod=1;
  else othr_mod = 0;
run;
  
ods excel file='c:\temp\testcolor.xlsx';

title 'test highlighting';
proc report data=newcars;
column type make model msrp invoice mpg_city mpg_highway type_mod othr_mod;
define type /order;
define make / order;
define model / order;
define type_mod / display;
define othr_mod / display;
** 4 possible values for combo type_mod and othr_mod;
** type_mod=0 and othr_mod=0;
** type_mod=1 and othr_mod=1;
** type_mod=1 and othr_mod=0;
** type_mod=0 and othr_mod=1;
** can change whole row in the compute block for othr_mod;
** and can test values for all vars in row in compute block for othr_mod;

compute othr_mod;
if type_mod = 0 and othr_mod = 0 then do;
   call define('type', "style", "style=[background=lightyellow]");
   call define('make', "style", "style=[background=lightyellow]");
   call define('model', "style", "style=[background=lightyellow]");
   call define('msrp.sum', "style", "style=[background=lightblue]");
   call define('invoice.sum', "style", "style=[background=lightblue]");
   call define('mpg_city.sum', "style", "style=[background=lightblue]");
   call define('mpg_highway.sum', "style", "style=[background=lightblue]");
end;
else if type_mod = 1 and othr_mod = 1 then do;
   call define('type', "style", "style=[background=peachpuff]");
   call define('make', "style", "style=[background=peachpuff]");
   call define('model', "style", "style=[background=peachpuff]");
   call define('msrp.sum', "style", "style=[background=lightgreen]");
   call define('invoice.sum', "style", "style=[background=lightgreen]");
   call define('mpg_city.sum', "style", "style=[background=lightgreen]");
   call define('mpg_highway.sum', "style", "style=[background=lightgreen]");
end;
else if type_mod = 1 and othr_mod = 0 then do;
   call define('type', "style", "style=[background=cxdddddd]");
   call define('make', "style", "style=[background=cxdddddd]");
   call define('model', "style", "style=[background=cxdddddd]");
   call define('msrp.sum', "style", "style=[background=lightred]");
   call define('invoice.sum', "style", "style=[background=lightred]");
   call define('mpg_city.sum', "style", "style=[background=lightred]");
   call define('mpg_highway.sum', "style", "style=[background=lightred]");
end;
else if type_mod =0 and othr_mod = 1 then do;
   call define('type', "style", "style=[background=lavender]");
   call define('make', "style", "style=[background=lavender]");
   call define('model', "style", "style=[background=lavender]");
   call define('msrp.sum', "style", "style=[background=pink]");
   call define('invoice.sum', "style", "style=[background=pink]");
   call define('mpg_city.sum', "style", "style=[background=pink]");
   call define('mpg_highway.sum', "style", "style=[background=pink]");
end;
endcomp;
run;
ods excel close;

Since you did not provide data, I made some fake data from SASHELP.CARS in a subset with 2 new variables type_mod and othr_mod and just made values of 0 or 1 based on some fake conditions. But once I had type_mod and othr_mod, then in the last column on the report (in this case, OTHR_MOD), I can use a COMPUTE block to test the values of OTHR_MOD and TYPE_MOD and to change the style in every cell.

I put partial color coded code into the screen shot below to make it easier to track the COMPUTE block logic next to the output rendered in Excel.

  You will notice that some of my CALL DEFINE statements refer to composite item names like "msrp.sum" and "invoice.sum" because numeric variables, by default have a usage of analysis and to touch that cell, I need to either change their usage to disply or use the composite varname.statname reference in my CALL DEFINE. I did not bother with bolding or much fancy style changes. I thought changing the background color was enough of an example to point you in a direction.

  Note that in the CALL DEFINE when I "touch" a particular column by name, the name of the column must be in quotes.

cynthia

[WendyT]

Cynthia-

Thanks so much for your code!  I'll test out your approach first thing when I get back to the office.

Wendy T

[WendyT]

Cynthia-

Thank you so much - it works beautifully for my data.  😄

Wendy T