topic Re: Conditional footnotes in ods pdf in ODS and Base Reporting

Conditional footnotes in ods pdf

_Manhattan — Fri, 19 Jul 2024 11:20:10 GMT

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

Re: Conditional footnotes in ods pdf

ChrisNZ — Fri, 19 Jul 2024 12:47:59 GMT

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;

Re: Conditional footnotes in ods pdf

_Manhattan — Fri, 19 Jul 2024 12:48:49 GMT

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.

Re: Conditional footnotes in ods pdf

PaigeMiller — Fri, 19 Jul 2024 13:11:13 GMT

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.

Re: Conditional footnotes in ods pdf

_Manhattan — Fri, 19 Jul 2024 13:20:15 GMT

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^^

Re: Conditional footnotes in ods pdf

PaigeMiller — Fri, 19 Jul 2024 13:22:59 GMT

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.

Re: Conditional footnotes in ods pdf

_Manhattan — Fri, 19 Jul 2024 13:27:47 GMT

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.

Re: Conditional footnotes in ods pdf

PaigeMiller — Fri, 19 Jul 2024 13:34:19 GMT

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;

Re: Conditional footnotes in ods pdf

_Manhattan — Fri, 19 Jul 2024 13:40:39 GMT

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

Re: Conditional footnotes in ods pdf

Cynthia_sas — Fri, 19 Jul 2024 19:17:15 GMT

Hi:

This worked for me:

Cynthia

Re: Conditional footnotes in ods pdf

PaigeMiller — Fri, 19 Jul 2024 19:20:06 GMT

@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.

Re: Conditional footnotes in ods pdf

Cynthia_sas — Fri, 19 Jul 2024 21:47:07 GMT

@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

Re: Conditional footnotes in ods pdf

PaigeMiller — Fri, 19 Jul 2024 21:53:06 GMT

@Cynthia_sas I will request documentation be changed.

Re: Conditional footnotes in ods pdf

Quentin — Sat, 20 Jul 2024 02:40:11 GMT

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#p0uv7aycufekfdn1jyaf35cvxeuj

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.

Re: Conditional footnotes in ods pdf

Tom — Sat, 20 Jul 2024 18:11:25 GMT

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:

Re: Conditional footnotes in ods pdf

Cynthia_sas — Sat, 20 Jul 2024 18:29:22 GMT

@Tom: Exactly right! Remember how HTML was supposed to lead to the paperless office? There are not any page breaks on the HTML page to know where a footnote goes. And if someone prints an HTML page, the page breaks are determined by the browser and the printer, after SAS has finished creating the output, so the footnotes from SAS would not be surfaced there.
Cynthia

Re: Conditional footnotes in ods pdf

ChrisNZ — Sun, 21 Jul 2024 08:06:12 GMT

To be fair, MS Word also changes the page layout when you change printer. Only PDF files provide a reliable formatting.

Re: Conditional footnotes in ods pdf

_Manhattan — Mon, 22 Jul 2024 09:24:47 GMT

Hi @Cynthia_sas ,

thank your for your input. Your idea works fine in the code I have posted here, but again not in my "original" one. I have tried to recreate an example to visualize my problem. Even though it is rubbish data the problem should be understandable. Short description of the problems:

1) The Footnote is printed below the according proc report step - not at the end of the page.

2) There is a footnote printed at page 2 that states "footnote: #byval1" which seems to be similar to the html output issue

The screenshot below the code shows the problem aswell.

proc datasets lib=work nolist; delete sample_data input_data mean: VariableInfo freq: ;run ;quit;

%let yourpath=\FootnoteTest.pdf;

ods listing;
/*** Example Data ***/
data work.sample_data;
  input EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;
  datalines;
  10 20 30 40 50  1 2 3 2 3
  15 25 35 45 55  1 3 4 3 2
  5  10 15 20 25  2 2 3 3 2
  20 30 40 50 60  4 3 2 1 1
  25 35 45 55 65  1 1 1 2 3
  ;
run;

data work.input_data;
   length FT $20 Info1 $1000 Info2 $1000;
   input FT $ Info1 $ Info2 $ Sort;
   infile datalines delimiter=",";
   datalines;
   FootnoteONE, Variable,Answer your Item Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,1
   FootnoteTWO, AGHK50A Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,Item A Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,1
   FootnoteTHREE, AGHK50B Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,Item B Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,1
   FootnoteFOUR, AGHK50C,Item C Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,1
   FootnoteFIVE, Variable,Answer your Item Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,2
   FootnoteSIX, BGKO28A,Item A Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,2
   FootnoteSEVEN, BGKO28B,Item B Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,2
   FootnoteEIGHT, Variable,Answer your Item Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   FootnoteNINE, EZGH25A,Item A Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   FootnoteTEN, EZGH25B,Item B Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   FootnoteELEVEN, EZGH25C,Item C Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   FootnoteTWELVE, EZGH25D,Item D Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
   FootnoteTHIRTEEN, EZGH25E,Item E Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space Test Space,3
;

/* Using proc sql for a Sorting Variable which is used to iterate trough the do-loop */
proc sql noprint;
   select distinct Sort into :Sort separated by " "
   from work.input_data;
quit;

%put &Sort; 

%let varlistE = EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E;
%let varlistA = AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;

/* Mean Calculation */
proc means data=work.sample_data noprint;
  var &varlistE;
  output out=work.mean_ezgh;
run;

%macro meandata;
   %do i = 1 %to %sysfunc(countw(&varlistE));
   %let vmean = %scan(&varlistE, &i);
   
   data work.mean&vmean;
      set work.mean_ezgh (keep=&vmean);
   run;
   
   %end;
%mend meandata;
%meandata;

/* Freq calculation */
%macro freqdata;
   %do i = 1 %to %sysfunc(countw(&varlistA));
   %let vfreq = %scan(&varlista, &i);
   
   proc freq data=work.sample_data;
      tables &vfreq/ out=work.freq&vfreq;
   run;
   
   %end;
%mend freqdata;

%freqdata;


/*** Macro Loop ***/

%macro Testloop;
  %do i = 1 %to %sysfunc(countw(&Sort.));
    %let SortID = %scan(&sort, &i);
    %let VariableA = %scan(&varlistA, &i);
    %let VariableE = %scan(&varlistE, &i);
    



/* Compute Variable Information for each Variable */
    data VariableInfo;
      set work.input_data;
      where Sort = &SortID.;
      drop Sort;
    run;
    
   %local Info1Items;
   %let Info1Items=;
   PROC SQL noprint;
      SELECT distinct Info1 INTO :Info1Items SEPARATED BY " "/*new*/
      FROM work.input_data
      WHERE Sort = &SortID.
      AND Info1 not in ('' 'Variable');
   QUIT;


   ods pdf startpage=no; 
   ods layout gridded columns=1; 
   ods region;
/* Report Variable Information for each Variable */
	footnote "Footnote: #byval1";
    proc report data=VariableInfo noheader;
      by FT;	
      column Info1 Info2;
    run;
    
ods layout end;
    
   %MACRO PrinItemStats(Item=,colPosition=);
   ods layout gridded columns=1;
      ods region;
      %if %sysfunc(exist(freq&Item.)) %then %do; 
         /* Print Frequency Table */
         
         proc report data=freq&Item.;
            column &Item. COUNT PERCENT;
            define &Item / display "&Item.";
            define COUNT / display "Absolut";
            define PERCENT / display "Percent";
         run;
      %end;

      %if %sysfunc(exist(mean&Item.)) %then %do; 
         /* Print Mean Table */
         proc report data=mean&Item.;
            column &Item.; 
         run;
      %end;
   ods layout end;
   %MEND PrinItemStats;


   %local j currItem;
   %LET j=1;
   %LET currItem=%SCAN(&Info1Items.,&j.,%STR( ));


   %DO %WHILE(%LENGTH(&currItem.)>0);


      %PrinItemStats(Item=&currItem.);

      %LET j=%EVAL(&j.+1);
      %LET currItem=%SCAN(&Info1Items.,&j.,%STR( ));
   %END;


  %end;


%mend Testloop;

ods _all_ close;
options  papersize=a4 orientation=portrait leftmargin=1.5cm rightmargin=1.5cm topmargin=1.5cm bottommargin=1.5cm nodate nonumber nobyline nobysorted;
ods pdf file="&yourpath.";
%Testloop;
ods pdf close;