topic Re: Highlighting (Coloring) Empty cells in excel output in New SAS User

Highlighting (Coloring) Empty cells in excel output

shamie011 — Wed, 15 May 2024 18:25:27 GMT

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;

Re: Highlighting (Coloring) Empty cells in excel output

PaigeMiller — Wed, 15 May 2024 18:37:51 GMT

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;

Re: Highlighting (Coloring) Empty cells in excel output

shamie011 — Wed, 15 May 2024 18:42:30 GMT

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?

Re: Highlighting (Coloring) Empty cells in excel output

PaigeMiller — Wed, 15 May 2024 18:46:08 GMT

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 Communities

scroll down to the answer by @Ksharp

Re: Highlighting (Coloring) Empty cells in excel output

Cynthia_sas — Wed, 15 May 2024 19:05:53 GMT

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

Re: Highlighting (Coloring) Empty cells in excel output

Ksharp — Thu, 16 May 2024 00:55:56 GMT

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;