BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kjowers
Fluorite | Level 6

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;

 


errors.png
Doctor J
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

kjowers
Fluorite | Level 6

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!

Doctor J
ballardw
Super User

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.

kjowers
Fluorite | Level 6

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!

Doctor J
ballardw
Super User

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.

kjowers
Fluorite | Level 6

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?

Doctor J
ballardw
Super User

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

bnawrocki
Quartz | Level 8

Option for removing the blank row between the title and the data in Excel:

* Use the ODS TEXT= statement instead of the TITLE statement.

 

kjowers
Fluorite | Level 6

awesome! thanks for the tip!!

Doctor J

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 6585 views
  • 1 like
  • 4 in conversation