Hi All,
I'm trying to output a dataset to Excel (using SAS 9.3), and I need to add a title in what would be cell A1.
I think that tagsets.excelxp is the way to go. When I run my syntax and then try to open the Excel file, Excel says it's in a different format than specified by the file extension, and then that it can't read the file (attached image of errors).
Any thoughts? Here's my syntax:
ods tagsets.excelxp
file = 'W:\List for 052017_for distribution.xls'
style = minimal
options (Sheet_Name = 'List_052017'
Embedded_Titles = 'yes');
title1 'List for May 2017 - updated 06/01/2017';
ods _all_ close;
ods tagsets.excelxp
file = 'W:\list for distribution.xls'
style = minimal
options (Sheet_Name = 'list'
Embedded_Titles = 'yes');
title1 'List for June 2017 - updated 06/01/2017';
ods _all_ close;
And here's my output:
2431 ods tagsets.excelxp
2432
2433 file = 'W:\list for distribution.xls'
2434
2435 style = minimal
2436
2437 options (Sheet_Name = 'list'
2438 Embedded_Titles = 'yes');
NOTE: Writing TAGSETS.EXCELXP Body file: W:\Monthly Reports\LIC_028 All Active Providers (also
Provider Lists)\LIC_028_All_Active_Providers_use for 052017_for distribution.xls
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011). Add
options(doc='help') to the ods statement for more information.
2439
2440 title1 'List for June 2017 - updated 06/01/2017';
2441
2442 ods _all_ close;
Well, topic 1, Excel states the file is in a different format than specified, because you have given the filename an XLSX extension. XLSX tells Excel to expect a file which is in its proprietary Open Office file format (a ZIP with XML files inside). What you create from tagsets.excelxp is XML. Now this XML can be read and interpreted by Excel, but its not what it expects from XLSX. Naming file extension per what they are for is good practice, so name your output file <filename>.xml.
Second topic, you get issues with the output file as you have not created any output. Nowhere in the text you have given is an output procedure such as proc print, proc report, proc tabulate etc. What you are after is:
ods tagsets.excelxp file='W:\List for 052017_for distribution.xml' style=minimal options (sheet_name='List_052017' embedded_titles='yes');
title1 'List for May 2017 - updated 06/01/2017'; proc print data=sashelp.class; run; ods tagstes.excelxp close;
Just using a SAS default dataset for example here.
Well, topic 1, Excel states the file is in a different format than specified, because you have given the filename an XLSX extension. XLSX tells Excel to expect a file which is in its proprietary Open Office file format (a ZIP with XML files inside). What you create from tagsets.excelxp is XML. Now this XML can be read and interpreted by Excel, but its not what it expects from XLSX. Naming file extension per what they are for is good practice, so name your output file <filename>.xml.
Second topic, you get issues with the output file as you have not created any output. Nowhere in the text you have given is an output procedure such as proc print, proc report, proc tabulate etc. What you are after is:
ods tagsets.excelxp file='W:\List for 052017_for distribution.xml' style=minimal options (sheet_name='List_052017' embedded_titles='yes');
title1 'List for May 2017 - updated 06/01/2017'; proc print data=sashelp.class; run; ods tagstes.excelxp close;
Just using a SAS default dataset for example here.
Thanks so much, RW9!
I was basing my syntax off of an example on the SAS website (https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html - below), so it's good to know the extension piece!
Embedded Titles and Footnotes
Use the EMBEDDED_TITLES= and EMBEDDED_FOOTNOTES= options.
ods listing close;
ods tagsets.excelxp file="embed.xls" style=statistical
options( embedded_titles='yes' embedded_footnotes='yes' );
title "My First Title";
title3 "My Third Title";
proc print data=sashelp.class (obs=5); run;
footnote "My First Footnote";
footnote3 "My Third Footnote";
ods tagsets.excelxp close;
ods listing;
And I can't believe I forgot to add in the proc print. Thanks!
Added question - it now adds two rows. Row 1 contains the title and row 2 is blank. How can I get it to only add row 1? This list goes to a group of folks who need it to be formatted precisely and consistently.
Thanks!
TAGSETS.EXCELXP creates Excel readable XML. The "error" is naming XML output with the XLS extension. Excel is getting more picky with each release about the content of the data matching the extension.
Change the extension to XML and excel should read it fine.
Thanks! The SAS website steered me wrong.
Any thoughts as to how to get it to only add the title row (row 1) and not the blank row beneath it (row 2)?
Thanks!
I suspect your blank row 2 is because you use Title1 and Title3 with no defined Title2. All of the destinations dating back to the dark ages of SAS 5 will insert a blank row when a situation like this is encountered.
If you want to get rid of the space between the title and the tables that is an entire other problem.
Thanks! Unfortunately, it's the entire other problem. I only had a title1. Do you know how to get rid of that row between title1 and the data?
@kjowers wrote:
Thanks! Unfortunately, it's the entire other problem. I only had a title1. Do you know how to get rid of that row between title1 and the data?
That apparently is buried in some esoteric bit of the tagsets. You aren't the first to ask, I did years ago about RTF where the PARSKIP option could be modified but not in the other ODS destinations.
With some procedures that support style=[pretext='text to show before the output'] sometimes this doesn't introduce that vertical space but you do not have the same display control as Title, text appears in a different style than Title statements and you can't build table of contents entries.
Option for removing the blank row between the title and the data in Excel:
* Use the ODS TEXT= statement instead of the TITLE statement.
awesome! thanks for the tip!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.