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,,);
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.
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;
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?
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.
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.
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.