Macro Footnote Question

[matoma]

How do I create the macros I want to use in the footnote described below if I want to base the macros on the values of the movies, pop, and awardnum variables? Is this possible or is there another way to create a footnote with the values of those variables? I tries #BYVAR and #BYVAL also and it didn't work. 

proc sql;

create table hw3.actor as

select id, title, actor, count(title) as movies, avg(popularity) as pop, count(awards) as awardnum

from hw3.films3

where id=&id;

quit;

proc print data=hw3.actor;

where id=&id;

var title;

title "Actor Name: &actor10";

footnote "&movies &pop &awardnum";

run;

[PaigeMiller]

I can't try this without your data, but it seems like your SQL isn't right. It seems like you want

group by id,title,actor

in your SQL in order to get the counts and averages.

Then, when you print BY ID; the variables #BYVAL and #BYVAR ought to work.

[matoma]

My proc sql is giving me kind of what I want in the sense that it has the total number of movies, the average popularity, and the number of awards. Its just showing up 7 times for each movie title and id. I guess what I'm trying to do is take the values of 7 for movies, 57.28 for pop and 7 for awards and put it into a footnote. 

[PaigeMiller]

Yes, but if you do the PROC PRINT with the BY statement, then FOOTNOTE with #BYVAR and #BYVAL works fine.

by id movies pop awardnum;

[Quentin · Accepted solution]

Take a look at @s_lassen 's answer to your question yesterday.  His first code block shows how to use SQL into:  to create macro vars.

https://communities.sas.com/t5/SAS-Programming/Macro-Beginner/m-p/633338

[Tom]

But the code you posted has neither a BY statement nor any use of the #BYVAL() tag.

proc sql;
create table hw3.actorS as
  select id, title, actor
       , count(title) as movies
       , avg(popularity) as pop
       , count(awards) as awardnum
from hw3.films3
group by id
order by id
;
quit;

proc print data=hw3.actorS;
  by id actor movies pop awardnum;
  pageby id;
  var title;
title "Actor Name: #byval(actor)";
footnote "Movies #byval(movies) Popularity #byval(pop) Awards #byval(awardnum)";
run;
title;
footnote;

 Notice that there is no need for defining a macro or for using any macro varaibles.