ODS Excel: Footnote in one table out of two tables in a sheet

[Barkat]

I would like to print 6 tables in 4 sheets in ODS Excel. Sheet1 has table-1 and table-2, sheet2 has table-3 and table-4, sheet3 has table-5, and sheet4 has table-6. I would like to keep footnote at the end of table-2 on sheet1, and table-4 of sheet2. Also end of the table-5 and table-6 in sheet3 and sheet4, respectively, Table-1 in sheet1 and table-3 in sheet2 will not have footnote. 

I used the following code, but getting footnotes at the end of all tables in all sheets.

ods excel file="\\accounts\...............xlsx" ;
footnote1 justify=l "aaaaaaaaaaaaaaa";
footnote2 justify=l "Data as of %sysfunc(day("&sysdate"d)) %sysfunc(substr(&SYSDATE9,3,3)) %sysfunc(year("&sysdate"d))";
footnote3 justify=l "bbbbbbbbbbbbbbbbb";
ods excel options( embedded_titles='yes' sheet_name = "sheet1"  EMBEDDED_FOOTNOTES = 'NO'   sheet_interval = 'none');
proc report data=fboth_re;
title j=left 'Table 1. # --------';
column institution value ;
define institution /group;
define value /"" ;
run;

ods excel options( embedded_titles='yes' EMBEDDED_FOOTNOTES = 'YES' sheet_interval = 'none');
proc report data=fall_re;
title j=left 'Table 2. # --------------';
column institution value ;
define institution /group;
define value /"" ;
run;

ods excel options( sheet_name = "sheet2"  EMBEDDED_FOOTNOTES = 'NO' sheet_interval = 'proc');
proc report data=fboth_prev;
title j=left 'Table 3. # -----------------';
column institution value ;
define institution /group;
define value /"" ;
run;

ods excel options( embedded_titles='yes' EMBEDDED_FOOTNOTES = 'YES' sheet_interval = 'none');
proc report data=fall_prev;
title j=left 'Table 4. # ------------------';
column institution value ;
define institution /group;
define value /"" ;
run;


ods excel options( sheet_name = "sheet3" embedded_titles='yes' EMBEDDED_FOOTNOTES = 'YES'  sheet_interval = 'proc');
proc report data=R_recent;
title j=left 'Table 5. # ------------------';
column institution value2 n;
define institution /group;
define value2 /"" ;
define n / "R=====";
run;


ods excel options( sheet_name = "shet4" embedded_titles='yes' EMBEDDED_FOOTNOTES = 'YES' sheet_interval = 'proc');
proc report data=RSV_prev;
title j=left 'Table 6. # ------------------';
column institution value2 n;
define institution /group;
define value2 / "" ;
define n / "RRR";
define TotalCase / computed "Total Cases";
run;

title;
footnote;
ods excel close;

[svh]

The EMBED_FOOTNOTES_ONCE='on' option is likely needed here, but I'm not sure exactly which ODS statements to use in. In general, this option forces the footnote to only be shown once when two (or more) tables are printed to an Excel worksheet.

[Barkat]

I tried, but did not work

[Cynthia_sas · Accepted solution]

Hi:

  I approached this task by just using the Footnote statement in the step where you wanted the footnote to appear and then make sure that the step where you did NOT want the footnote had a blank footnote. I got the results you described, as shown below:

I used SASHELP datasets for each sheet. As you can see, I believe the footnotes are where you described them. The tabs are named appropriately, although I did not show the tabs in all the screen shots, they are one_two for Table 1 and Table 2; three_four for Table 3 and Table 4; table_five for 5 and table_six for 6.

  Here's the code  I used:

ods excel file='c:\temp\testfoot.xlsx'
    options(embedded_titles='yes' embedded_footnotes='yes');

ods excel options (sheet_name='one_two' sheet_interval = 'none');
proc report data=sashelp.class (obs=3);
where age = 14;
title j=left 'Table 1. # --------';
footnote;
run;

ods excel options(EMBEDDED_FOOTNOTES = 'YES' embedded_titles='yes');
proc report data=sashelp.class;
where age = 12;
title j=left 'Table 2. # --------------';
footnote1 justify=l "aaaaaaaaaaaaaaa";
footnote2 justify=l "Data as of %sysfunc(day("&sysdate"d)) %sysfunc(substr(&SYSDATE9,3,3)) %sysfunc(year("&sysdate"d))";
footnote3 justify=l "bbbbbbbbbbbbbbbbb";
run;

ods excel options (sheet_name='three_four' sheet_interval = 'now' embedded_titles='yes');
proc report data=sashelp.shoes (obs=5);
where product='Boot';
title j=left 'Table 3. # --------';
footnote;
run;

ods excel options(EMBEDDED_FOOTNOTES = 'YES' embedded_titles='yes' sheet_interval='none');
proc report data=sashelp.shoes(obs=5);
where product='Slipper';
title j=left 'Table 4. # --------------';
footnote1 justify=l "aaaaaaaaaaaaaaa";
footnote2 justify=l "Data as of %sysfunc(day("&sysdate"d)) %sysfunc(substr(&SYSDATE9,3,3)) %sysfunc(year("&sysdate"d))";
footnote3 justify=l "bbbbbbbbbbbbbbbbb";
run;
 
ods excel options (sheet_interval='now' sheet_name='table_five' embedded_titles='yes' embedded_footnotes='yes');
proc report data=sashelp.classfit (obs=3);
where age = 11;
title j=left 'Table 5. # --------';
footnote1 justify=l "aaaaaaaaaaaaaaa";
footnote2 justify=l "Data as of %sysfunc(day("&sysdate"d)) %sysfunc(substr(&SYSDATE9,3,3)) %sysfunc(year("&sysdate"d))";
footnote3 justify=l "bbbbbbbbbbbbbbbbb";
run;

ods excel options(sheet_interval='now' sheet_name='table_six' EMBEDDED_FOOTNOTES = 'YES' embedded_titles='yes');
proc report data=sashelp.classfit(obs=5);
where age = 12;
title j=left 'Table 6. # --------------';
footnote1 justify=l "aaaaaaaaaaaaaaa";
footnote2 justify=l "Data as of %sysfunc(day("&sysdate"d)) %sysfunc(substr(&SYSDATE9,3,3)) %sysfunc(year("&sysdate"d))";
footnote3 justify=l "bbbbbbbbbbbbbbbbb";
run;

ods excel close;

  I like this approach because if I wanted to have a table-specific footnote, then I could use the text I wanted in the appropriate FOOTNOTE statement.

Cynthia