ExcelXP tagset, PROC TABULATE, footnote placement

[UCFAngel]

I am using the latest ExcelXP tagset with PROC TABULATE to general a multi-sheet workbook. The TABLE statement contains a page dimension in addition to the column and row dimensions. I have each page appearing on the same worksheet (sheet_interval='none') and each sheet_name is generated dynamically using an array and macro variables in a do loop.

The report is created exactly as I want except that the footnote is appearing after the first value of the page dimension on each worksheet and not at the end after all eight tables. Is there an option that I am missing?

[Cynthia_sas]

Hi:
As I remember, there is an embedded_titles suboption, embedded_footers suboption, as well as Print_Header and Print_Footer suboptions (I may not have the spelling of those exactly correct, but you can find them in the SAS log) To get the list of all the suboptions in the TAGSETS.EXCELXP version that you are using, submit this code:
[pre]
ods tagsets.excelxp options(doc='Help');
ods tagsets.excelxp close;
[/pre]

and this paper talks about how to easily create printable spreadsheets by using these and other suboptions:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia

[UCFAngel]

Thank you for your quick reply! I am using embedded_footnotes='yes' as I do want the footnote on the spreadsheet (user will not be printing the output). I don't understand why it shows up after the first page instead of after all eight tables are rendered and PROC TABULATE finishes.

The reference you have supplied is a very valuable resource - thanks! I've searched all over SAS support to see if someone else has reported this same issue but I cannot find anything.

The page dimension variable has eight values. The output in excel ends up as follows:

Title
Table for value 1
Footnote
Table for value2
. . .
Table for value8

[Cynthia_sas]

Hi:
This may be an issue for Tech Support. When I try something similar in SAS 9.2, I get the title and footnote around each table on the sheet... sort of like this:

title
table for value1
footnote

title
table for value 2
footnote

title
table for value 3
footnote


So, I don't know whether the behavior you're noticing is a SAS 9.1.3 vs SAS 9.2 difference or a tagset version difference. That would be a question for Tech Support.

One possible workaround would be to make a fake footnote...something like the code snippet below.

cynthia
[pre]
ods tagsets.excelxp file='xyz.xls' style=sasweb
options(sheet_interval='none' sheet_name='XYZ' embedded_titles='yes'
embedded_footnotes='yes');

/* whatever code you have now, but then at the end of the very last output
where you want the fake footnote */

title; footnote;
data ff;
length fake_footnote $100;
fake_footnote = 'This is a Fake Footnote';
run;

proc report data=ff nowd noheader;
column fake_footnote;
define fake_footnote/ display style=SystemFooter;
run;


ods tagsets.excelxp close;
[/pre]

[UCFAngel]

This is a great suggestion you have - thank you. My footnote needs to contain two lines and I would like the second line to contain the system date. I tried using a data step with datalines to create the fake_footer variable with two values but I get the following error in EG and the data set is empty:

ERROR: The macro PEGMODEL generated CARDS (data lines) for the DATA step, which could cause incorrect results. The DATA step and the macro will stop executing.

Here is the code I am using:
[pre]
data _null_;
call symput("today",left(put("&sysdate"d, worddate.)));
run;
data ff;
infile datalines dsd;
input fake_footnote $35;
datalines;
"Source: Student Instruction File"
"Report Generated: &today"
;
run;
[/pre]

[Cynthia_sas]

Hi:
You cannot use CARDS or DATALINES within a macro program...it's just one of the rules of macro processing.

However, there's nothing to prevent you from using the %SYSFUNC macro in a text string directly. Or from creating a DATA step like this:
[pre]
data ff;
length fake_footnote $38;
fake_footnote = "Source: Student Instruction File";
output;
fake_footnote = "Report Generated: %sysfunc(date(),worddate.)";
output;
run;
[/pre]

cynthia

[UCFAngel]

Thank you so much - this works great! I haven't used a datalines statement in about 8 years and never in a macro so I didn't know that rule.

Thanks so much - you've saved my sanity!