topic Re: XML files using ods xltagsets - unable to open error when opening with excel in SAS Programming

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

Kayalvizhi — Sun, 27 Oct 2024 04:34:44 GMT

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..

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

ballardw — Sun, 27 Oct 2024 09:16:25 GMT

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?

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

Kayalvizhi — Sun, 27 Oct 2024 16:57:14 GMT

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;

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

Tom — Sun, 27 Oct 2024 17:23:50 GMT

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.

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

Kayalvizhi — Sun, 27 Oct 2024 17:43:45 GMT

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

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

Tom — Sun, 27 Oct 2024 19:20:27 GMT

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.

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

Sajid01 — Sun, 27 Oct 2024 23:52:24 GMT

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.