topic Re: how to add a title line as the first row before exporting to Excel in SAS Programming

how to add a title line as the first row before exporting to Excel

kjowers — Wed, 07 Jun 2017 15:35:00 GMT

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;

Re: how to add a title line as the first row before exporting to Excel

RW9 — Wed, 07 Jun 2017 15:40:32 GMT

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.

Re: how to add a title line as the first row before exporting to Excel

ballardw — Wed, 07 Jun 2017 15:41:22 GMT

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.

Re: how to add a title line as the first row before exporting to Excel

kjowers — Wed, 07 Jun 2017 15:54:15 GMT

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!

Re: how to add a title line as the first row before exporting to Excel

kjowers — Wed, 07 Jun 2017 15:55:11 GMT

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!

Re: how to add a title line as the first row before exporting to Excel

ballardw — Wed, 07 Jun 2017 16:56:59 GMT

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.

Re: how to add a title line as the first row before exporting to Excel

kjowers — Wed, 07 Jun 2017 17:45:23 GMT

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?

Re: how to add a title line as the first row before exporting to Excel

ballardw — Wed, 07 Jun 2017 19:06:06 GMT

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

Re: how to add a title line as the first row before exporting to Excel

bnawrocki — Mon, 14 Aug 2017 13:32:24 GMT

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

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

Re: how to add a title line as the first row before exporting to Excel

kjowers — Mon, 14 Aug 2017 15:55:38 GMT

awesome! thanks for the tip!!