Highlighting (Coloring) Empty cells in excel output

[shamie011]

I have a exported an excel report that contains empty cells and would like to write a code that would color all empty cells in the excel output.

I have the following code which only highlights/colors empty cells in the NAME column, however I'd like to highlight all empty cells without having to write out code for all 47 columns that I have in my report :

PROC REPORT DATA = STAGE1;
DEFINE NAME -- REFERRAL_BASIS/ DISPLAY;

COMPUTE NAME;
IF NAME= '' THEN
CALL DEFINE('NAME', "STYLE", "STYLE=[BACKGROUND=CYAN]");
ENDCOMP;
RUN;

[PaigeMiller]

I can't reproduce this. The code works properly on my example data. Please provide (a portion) of your actual data set STAGE1, as working SAS data step code (examples and instructions), and not in any other format.

data class;
    set class;
    if name="Jane" then call missing(name);
run;

ods excel file='test.xlsx';
PROC REPORT DATA = class;
DEFINE name--weight/ DISPLAY;
COMPUTE NAME;
IF NAME= '' THEN
CALL DEFINE('NAME', "STYLE", "STYLE=[BACKGROUND=CYAN]");
ENDCOMP;
RUN;
ods excel close;

[shamie011]

The code works fine, I guess my question is do I have to write out all the 47 columns that I have that I'd want to color if they have empty cells?

[PaigeMiller]

Sorry, I didn't read it carefully.

The answer is here: Solved: Re: How to use proc report with same compute block for many variables ... - SAS Support Comm...

scroll down to the answer by @Ksharp 

[Cynthia_sas]

Hi:

  I was able to make it work for more variables with ARRAY processing in the COMPUTE block. I changed the COMPUTE block to be placed on the last report item in the report row (WEIGHT instead of NAME) because then I could do everything for each column in one COMPUTE block. But I still needed to have the column names listed in the ARRAY statement.  I took advantage of the fact that you can use temporary variables like "var_col" in the CALL DEFINE statement.

Cynthia

[Ksharp]

For your this special case, you could try keyword _CHARACTER_ and _NUMERIC_ to apply traffic light style to all these variables.

data class;
    set sashelp.class;
    if _n_ in (1:4) then call missing(name);
    if _n_ in (6 10:12) then call missing(age,sex,height);
run;


proc format;
value $fmt
' '='cyan'
other='white'
;
value _fmt
.='cyan'
other='white'
;
run;
option missing=' ';
ods excel file='c:\temp\test.xlsx';
PROC REPORT DATA = class nowd;
DEFINE _character_/display style={background=$fmt.};
DEFINE _numeric_/display style={background=_fmt.};
RUN;
ods excel close;