topic Re: ExcelXP -- Warning: In Event: style_over_ride, Invalid expression in ODS and Base Reporting

ExcelXP -- Warning: In Event: style_over_ride, Invalid expression

cooool — Thu, 09 Jul 2015 19:56:17 GMT

Hi all,

I used ExcelXP.sas to create a .xml in SAS 9.1.3. The program worked fine. But after upgraded to SAS9.3, I got this error:

Warning: In Event: style_over_ride, Invalid Expression: eval $style_list[$cell_class] $style_list[$cell_class] + 1

in a nutshell, I want to do traffic lighting in compute block based on the value of variables :

proc format ;

value color 0='black', 1='yellow', 2='red';

value fntsyle 0='roman' 1='italic';

run;

Dataset:

Var1 Var2 Var3

grade 0 0 0

grade 2 1 0

grade 4 2 1

ODS tagsets.excelXP options (sheet_interval='bygroup' suppress_bylines='yes' frozen_headers='yes');

proc report nowindows;

column var1 var2 var3 dummy;

define var1/display;

define var2/display noprint;

define var3/display noprint;

define dummy/computed noprint;

compute dummy;

call define ('var1', 'style', 'style=[background=' || put (var2, color.) || ' font_style=' || put (var3, fntstyle.) || ']' );

endcomp;

run;

quit;

Re: ExcelXP -- Warning: In Event: style_over_ride, Invalid expression

Cynthia_sas — Fri, 10 Jul 2015 12:22:44 GMT

Hi:

Is your format actually working? Are there really commas in the code you submitted in the format for color? Also, you spelled the other format "fntsyle" in the PROC FORMAT step, but then refer to it as "fntstyle" (with the t in style) in the PROC REPORT step. So I wouldn't expect your FORMAT step is working at all.

In general, I prefer to make a character string to hold my whole style override for the CALL DEFINE statement. To me, it makes it easier to avoid having the incorrect syntax or quoting issues, when/if I need to specify a style override that needs quotes to do something like this:

compute dummy;

length svar $100;

svar=catt('style={background=',put (var2, color.),' font_style=',put(var3, fntstyle.),'}' );

call define ('var1', 'style', svar);

endcomp;

It gives me a chance to test the string I want to build separately from the CALL DEFINE and makes it easier (in my opinion) for me to add more style changes in the creation of SVAR.

cynthia

Re: ExcelXP -- Warning: In Event: style_over_ride, Invalid expression

cooool — Fri, 10 Jul 2015 15:04:10 GMT

Thanks for your reply. Those were transcription errors. In my real program, format is working. I tried creating svar and I still got the same error. I suspect it is related to version of ExcelXP and version of SAS (9.3).

the error came from ExcelXP -

define event style_over_ride ; ......

if ^$style_overrides [$key];

set $cell_class lowcase(htmlclass);

do / if contains ($cell_class,"ata") | contains ($cell_class, "eader"); /*Header*/

set $cell_class $cell_class "___" just /if ^$style_with_just[$cell_class];

done;

do / if $style_list[$cell_class];

set $cell_class $cell_class "__";

eval $style_list[$cell_class] $style_list[$cell_class] + 1; <-- error came from here

else;

eval $style_list[$cell_class] 1;

done

Re: ExcelXP -- Warning: In Event: style_over_ride, Invalid expression

Cynthia_sas — Sat, 11 Jul 2015 14:53:32 GMT

Hi:

I do not have SAS 9.3 to test with anymore. But when I run this code with SAS 9.4 and open the file with Excel 2013, the file opens without errors. The version of TAGSETS.EXCELXP that I use is this:

19677 ODS tagsets.excelXP file='c:\temp\test_traf.xml' style=sasweb;

NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\test_traf.xml

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.129, 11/07/2011). Add options(doc='help') to the ods statement for more information.

If you are running v1.129 (which you should see in your Log, then I do not understand what is happening. If you are NOT running v1.129, then you can update it from here: Base SAS: ODS MARKUP (scroll down to the middle of the page to see all the updates that are available for TAGSETS.EXCELXP. This Tech Support note explains how to use ODS PATH to direct your update to a template store: 32394 - Installing and Storing Updated Tagsets for ODS MARKUP

Otherwise, I do not recommend changing the ExcelXP template to alter the event processing, except under the direction of Tech Support. These errors from Excel are rarely the fault of the template. Usually something in your code generates invalid XML. Tagsets.ExcelXP is Spreadsheet Markup Language XML and the style information is written in a very particular way to make Excel happy. Unfortunately, you can do things to make Excel unhappy.

Since the code works in 9.4, you may only need to update to one of the more current ExcelXP versions on the web. Otherwise, for someone to test your code in 9.3, I recommend that you work with Tech Support. I'd suggest trying my code first. Code like this has worked for me in 9.2, 9.3 and 9.4 because we have used an example like this in our classes for quite a while.

If this code does NOT work for you, and you are running at least v1.129 of the ExcelXP template, then open a track with Tech Support.

cynthia

** the code;

proc format ;
value color 0='lightgreen' 1='lightyellow' 2='lightred';
value fntstyle 0='roman' 1='italic';
run;

data fakedata;
infile datalines dlm=',' dsd;
input Var1 $ Var2 var3;
return;
datalines;
grade 0,          0,          0
grade 2,          1,          0
grade 4,          2,          1
;
run;

ODS tagsets.excelXP file='c:\temp\test_traf.xml' style=sasweb;

proc report data=fakedata nowindows;
column var1 var2 var3 dummy;
define var1/display;
define var2/display ;
define var3/display ;
define dummy/computed ;
compute dummy;
length svar $100;
svar=catt('style={background=',put (var2, color.),' font_style=',put(var3, fntstyle.),'}' );
call define ('var1', 'style', svar);
endcomp;
run;

ods _all_ close;

Re: ExcelXP -- Warning: In Event: style_over_ride, Invalid expression

cooool — Tue, 14 Jul 2015 15:01:57 GMT

Thank you. I have contacted Tech support. She said the problem was my tagset file is old. Once I use the shipped version of the tagset, the program runs without errors.

One can also add "ODS path sashelp.tmplmst(read); " to direct the program to use the latest tagset.