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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.