Macro Beginner

[matoma]

I'm very new to macros and they are pretty confusing at the moment so any help would be greatly appreciated. 

Heres what i'm trying to do: 

In a footnote, please summarize the number of the films in which the actor was involved, the average popularity scores of these films, and the number of films that received awards.

I assigned each actor an id, now I'm just trying to make macros for the number of films, average popularity, and number of awards. I know I need %sysfunc and maybe %eval but I'm not sure how to do it. I attached below what I've tried. 

%let id=10;

%let films=%sysfunc(sum(films));

%let pop=%sysfunc(avg(pop));

%let awards=%sysfunc(sum(awards));

proc print data=hw3.films3;

where id=&id;

var title;

title1 "Actor Name: &actor10";

footnote "&films &pop &awards";

run;

[Reeza]

Macro variables are literal text replacements and one check - is your code valid when you replace it using cut and paste?

proc print data=hw3.films3;

where id=10;

var title;

title1 "Actor Name: &actor10"; *not shown so no idea what this should be;

footnote "%sysfunc(sum(films)) %sysfunc(avg(pop)) %sysfunc(sum(awards))";

run;

This is not valid SAS code. You'll want to first calculate these statistics ahead of time and create macro variables with the values you need ahead of time. A general rules is to first have working SAS code then convert it to a macro.

Note that within the PROC PRINT you can use SUM statement to have the totals printed at the bottom anyways. Not sure if it can do averages, but if not, PROC REPORT will do that.

https://documentation.sas.com/?docsetId=proc&docsetVersion=9.4&docsetTarget=p0f022tve3mt0tn162itrb0t...

Here are some macro references for you, that may help.

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[Tom]

This statement does not make sense.

%let films=%sysfunc(sum(films));

You are telling the macro process (%sysfunc()) to use the SUM() function. The sum function adds the values you pass it. So SUM(1,2,3) is 6.  Not only did you only pass it only one value to sum, but the value passed was the string "films" instead of a number.  

[bobpep212]

Listen to @Reeza , especially the first sentence. This was the key for me to understanding the macro facility as a beginner. The entire macro facility is a text replacement function. You aren't writing actual SAS programming in macros. You should already have the program written. Once you start wrapping that in a macro, you need to understand which values are going to change (the macro variables) and the best way to insert them into your already written code. This doesn't help answer your specific question, but as a beginner, I wanted you to know what concept really helped me and made the macro facility really click. But Reeza is also right that a lot of people think they need a macro when they don't. So thinking of it as a text replacement tool helped me understand when I need to use it and when I don't. 

[Quentin]

This looks like homework, which is fair to ask about.  But it's hard to give you a helpful answer without knowing what you're being taught.

So you have a data set of film info. You want to print all the films that actor with ID #10 was in and add a footnote with:

1. The name of the actor
2.  Number of the films in which the actor was involved
3.  The average popularity scores of these films
4.  The number of films that received awards

First, you need to calculate the above three statistics. That calculation is not done by the macro language, but by a SAS step.  Maybe PROC MEANS and output the value to a data set.  Maybe PROC SQL.  You could do it in a DATA step.  It all depends what you've been learning.

Then once you have the values calculated, you can write the four values to macro variables.  Actor Films Pop and Awards.  If you have the values in a SAS dataset, you can use CALL SYMPUT to create macro variables from values in a data set.

Then once you have the values in macro variables, you can use the macro variables in your code, as you have tried:

proc print data=hw3.films3;
  where id=&id;
  var title;
  title1 "Actor Name: &actor";
  footnote "&actor was in &films films with popularity rating &pop and received &awards awards";
run;
title; *Clear them;
footnote;

If this is an assignment in the macro language, my guess is they might be asking you to write a macro, so your code would be like:

%macro ActorReport(id=);
  %local actor films pop awards;

  *compute the statistics for the actor;
  *SAS code here;

  *create macro variables with the actor name, and each statistic;
  *SAS code here;

  *print the report;
  proc print data=hw3.films3;
    where id=&id;
    var title;
    title1 "Actor Name: &actor";
    footnote "&actor was in &films films with popularity rating &pop and received &awards awards";
  run;
  title; *Clear them;
  footnote;
%mend;

And then you could call the macro like:

%ActorReport(id=10)
%ActorReport(id=20)

[s_lassen · Accepted solution]

The assignments to your macro variables will not work. A %SYSFUNC statement is calculated immediately, like e.g. 

%let y=4;
%let x=%sysfunc(sqrt(&y));

But an assignment like

%let films=%sysfunc(sum(films));

will not work, as "films" is a text value.

You will have to calculate the values you want beforehand.

You can do this using a macro, e.g. 

%macro print_actor(id);
  %local name films pop awards;
  proc sq noprint;
    select name, sum(films),avg(pop),sum(awards) 
    into :name trimmed, :films trimmed, :pop trimmed, :awards trimmed
    from hw3.films3 
    where id=&id;
  quit;
  title "Actor Name: &name";
  footnote "&films &pop &awards";
  proc print data=hw3.films;
    where id=&id;
    var title;
  run;
%mend;

%print_actor(10);

Another possibility is to create a view with the values you want, and use #BYVAL in your title and footnotes.

proc sql;
  create view actor_sum as select
    id,title,name,sum(films) as films, avg(pop) as pop,sum(awards) as awards
    from hw3.films
    group by id
    order by id;
quit;

Title "Actor Name: #BYVAL(name)";
Footnote "#BYVAL(films) #BYVAL(pop) #BYVAL(awards)";
proc print data=actor_sum;
  by id name films pop awards;
  var title;
run;

This assuming that the NAME variable is on the table as well, and that all records with the same ID have the same NAME value.

I personally prefer the last solution (if you only want a single ID, just put a WHERE clause on PROC PRINT).