topic Re: Help with ODS ExcelXP errors using STYLE=minimal in ODS and Base Reporting

Help with ODS ExcelXP errors using STYLE=minimal

SASMike — Tue, 14 Jan 2014 18:41:47 GMT

Greetings!

I have been acclimating myself to the ODS ExcelXP facility lately and I've run into an issue generating files. The issue only occurs when using the "minimal" style oddly. The SAS log does not generate any errors but when attempting to open the spreadsheet (via Excel 2010) I run into the following error. Other styles work perfectly and do not generate errors - just minimal. I've verified that the "minimal" style works perfectly with other outputs (HTML, PDF). I've even opened the style CSS itself and confirmed it's existence. It does not matter if I use the extention .xls or .xml...same error. Any help would be much appreciated!

Thank you!!!

SAS Mike

Excel error upon attempt to open:

Here's the Excel log file:

XML ERROR in Table

REASON: Bad Value

FILE: C:\Users\kj1062\Desktop\myexcel.xls

GROUP: Row

TAG: Cell

ATTRIB: StyleID

VALUE: notecontent__c

XML ERROR in Table

REASON: Bad Value

FILE: C:\Users\kj1062\Desktop\myexcel.xls

GROUP: Row

TAG: Cell

ATTRIB: StyleID

VALUE: notecontent__c

XML ERROR in Table

REASON: Bad Value

FILE: C:\Users\kj1062\Desktop\myexcel.xls

GROUP: Row

TAG: Cell

ATTRIB: StyleID

VALUE: notecontent__c

XML ERROR in Table

REASON: Bad Value

FILE: C:\Users\kj1062\Desktop\myexcel.xls

GROUP: Row

TAG: Cell

ATTRIB: StyleID

VALUE: notecontent__c

Here's my SAS code:

ODS TAGSETS.ExcelXP

FILE="C:\Users\kj1062\Desktop\myexcel.xls"

STYLE=minimal

OPTIONS ( EMBEDDED_TITLES='Yes'

SHEET_NAME='PROC PRINT'

FROZEN_HEADERS='3');

TITLE BOLD COLOR=BLACK "ODS Excel Test - PROC PRINT";

PROC PRINT DATA=SASHELP.CARS OBS='Observation #' SPLIT='*'; *SPLIT implies LABEL;

VAR Make / STYLE=[JUST=RIGHT TAGATTR='format:text'];

VAR MSRP;

WHERE Make='Audi';

RUN;

ODS TAGSETS.ExcelXP

OPTIONS ( SHEET_NAME='PROC REPORT');

TITLE BOLD COLOR=RED "ODS Excel Test - PROC REPORT";

PROC REPORT DATA=SASHELP.CARS (WHERE=(Make='Porsche')) MISSING NOWD

STYLE(HEADER)=[FONT_WEIGHT=BOLD JUST=CENTER BORDERWIDTH=1]

STYLE(COLUMN)=[JUST=CENTER BORDERWIDTH=1];

COLUMN Make MSRP;

DEFINE MSRP / F=DOLLAR20.;

COMPUTE AFTER /* STYLE=[] */;

LINE "";

LINE "1. Footnote one";

LINE "2. Footnote two";

LINE "https://www.google.com";

ENDCOMP;

RUN;

ODS TAGSETS.ExcelXP CLOSE;

Re: Help with ODS ExcelXP errors using STYLE=minimal

ballardw — Tue, 14 Jan 2014 21:49:17 GMT

The LINE statements in the Compute After block if your set up is similar mine, SAS 9.2.3,

reference a style ID that was not defined the XML style definitions.

StyleID="notecontent__c"

At least when I manually edit the XML with another defined style replacing that the result displays in Excel.

Re: Help with ODS ExcelXP errors using STYLE=minimal

SASMike — Tue, 14 Jan 2014 22:00:25 GMT

I'm not sure why :smileyconfused: but when using the 'minimal' style, there are issues when using a COMPUTE block. The errors that are generated relate directly to those COMPUTE lines. Unless someone has a solution, I'll have to build a workaround.

Appreciate your response !

COMPUTE AFTER /* STYLE=[] */;

LINE "";

LINE "1. Footnote one";

LINE "2. Footnote two";

LINE "https://www.google.com";

ENDCOMP;

The four COMPUTE lines each generate an error in the Excel log file.

Re: Help with ODS ExcelXP errors using STYLE=minimal

Cynthia_sas — Wed, 15 Jan 2014 04:07:47 GMT

Hi:

Did you search support.sas.com? This Tech Support note (39769 - An incorrect file might be generated when using version 1.86 and below of the ExcelXP tagset along with the Minimal style) seems to indicate that there is some kind of known problem, which, if encountered, should lead you to contact Tech Support. BTW, in SAS 9.4, there is still a problem with STYLE=MINIMAL and TAGSETS.EXCELXP, but the code ran fine using STYLE=SASWEB.

cynthia

Re: Help with ODS ExcelXP errors using STYLE=minimal

SASMike — Wed, 15 Jan 2014 14:43:54 GMT

That explains a lot. I will have to incorporate another style to circumvent this issue.

Thank you very much !!!

Re: Help with ODS ExcelXP errors using STYLE=minimal

SASMike — Wed, 15 Jan 2014 16:58:22 GMT

Solution Follow-Up:

I was able to find a solution to this problem by installing the newest version of the ExcelXP tagset from this link: Base SAS: ODS MARKUP

There is a help note about installation here: 32394 - Installing and Storing Updated Tagsets for ODS MARKUP

This solved the issues I was having with the 'minimal' style. I cannot guarantee this will solve the issue for everyone, however, it did work for me.

Thanks all!