ods tagsets.ExcelXP conditional cell formatting syntax question

[biglerc]

Hello!

I am using proc report and need to conditionally format 4 variables' cells.  The cell must be a specified color if the value is greater than 0.   When I run the proc report without ODS, the screen output is correct and there are no log errors. 

However, when I include the ODS portion of the code, I get log errors and no Excel output.  and log errors of:

WARNING: In Event: style_over_ride, Invalid Expression: eval $style_list[$cell_class ] $style_list[$cell_class ] +1
ERROR: WHERE clause operator requires numeric variables.
WARNING: In Event: style_over_ride, Invalid Expression: eval $style_list[$cell_class ] $style_list[$cell_class ] +1
ERROR: WHERE clause operator requires numeric variables

I was wondering if someone could tell me where my error is?  I've scoured Google and the boards to no avail.  Is it correct to have a separate compute block for each variable?  The four variables that I am applying the computer blocks for are of numeric type.

Thanks for any guidance!

ods listing close;

ods tagsets.ExcelXP
options (

          embedded_titles ='yes'
          autofilter='yes'
          frozen_headers ='3'
          autofit_height='yes'
          gridlines='yes'
          sheet_name="Query to Close"
          width_fudge='1'
)
path="&output.\Query Report"
file="Queries_Reports_All_&str_today._new.xml"
style=normal;

ods tagsets.ExcelXP
options ( sheet_name="Query by Date" );

Title1 "Open Query Summary by Date";

proc report data=query_open_statistics (drop=sitenum) nowd headline headskip spacing=3 split='|' missing ls=250 ps=42 ;
where Subject_Name='All';

column _all_;

define subject_name / display;
define Query_Open_L15 / display;
define Query_Open_15_30 / display;
define Query_Open_30_45 / display;
define Query_Open_GE45 / display;

compute Query_Open_L15;
      if Query_Open_L15 gt 0 then do;
          call define("Query_Open_L15",'style','style={background=light green}');
     end;
endcomp;

compute Query_Open_15_30;
     if Query_Open_15_30 gt 0 then do;
          call define("Query_Open_15_30",'style','style={background=yellow}');
     end;
endcomp;

compute Query_Open_30_45;
     if Query_Open_30_45 gt 0 then do;
          call define("Query_Open_30_45",'style','style={background=red}');
     end;
endcomp;

compute Query_Open_GE45;
     if Query_Open_GE45 ne . and Query_Open_GE45 gt 0 then do;
          call define("Query_Open_GE45",'style','style={background=blue}');
     end;
endcomp;

run;

ods tagsets.ExcelXP close;
%put &output.;
ods listing;

.

[ballardw]

I suggest using BACKGROUNDCOLOR instead of just background

also remove the space

background=light green}');

color names don't allow spaces

It helps to post the entire code along with the messages from the LOG. Copy and paste into a code box opened with the forum's {I} or "running man" icons to preserver formatting as often errors are accompanied by diagnostics but the forum main message windows will reformat the text so the diagnostics are not where they should be. 

[biglerc]

Hi, thanks for the quick response!  I tried replacing BACKGROUND with BACKGROUNDCOLOR and I get the same result, unforunately.

I did post my code and log error (copied directly from my sas session), so could you please clarify your other comment?  If I can provide anything else that would help, I'm very very happy to!

The most confusing part of the log error is that it says the where clause requires numeric variables - but even when I remove my where clause, I still get that message, so it must be something cryptic.

Log again is:

WARNING: In Event: style_over_ride, Invalid Expression: eval $style_list[$cell_class ] $style_list[$cell_class ] +1
ERROR: WHERE clause operator requires numeric variables.
WARNING: In Event: style_over_ride, Invalid Expression: eval $style_list[$cell_class ] $style_list[$cell_class ] +1
ERROR: WHERE clause operator requires numeric variables.
WARNING: In Event: style_over_ride, Invalid Expression: eval $style_list[$cell_class ] $style_list[$cell_class ] +1
ERROR: WHERE clause operator requires numeric variables.
WARNING: In Event: style_over_ride, Invalid Expression: eval $style_list[$cell_class ] $style_list[$cell_class ] +1
ERROR: WHERE clause operator requires numeric variables.
NOTE: There were 1 observations read from the data set WORK.QUERY_OPEN_STATISTICS.

Thank you again for your time!

[Cynthia_sas]

Hi: There are some oddities in your code that are just distracting since they are ignored by ODS destinations. Options like

headline headskip spacing ls ps 

are ALL ignored by all ODS destinations except for LISTING.

Next if you have this:

compute Query_Open_L15;
   if Query_Open_L15 gt 0 then do;
      call define("Query_Open_L15",'style','style={background=light green}');
   end;
endcomp;

Where the variable being changed in the CALL DEFINE is the same as the variable name in the COMPUTE block, then you can do THIS:

compute Query_Open_L15;
   if Query_Open_L15 gt 0 then do;
      call define(_col_,'style','style={background=light green}');
   end;
endcomp;

Because _COL_ always refers to the variable named on the COMPUTE block. You only need a quoted string when you are referring to a different column in the COMPUTE block.

When I use slightly modified code, using SAS 9.4 M5 and either ODS EXCEL or ODS TAGSETS.EXCELXP, Since you did not provide data, I made some fake data with 15 rows, but only 10 rows had a Subject_Name='All'

** make 15 rows but only 10 with All for Subject_Name;
data query_open_statistics;
  infile datalines;
  input sitenum $ Subject_Name $ Query_Open_L15 Query_Open_15_30
        Query_Open_30_45 Query_Open_GE45;
  datalines;
X123 All 154 74  458 0 
X123 All 233 112 283 0 
X123 VVV 148 120 509 . 
X123 All 0   62  712 283
Y234 All 232 112 608 . 
Y234 All 415 0   625 193 
Y234 WWW 470 249 359 130 
Y234 XXX 904 435 218 183 
Y234 All 412 0 158 831
Z987 All 583 309 0   244 
Z987 All 383 184 248 . 
Z987 All 0   66  0   294 
Z987 YYY 272 135 246 0 
Z987 All 432 66  534 294 
Z984 ZZZ 123 456 789 849
;
run;
   
** test with ODS EXCEL and ODS TAGSETS.EXCELXP;
ods excel file='c:\temp\output\QRA_test.xlsx' style=normal
    options ( embedded_titles ='yes'
              sheet_name="Query by Date");

ods tagsets.ExcelXP path="c:\temp\output"
    file="QRA_XP_test.xml" style=normal
    options ( embedded_titles ='yes'
              sheet_name="Query by Date");
 
 Title1 "Open Query Summary by Date";

proc report data=query_open_statistics (drop=sitenum) nowd 
     split='|' missing ;
where Subject_Name='All';

column subject_name Query_Open_L15 Query_Open_15_30 
       Query_Open_30_45 Query_Open_GE45;

define subject_name / display;
define Query_Open_L15 / display;
define Query_Open_15_30 / display;
define Query_Open_30_45 / display;
define Query_Open_GE45 / display;
 
compute Query_Open_L15;
      if Query_Open_L15 gt 0 then do;
          call define(_col_,'style','style={background=lightgreen}');
     end;
endcomp;
 
compute Query_Open_15_30;
     if Query_Open_15_30 gt 0 then do;
          call define(_col_,'style','style={background=lightyellow}');
     end;
endcomp;
 
compute Query_Open_30_45;
     if Query_Open_30_45 gt 0 then do;
          call define(_col_,'style','style={background=lightred}');
     end;
endcomp;

compute Query_Open_GE45;
     if Query_Open_GE45 ne . and Query_Open_GE45 gt 0 then do;
          call define(_col_,'style','style={background=lightblue}');
     end;
endcomp;
run;
 
ods tagsets.ExcelXP close;
ods excel close;
 

I do not get the error messages that you report.

And here is the output I get from TAGSETS.EXCELXP (the ODS EXCEL output looks the same):

Starting from working code with test data, you should be able to add suboptions until you find out what is breaking.

Hope this helps,

Cynthia