Hi All,
I'm running a report on building stats. I'm using the by statement in the report so that I get separate tables for each building. I want to add I have the variables building_id and building_name to the title of each report. I am not sure how to create a macro for 2 variables. Can I modify this code to include the building_name as shown below? Also, instead of calling the individual buildings with %do_id at the end, can I build a smarter macro that looks for each unique ID number and runs the report?
%macro do_id(building_id);
%macro do_name(building_name);
title1 "Annual Report for &id and &name;
proc report data=buildings;
by buiding_id;
define .....
run;
%mend do_id;
%mend do_name;
%do_id (A94);
%do_id (A02)
%do_id (A503);
%do_id (A06);
Make sure the parameter names you define in the %MACRO statement match the macro variable references in the code.
Make sure to use double quotes so that macro references are resolved.
Make sure to quote the values of character variables in the generated SAS code.
%macro do_id_name(id,name);
proc report data=have nowd split='/';
title "&id and &name";
where building_id = &id and building_name = "&name" ;
columns Type Subtype Region Count Expected count_ontime late pct_within pct_not_within;
define Type/display " Type";
define subtype /display "subtype";
define Region/display "Region";
define Count/ display "Count";
define Expected/ display 'Expected';
define count_ontime/display '# Ontime';
define late/display '# late';
define Pct_within/display '% ontime' format=PERCENT7.0;
define Pct_not_within/display '% late' format=PERCENT7.0;
run;
%mend do_id_name;
%do_id_name(1, 1222 smith st);
To generate a call for each value in the data there are a number of methods.
With a small list of values you might just generate the calls into a macro variable.
proc sql noprint ;
select distinct cats('%do_id_name(', building_id , ',' , building_name ,')')
into :runlist separated by ';'
from have
;
quit;
&runlist ;
Not sure what the question is.
If you want the TITLE to reflect the value of the BY variable then use #BYVAL() in the TITLE statement. See the documentation on the TITLE statement. http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000220968.htm
title1 "Annual Report for #byval(building_id) and #byval(building_name)"
options nobyline;
proc ...
by building_id building_name;
....
If you want to pass more than one parameter into a macro then just define it with more than one parameter.
%macro do_id_name(id,name);
....
title1 "Annual Report for &id and &name";
...
where building_id = &id and building_name = "&name" ;
....
%mend do_id_name;
Hi Tom,
Thanks for your suggestions. Since I have a couple of different tables I want to create for each building I think using a macro with more than one parameter is the best solution. However, I'm new to macros and am having trouble with the next step. I wrote this marco: %macro do_id_name(id,name). When I call the macro with %do_id_name; I get this error:
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference NAME not resolved.
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, INPUT, PUT.
ERROR 76-322: Syntax error, statement will be ignored.
I have:
%macro do_id_name(id,name);
....proc report
title1 "Annual Report for &id and &name";
...
where building_id = &id and building_name = "&name" ;
....
proc report
title1 "Annual Report for &id and &name";
...
where building_id = &id and building_name = &name ;
....
%mend do_id_name;
%do_id_name;
You don't need a macro if you're generating all at once is what Tom is implying. The BY variables become macro variables of a sort and you can reference them, without having to explicitly create them.
See how Tom used #byval(building_id) and #byval(#name) in the title above.
If you want a macro route then its best to post your code and log to help with debugging. for starters, your last line that calls the macro doesn't include the input parameters or the underscores in the macro name.
You will need to create a real macro with real code replacing the ...
This message is an indication that your macro is not defined. Check for errors from SAS when it actually compiled the macro.
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference NAME not resolved.
You also might have unbalanced quotes or () that are confusing it.
Hi Reeza and Tom,
I don't want to use the #byval because I have 3 report tables I need to make for each building id. If I do a byval, it will run table #1 for each building, and then table 2 for each building and so on. I want instead to run for building 1 tables #1 - table #3, then building 2 tables 1-3, etc.
Here is my code. Do I need to call each building id/name combo in the %do_id_name; statement? For instance %do_id_name (1, 1222 smith st)? I'd like for the macro to run through the dataset and run the report for each unique building id/name combo, instead of me having to tell it to. Is that possible?
%macro do_id_name(building_id,building_name);
proc report data=have nowd split='/';
title '&id and&name';
where building_id = &id and building_name = &name ;
columns Type Subtype Region Count Expected count_ontime late pct_within pct_not_within;
define Type/display " Type";
define subtype /display "subtype";
define Region/display "Region";
define Count/ display "Count";
define Expected/ display 'Expected';
define count_ontime/display '# Ontime';
define late/display '# late';
define Pct_within/display '% ontime' format=PERCENT7.0;
define Pct_not_within/display '% late' format=PERCENT7.0;
run;
%mend do_id_name;
%do_id_name;
Yes its possible, first off though macro variables only resolve when in double quotes. And you may need to quote your macro variables in your where condition to have the code be valid SAS code.
Again, you've provided parameters at the top but haven't called it with any...
Hi Reeza,
I forgot that double quotes were needed. I've substituted double quotes for singles in the proc report statement and around "&name" as this is a character field. I've added the parameters when I call the macro: %do_id_name (building_id,building_name);
However I'm still getting this error:
2500 %mend do_id_name;
2501 %do_id_name(building_id,building_name);
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference NAME not resolved.
NOTE: Line generated by the invoked macro "DO_ID_NAME".
1 proc report data=les.all_cover nowd split="/"; where building_id = &id and building_name =
-
22
76
1 ! "&name" ; columns ....
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference NAME not resolved.
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, INPUT, PUT.
ERROR 76-322: Syntax error, statement will be ignored.
The last example in the link below is close to what you're looking for, there are also lots of other examples on here, but get your base macro working first
Statistical Computing Seminar: Introduction to SAS Macro Language
Thanks, the documentation is helpful.
Make sure the parameter names you define in the %MACRO statement match the macro variable references in the code.
Make sure to use double quotes so that macro references are resolved.
Make sure to quote the values of character variables in the generated SAS code.
%macro do_id_name(id,name);
proc report data=have nowd split='/';
title "&id and &name";
where building_id = &id and building_name = "&name" ;
columns Type Subtype Region Count Expected count_ontime late pct_within pct_not_within;
define Type/display " Type";
define subtype /display "subtype";
define Region/display "Region";
define Count/ display "Count";
define Expected/ display 'Expected';
define count_ontime/display '# Ontime';
define late/display '# late';
define Pct_within/display '% ontime' format=PERCENT7.0;
define Pct_not_within/display '% late' format=PERCENT7.0;
run;
%mend do_id_name;
%do_id_name(1, 1222 smith st);
To generate a call for each value in the data there are a number of methods.
With a small list of values you might just generate the calls into a macro variable.
proc sql noprint ;
select distinct cats('%do_id_name(', building_id , ',' , building_name ,')')
into :runlist separated by ';'
from have
;
quit;
&runlist ;
Thanks Tom! This was very helpful.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.