topic Re: footnote behavior in proc sql in SAS Programming

footnote behavior in proc sql

jteres — Tue, 06 Nov 2018 20:27:35 GMT

I'm a little confused about how FOOTNOTES behave in PROC SQL.

I had sort of assumed that FOOTNOTES behaved similar to TITLES. They seem to have similar behavior in terms of persistence, but I can't really make sense of how this code behaves:

proc sql ;
     title "query 1: unique values of sex" ;
     select    distinct
               Sex
     from      sashelp.class ;
     footnote       "query 1: &SQLObs rows" ;
     %put NOTE: There were &SQLObs rows read from sashelp.class. ;

     title "query 2: unique combinations of sex and age" ;
     select    distinct
               Sex,
               Age
     from      sashelp.class ;
     %put NOTE: There were &SQLObs rows read from sashelp.class. ;
     footnote2      "query 2: &SQLObs rows" ;
quit ;

When I run it, i get the following output:

query 1: unique values of sex

Sex
F
M

query 2: unique combinations of sex and age

Sex	Age
F	11
F	12
F	13
F	14
F	15
M	11
M	12
M	13
M	14
M	15
M	16

query 1: 2 rows

Note that the footnote appears only at the end of the PROC (which is consistent with the description of the FOOTNOTE statement in the documentation) but the footnote2 doesn't appear.

I looked around but couldn't see anything that explicitly discusses how footnotes behave in PROC SQL when you are executing multiple queries in a single PROC.

I guess my question is, where should I specify footnotes, and how can I expect them to behave?

Re: footnote behavior in proc sql

FreelanceReinh — Tue, 06 Nov 2018 20:53:38 GMT

@jteres wrote:

I'm a little confused about how FOOTNOTES behave in PROC SQL.

I had sort of assumed that FOOTNOTES behaved similar to TITLES.

Hi @jteres,

I think you're right, but this means that they have to be specified before the SELECT statement that produces the output. In your case this is unfortunate because the desired value of &SQLOBS is available only after the SELECT statement has executed.

You could use the NUMBER option of the PROC SQL statement to obtain row numbers in the output, in particular the number of the last row.

proc sql number;
...

Edit: Your FOOTNOTE2 statement will affect output that is created after that statement, e.g., if you run your code twice.

Re: footnote behavior in proc sql

ChrisNZ — Tue, 06 Nov 2018 21:01:07 GMT

As you pointed out, he documentation is clear:

FOOTNOTE Statement: Writes up to 10 lines of text at the bottom of the procedure or DATA step output.

where should I specify footnotes, and how can I expect them to behave?

The footnote is somewhat part of the query and must be known when the query runs.

Since there is no query after your footnote2, it is ignored.

[Edited: you already had a reply from @FreelanceReinh

Another way to display the observation count is to add something like this at the end of the procedure:

title;
select "&SQLObs" "Nb obs" from sashelp.class(obs=1);

ods text=Nb obs: &sqlobs";

]

Re: footnote behavior in proc sql

mkeintz — Wed, 07 Nov 2018 05:40:27 GMT

As has been pointed out, the footnote text has to be established before the select statement that invokes the footnote, but that is too soon to have &sqlobs resolved.

One solution is to run each query as

a create table dummy as ...,
then the title and footnotes,
then a select * from dummy:

footnote;
title;
dm 'clear out';
proc sql ;
   create table dummy as 
     select    distinct
               Sex
     from      sashelp.class ;
     title "query 1: unique values of sex" ;
     footnote       "query 1: &SQLObs rows" ;
   select * from dummy;
     %put NOTE: There were &SQLObs rows read from sashelp.class. ;

   create table dummy as
     select    distinct
               Sex,
               Age
     from      sashelp.class ;
     title "query 2: unique combinations of sex and age" ;
     footnote      "query 2: &SQLObs rows" ;
   select * from dummy;
     %put NOTE: There were &SQLObs rows read from sashelp.class. ;
quit ;

Actually the TITLE statements could precede the create table dummy expressions.

Also, I suspect you don't really want FOOTNOTE2 for the second table, so I changed it to FOOTNOTE.

Of course, this means an extra pass through the data, but if it is for purposes of printing, it's probably not big enough to be costly.