topic Re: ODS tagsets.excelxp footnote not appearing in Excel file for each table / procedure in ODS and Base Reporting

ODS tagsets.excelxp footnote not appearing in Excel file for each table / procedure

honk — Tue, 12 Jan 2016 21:26:23 GMT

Hi All,

I would like footnotes to appear after each procedure that is run and output to excel. Currently, I run my ods tagsets.excelxp and even if I have footnotes for each procedure, it only appears after the last procedure in the excel file.

This is an example of my code:

%macro rt (var= );

title "&var";
proc tabulate data=dsn;
var count rate;
class year &var ;
table &var , year*(Count Rate);
footnote "This is a footnote";
run;
footnote;
title;

%mend rt;

ods tagsets.excelxp path="c:\filepath" 
file="excelfilename.xls"
style = statistical 
options (embedded_titles = 'yes' 
		embedded_footnotes = 'yes' 
		sheet_name="sheetname"
		sheet_interval = 'none'
		merge_titles_footnotes="yes");

%rt(var= Sex)
%rt(var= Race)
%rt(var= Age)

ods tagsets.excelxp close;

The attached excel file is how it turns out. I'd like the footnote after each table.

Re: ODS tagsets.excelxp footnote not appearing in Excel file for each table / procedure

Reeza — Tue, 12 Jan 2016 22:07:26 GMT

Verify that you're using the latest tagsets. Using the latest version I don't see the issue you're having. You can find a NOTE in your log indicating the version, it should match the information below.

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.131, 04/23/2015).
Add options(doc='help') to the ods statement for more information.

You can download the latest version here:

http://support.sas.com/rnd/base/ods/odsmarkup/index.html

Re: ODS tagsets.excelxp footnote not appearing in Excel file for each table / procedure

Ksharp — Wed, 13 Jan 2016 01:05:15 GMT

Try delete :

footnote;
title;

Or switch to use :

ods text="This is a footnote";

Re: ODS tagsets.excelxp footnote not appearing in Excel file for each table / procedure

RW9 — Wed, 13 Jan 2016 09:51:27 GMT

The reason you are not seeing footnotes is because that is the default action of Excel. Excel is not a proper review format even though it appears everyone thinks it is. If you goto the lower right of your Excel fil you will see three icons and a scroll bar. If you select the middle icon - which will say print layout when hovered over - then it will show you the Excel file as a proper print format. The question is, if you want a review document with document features, then why not use a document format - PDF, RTF etc. which are built for that purpose?

One other note, you appear to have re-saved that document as a .xls file. As the program you have given is actually generating XML output (which Excel can interpret) its a good idea not to name the output file xls, but rather xml. You can still associate the extension xml with excel and get the same functionality, but someone taking the file will then know the contents of the file - which is what extensions are for (i.e. right click open with).

Re: ODS tagsets.excelxp footnote not appearing in Excel file for each table / procedure

Reeza — Wed, 13 Jan 2016 13:21:29 GMT

Because embedded_footnotes option is specified they should show up by default, and they did in a test run on my PC.

Re: ODS tagsets.excelxp footnote not appearing in Excel file for each table / procedure

honk — Wed, 13 Jan 2016 16:12:00 GMT

Simple solution. Mine was:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.127, 09/26/2011).

I updated to the most recent version you specified and it functions properly now.

Thank you very much.