XML files using ods xltagsets - unable to open error when opening with excel

[Kayalvizhi]

Hi All,

Facing issue with creating xml reports which ids created using xltagsets.

Logs are clean but report has either of the below error and it occurs 1/30 runs.

1. “Unable to read”
2.”Problem during load

The error which we are getting on reports which we are unable to replicate as the data refreshes earlier than before identifying the error in xml report..

[ballardw]

Please show at least the ODS statement creating your files. Since the tagset I think you meant to use is tagsets.excelxp not "xltagsets" I suspect you may be using the wrong ODS statement.  Better would be to show a log for one of these "reports".

Also what feature of XML do you need that means you aren't using ODS EXCEL if you want to open files in Excel?

[Kayalvizhi]

Ods listing close;
Ods tagsets.excelxp file=“&file.”
Options(embedded_titles=‘yes’ minimize_style=‘yes’ warptext=‘yes’ autofit_height=‘yes’) rs=none style=Xlsansprinter;
Ods noproctitle;
Ods tagsets.excelxp style=xlsansprinter
Options(sheet_name=‘sheet1’ sheet_interval=‘none’ embedded_titles=‘yes’ absolute_column_width=“10,10”);
Title “sheet1”;
Proc print data=sashelp.cars(obs=5);
Var make origin;run;
Title;
Ods tagsets.excelxp close;

[Tom]

Check the SAS log and make sure the PROC PRINT actually printed something.

When no output is written between the opening and closing the ODS destination the resulting file is left in an invalid state.  Instead of EXCEL saying the file is empty it gives a strange error message.

[Kayalvizhi]

Yes logs are clean without error and warning . But xml reports get corrupted sometimes. Actual data which was used different, in real-time.

[Tom]

Not writing any output would not cause an error in the SAS log.

Are you sure the steps you ran produced output?

Depending on what steps you ran you might have also used NOPRINT option.

The other possibility is the content is somehow confusing the XML.  What type of text is the report generating?   Perhaps you can use a binary search to isolate some problem text in the data.  What happens if you ask it to produce only half of the output? Does it then work?  If so try producing the other half and see that causes trouble.  Repeat until problem data is isolated.

[Sajid01]

Hello @Kayalvizhi 
Ods tagsets.excelxp creates xml files and these are typically saved as .xls.
When Microsoft Excel reads these files, it gives warnings.
To avoid this you have two options

1) use the xls2xlxs.vbs script  (43496 - Convert files created using an ODS destination to native Microsoft Excel files)

2) A better option is to use ods Excel rather than tagset excelxp.