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?
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:
ods tagsets.excelxp options(doc='Help');
ods tagsets.excelxp close;
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:
Table for value 1
Table for value2
. . .
Table for value8
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:
call symput("today",left(put("&sysdate"d, worddate.)));
infile datalines dsd;
input fake_footnote $35;
"Source: Student Instruction File"
"Report Generated: &today"
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:
length fake_footnote $38;
fake_footnote = "Source: Student Instruction File";
fake_footnote = "Report Generated: %sysfunc(date(),worddate.)";