topic Re: XML export problem in ODS and Base Reporting

XML export problem

SB_145 — Thu, 03 Sep 2020 13:04:34 GMT

Hi all,

I have a piece of code that I've been using forever, which exports data to XML files with multiple tabs. Recently however, the code exports only the first line. Has SAS made any changes recently that affect the ODS outputting? Any suggestions would be appreciated.

Thank you.

ods listing close;
options nocenter;
ods tagsets.excelxp options (width_fudge='0.7' embedded_titles='yes')
file="mypath/myfilename.xml"
style=htmlblue ;
title;
ods noproctitle noresults;

%macro tabs (sheetname, dataname, keepstatement, headeropt);

ods tagsets.excelxp options (sheet_name = "&sheetname.");
proc report data = &dataname. &keepstatement. nowd ;
run;

%mend tabs;

%tabs (TabName1, dataset1,,);
%tabs (TabName2, dataset2,,);
%tabs (TabName3, dataset3,,);

Re: XML export problem

Tom — Thu, 03 Sep 2020 13:15:29 GMT

If by the first line you mean the header row in a spreadsheet that contain the variable names then perhaps the issue is that your data now has zero observations.

Also check that you don't have some earlier error in your program that has set the OBS option to 0 . You should try to find and fix that error first. But you can reset the option using an option statement.

options obs=max;

Re: XML export problem

SB_145 — Thu, 03 Sep 2020 13:22:17 GMT

Thank you for your suggestions, Tom. To be clear, the code exports the header and the first observation. It has the same behavior for all datasets that I've tried, regardless how many observations they have.

I tried executing the option statement to no effect: options obs=max;
Thank you again!

Re: XML export problem

Tom — Thu, 03 Sep 2020 14:02:38 GMT

What version of SAS are you running? What version of the excelxp tagset are you using?

Did you try just making a simple file? For example try writing SASHELP.CLASS and see if it works.

Why not switch to generating actual XSLX file instead by using ODS EXCEL instead?

Re: XML export problem

SB_145 — Thu, 03 Sep 2020 14:36:39 GMT

1) I'm running SAS Enterprise Guide 7.15 HF3.

2) SAS Excelxp Tagset .v1.131

3) Now, that's interesting: Yes, I can write all observations from SASHELP.CLASS. The reason I say this is interesting is because there is absolutely nothing special about the datasets that I'm trying to export: they are the result of proc transpose, have less than 30 observations, some of which are missing (replacing the missing values with 0s didn't solve the problem).

4) I believed I tried using ODS EXCEL but I wasn't able to create multiple tabs, which was a must-have. Perhaps I should look again into it, in case I missed something.

Thank you.

Re: XML export problem

Tom — Thu, 03 Sep 2020 14:41:45 GMT

The version of the GUI tool you are using to submit your SAS code doesn't matter for this. The version of SAS that is running the code is what is important. SAS has been on version 9.4 for multiple years so what the maintenance level you are at makes a big difference. I think 9.4m7 is the latest release.

You can definitely create multiple sheets in the same workbook with ODS EXCEL. Syntax for sheets is pretty much identical to that used by TAGSETS.EXCELXP.

Re: XML export problem

SB_145 — Thu, 03 Sep 2020 14:45:10 GMT

I'm running 9.4 TS 1M5.
All right, I'll check again exporting the output straight to excel.

Thanks, Tom!

Re: XML export problem

Tom — Thu, 03 Sep 2020 15:03:26 GMT

Let us know how it goes. It sounds like you might have strange characters that are causing SAS to generated weird XML that appears truncated. Since XLSX files are really just zipped XML files it would be good to know if switching ods destinations makes a difference.

Re: XML export problem

SB_145 — Thu, 03 Sep 2020 18:13:38 GMT

As you suggested, Tom, I solved this issue by switching to ODS EXCEL (working code listed below). I believe you may be right and that the problem is caused by hidden special characters (some of my data contains names imported from external sources). I'm sure there is a way to "clean" them automatically, but since exporting the data straight to Excel eliminates this issue, I'm taking the easy way out.

Thank you again!

ods listing close; 
    ods excel options (frozen_headers="3" frozen_rowheaders="2" embedded_titles="yes")
        file="mypath/myfilename.xlsx";

%macro tabs (sheetname, dataname, keepstatement);
    ods excel options (sheet_name="&sheetname.");
    proc print data=&dataname.; 
    title "Top Candidates For &sheetname.";
    run;
%mend tabs;

%tabs (Tab1, Dataset1,);  
%tabs (Tab2, Dataset2,); 
%tabs (Tab3, Dataset3,); 
ods excel close;
ods listing;