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?
@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.
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";
]
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
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.
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.
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.
Ready to level-up your skills? Choose your own adventure.