BookmarkSubscribeRSS Feed
jteres
Obsidian | Level 7

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?

3 REPLIES 3
FreelanceReinh
Jade | Level 19

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

ChrisNZ
Tourmaline | Level 20

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);

or

ods text=Nb obs: &sqlobs";

]

mkeintz
PROC Star

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

  1. a create table dummy as ...,
  2. then the title and footnotes,
  3. 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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2277 views
  • 0 likes
  • 4 in conversation