XML PARSE ERROR: Missing whitespace between attributes

[chandler]

Need help on tagsets.excelxp

How do I get rid of this error, so I can open my Excel output.

(Using Base SAS v9.3 on WindowsXP platform.)

Sample of my code is in attachment below.

[Cynthia_sas · Accepted solution]

Hi:

  I don't observe an issue with Excel when I produce output with ODS TAGSETS.EXCELXP. However, I no longer have Windows XP to test with. I just have Windows 7 and Excel 2010 using SAS 9.3 and ExcelXP tagset version 1.127 dated 09/26/2011.

  But, the screenshot attached, was produced by the code below. Since you did not post your complete code, I used SASHELP.PRDSALE and made some fake variables (you had over 20 variables on your report), so I made sure that I had more than 20 variables on my report. You didn't show all of your REPORT code, so I just made most of the variables have a usage of DISPLAY. And then, I removed all the "LISTING" only options, such as LS, PS, HEADLINE, HEADSKIP, which are ignored by ODS.

  Also, the HTMLSTYLE attribute that you used in your code was intended only for use with ODS HTML or ODS MSOFFICE2K or ODS MSOFFICE2K_X destinations -- HTMLSTYLE is the way that you alter the Microsoft format used for a number with HTML destinations. But, since TAGSETS.EXCELXP is not HTML, but instead is XML (Spreadsheet Markup Language 2003 XML); with TAGSETS.EXCELXP, you need to use the TAGATTR style attribute (see this paper, http://support.sas.com/resources/papers/proceedings11/266-2011.pdf page 3 to see how to specify a "text" format using TAGATTR with TAGSETS.EXCELXP ). You can see that the attribute got applied because all my numeric columns show the little green triangle, which is Excel's not so subtle message that I am storing a number as text. And I popped up the format box on the YEAR column so you could see that Excel is using the TEXT format for the cell. It is not appropriate to use HTMLSTYLE with TAGSETS.EXCELXP.

  I did not have an issue with the XML that was generated or with opening the file in Excel. Perhaps your version of Excel has issues with the HTMLSTYLE information like "mso-number-format" being passed in -- I'm not sure. That would be another question for Tech Support.  If you run my test code and get the same error, then that would be an indication that you need to work with Tech Support on this issue. If you run my test code and do NOT get the same error with Excel, but you still are not getting the results that you want with your code,  then that would be an indication that something is different about your PROC REPORT code, and again, it would be good to open a track with Tech Support.

  To open a track with Tech Support, fill out the form at this link:

http://support.sas.com/ctx/supportform/createForm

cynthia

%let filedt = 2013APR09;
     
data prdsale;
  set sashelp.prdsale;
  ** original report column statement had over 20 variables,  ;
  ** so make a bunch of fake variables for the report.;
  array fakevar var1-var10;
  do i = 1 to 10;
     if i le 5 then fakevar(i) = i * actual/100;
     else fakevar(i) = i * predict/100;
  end;
run;
    
ods listing close;
ods tagsets.excelxp file="c:\temp\Remote_UW_&filedt..xml" 
    style=statistical ;
PROC REPORT DATA=prdsale  SPLIT="/"  CENTER MISSING  nowd
      STYLE(REPORT) =[FONT_face="Arial" FONT_SIZE=8pt background=black]
      STYLE(HEADER) =[FONT_SIZE=8pt FOREGROUND=BLACK BACKGROUND=WHITE]
      STYLE(COLUMN) =[FONT_SIZE=8pt FOREGROUND=BLACK BACKGROUND=WHITE  tagattr='Format:@'] ;

  
     COLUMN  country region division prodtype product
          quarter year actual predict
          var1 var2 var3 var4 var5 var6 var7 var8 var9 var10;
  
  define country / display;
  define region / display;
  define division / display;
  define prodtype / display;
  define product / display;
  define quarter / display;
  define year / display;
  define actual / display;
  define predict / display;
run;
   
ods tagsets.excelxp close;