topic How to add a footnote with asterisk to an excel table using ODS in SAS Programming

How to add a footnote with asterisk to an excel table using ODS

sas_student1 — Tue, 27 Apr 2021 15:59:23 GMT

Hello,

I am running a proc report that I am exporting to excel.

I want to be able to put a footnote at the end of the excel sheet on a condition in the table. That is, if a clinic has a certain characteristic e.g. if a clinic has an ID that has an "A", then put an asterisk (*) next to the cell and put a footnote with the asterisk at the end of the excel table.

Below is the SAS code that I have written out which works with what I need, except figuring out the footnote.

Any ideas on what I would need to add to the code below?

ods excel file ='C:\Excel_table.xlsx';
ods escapechar ='^';
ODS excel options(Embedded_Titles ='yes' sheet_name='New Cases');

title1 j=l h=11pt color=black font=Calibri bold 
"^{style[fontweight=bold]
Summary of ^{style[fontstyle=italic] New} Cases by clinic}";

title2 j=l h=11pt color=black font=Calibri  
"^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}";

 proc report data=have 
 style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold]
 style(column)={font_face='Calibri' fontsize=11pt};
  column clinic new_patients new_cases;
  define clinic / group;
  define new_patients / sum;
  define new_cases / sum;

  /*break after Program_Name / summarize style={background=lightgrey};*/

  rbreak after / summarize style=Header;

  Compute after;
  clinic ='GRAND TOTAL';
  endcomp;
 run;

Thank you in advance!

Re: How to add a footnote with asterisk to an excel table using ODS

ChrisNZ — Wed, 28 Apr 2021 00:26:45 GMT

I don't see where you add your asterisk, but this should do what you want.

Run something like this before the proc report:

data _null_;

set HAVE;

if index(ID,'A') then do;

call execute('footnote "* This cell contains an ''A'' ";');

stop;

end;

run;

Re: How to add a footnote with asterisk to an excel table using ODS

sas_student1 — Wed, 28 Apr 2021 02:15:29 GMT

@ChrisNZ Thank you!

This is exactly what I needed!

I tweaked the code so it would left justify and change the font. A follow-up how do I change the font size of the footnote? I tried fontsize=11pt or font=11pt but that didnt work.

data _null_;
set have ;
if index(client,'*') then do;
call execute 
('footnote justify=left font=Calibri color=black bold  "* Multiple clients are reported here";');
stop;
end;
run;

Re: How to add a footnote with asterisk to an excel table using ODS

ChrisNZ — Wed, 28 Apr 2021 02:20:56 GMT

Have you tried option h= ?

Re: How to add a footnote with asterisk to an excel table using ODS

sas_student1 — Wed, 28 Apr 2021 02:54:13 GMT

@ChrisNZ

That worked!!! Whoot whoot! You rock!!

Thank you!