BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
matoma
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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

View solution in original post

5 REPLIES 5
Reeza
Super User

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
Super User Tom
Super User

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
Quartz | Level 8

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
Super User

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)

 

 

BASUG is hosting free webinars Next up: Mark Keintz presenting History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies on May 8. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
s_lassen
Meteorite | Level 14

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 616 views
  • 6 likes
  • 6 in conversation