Conditional footnotes in ods pdf

[_Manhattan]

Hello,

I would like to know if there is any way to program conditional footnotes in ods pdf. I have tried to implement some kind of if/then logic in my code which contains a more or less complex macro. Beforehand, I have defined the footnote text in excel, imported the excel as a sas dataset and created a macro variable with proc sql. The idea was to iterate over my code with a do loop and always print a footnote if the footnote-text-macro-variable is not empty. Now, what is a bit problematic is that as I was recreating an example with the sashelp data sets my code worked just fine. But in my original code it was always just generating the first footnote on every page. With the use of some "put" statements I have figured out that the macro variable is returning the correct value, but the printing is somehow not working.

LONG STORY SHORT: I am looking for a way to generate footnotes at the end of the page (not at the end of each proc report step) that is different to the one I have used in the code below. I can not show you my original code, but let me assure you that I have tried a lot of stuff to get the footnotes working. I am guessing the macro structure in my original code is messing with the functioning of the approach below. If you have any idea how to generate some kind of conditional footnotes please let me know.

ods escapechar="^";
data cars_with_sort;
    set sashelp.cars (obs=55);
    select (Make);
        when ("Acura") Sort = 1;
        when ("Audi") Sort = 2;
        when ("BMW") Sort = 3;
        when ("Buick") Sort = 4;
        otherwise Sort = .; 
    end;
run;

proc sql noprint;
  select distinct Sort into :Sort separated by " "
  from cars_with_sort;
quit;


%macro Foot;
%do i = 1 %to 4;
%let SortID = %scan(&Sort., &i.);
proc sql noprint;
	 select distinct Make into :FootnoteT separated by " "
	 from cars_with_sort 
	 where Sort = &SortID.;
   quit;
%put Fußnote = &FootnoteT;


 %if %length(&FootnoteT) > 0 %then %do;
       footnote j=l "^S={fontfamily=calibri font_size=10pt} &FootnoteT";
 %end;


data cars;
set cars_with_sort;
where Sort = &SortID.;
run;

ods layout gridded;
proc report data=cars;
	column Make Sort;
	define Make / "Car Model";
	define Sort / noprint;
run;
ods layout end;

%end;
%mend Foot;

options papersize=a4 orientation=portrait nodate leftmargin=1.5cm rightmargin=2.5cm bottommargin=0.5cm topmargin=2cm nonumber;
ods listing close;
ods pdf file = "yourpath\FootnotesTest.pdf";
%Foot;
ods pdf close;

Kind regards

[ChrisNZ]

This works:

ods escapechar="^";
data cars_with_sort;
    set sashelp.cars (obs=55);
    select (Make);
        when ("Acura") Sort = 1;
        when ("Audi") Sort = 2;
        when ("BMW") Sort = 3;
        when ("Buick") Sort = 4;
        otherwise Sort = .; 
    end;
run;

%macro Foot;
  %local FootnoteT;
  %do i = 1 %to 4;
    %let FootnoteT=;
    proc sql noprint;
       select distinct Make into :FootnoteT 
       from cars_with_sort 
       where Sort = &i.;
       quit;
    
    footnote;
    
    ods layout gridded;
    proc report data=cars_with_sort;
      where Sort = &i.;
      column Make Sort;
      define Make / "Car Model";
      define Sort / noprint;
      %if %length(&FootnoteT) > 0 %then %do;
        footnote j=l "^S={fontfamily=calibri color=red font_size=10pt} &FootnoteT";
      %end;
    run;
    ods layout end;
  
  %end;
%mend Foot;

options papersize=a4 orientation=portrait nodate leftmargin=1.5cm rightmargin=2.5cm bottommargin=0.5cm topmargin=2cm nonumber;
ods listing close;
ods pdf file = "%sysfunc(pathname(WORK))\FootnotesTest.pdf";
%Foot;
ods pdf close;

[_Manhattan]

Hey Chris,

thank you for you reply! Unfortunately, with your approach the footnotes are indeed updated, but not placed where they are supposed to be (at the bottom of the page):

Instead, the footnotes are printed below the proc report step which is not needed.

[PaigeMiller]

Just a thought — this would be SOOOOO much easier if you wanted to do this using TITLE statement rather than FOOTNOTE. Then you don't need macros at all, you just need a BY statement.

[_Manhattan]

Hey Paige,

thank your for your reply. Could you elaborate on that thought? In what way would you use a title statement to generate the footnotes? I am not eager to use the footnote statement. It just seemed like the obvious thing to do if one wants to create footnotes^^

[PaigeMiller]

You don't use the TITLE statement to create footnotes. You use it to create titles. But still, all the information is present in the output, at the cost of doing much less programming, that was the point.

[_Manhattan]

Alright, thanks I appreciate it! May I ask if you could modify the code to show how to simplify it? Maybe I could integrate your approach to update my original code.

[PaigeMiller]

options nobyline;
title 'All Models of #byval1';
proc report data=sashelp.cars(where=(make in ("Audi",'Acura','BMW','Buick')));
    by make;
    columns model msrp invoice mpg_city;
run;

[_Manhattan]

I see what you are saying. But sadly it is not working for my initial problem as I need the footnotes. Thank you anyway!

[Cynthia_sas]

Hi:

  This worked for me:

Cynthia

[PaigeMiller]

@Cynthia_sas 

I looked in the documentation for footnotes, and didn't anywhere that indicated #byval1 (and similar) could be used. But I didn't actually try it.

[Cynthia_sas]

@PaigeMiller, I know. I thought that too when I first looked at it and then some faint memory of a past program led me to try it.
Cynthia

[PaigeMiller]

@Cynthia_sas I will request documentation be changed.

[Quentin]

It is mentioned in a section of the docs on ODS GRAPHICS: 
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/grstatproc/n1ukd9sqgqiwwhn1mrx4c1rbse1j.htm#p...

But I agree, it would be good to include this in the documentation for the FOOTNOTE statement, as it is in the docs for the TITLE statement.

[Tom]

Note that #BYVAL() works in footnote for PDF output.  But not that well in HTML output. I assume because HTML output does not really have any type of page concept.

Example:

proc sort data=sashelp.class out=class; by sex; run;
options nobyline;
proc print data=class;
  by sex;
  pageby sex;
  title1 "From: #byval(sex) ";
  footnote1 "To: #byval(sex) ";
run;

Works for PDF results but the HTML results look like this: