How Do I Build a Macro Application? Q&A, Slides, Code, and On-Demand Recording
Watch this Ask the Expert session to learn how to build a macro application, saving you time on the development and maintenance of your SAS programs.
Join Stacey Syphus as she shows you how SAS macro programming can reduce the amount of time that you spend on the development and maintenance of SAS programs by providing programmers with the ability to write SAS code that will rewrite itself. The techniques you'll learn during this webinar can minimize the amount of SAS code that you need to write to perform common tasks. In this presentation, you’ll see an example of how to turn a SAS program into a macro application. You’ll learn the following skills through a comprehensive case study:
- How to build a macro application that dynamically updates SAS code based on input parameters and data.
- How to validate macro parameters and build custom log messages for useful feedback.
- How to save macro programs for easy reuse.
The questions from the Q&A segment held at the end of the webinar are listed below. The slides and code shown during the webinar are attached.
Q&A
Does this macro code work in both SAS 9 and SAS Viya?
Yes, the macro facility works in both SAS 9 and SAS Viya.
Can you discuss resolving the macro at compilation vs. execution?
When generating SAS code, macro triggers process text while the program you wrote is still parsed into tokens - it hasn't been compiled yet. By the time the SAS program compiles and executes, the macro code has already resolved.
Sorry, what does the little dash do (behind e.g. :topsupp)?
It creates a series of macro variables all with the same name prefix. For example, if there were 10 rows of data, :name1- would create macro variables name1, name2, name3 ... name 10.
What is the preferred way of calling macros? I have seen examples where it ends with a semicolon ("%macroname(param1=x);"), and others (even coming from SAS), do not have semicolons ("%macroname(param1=x)"). I have seen some weird instances where if a semicolon is not used when trying to call a macro function, the code does not work.
A macro call never requires a semicolon. If a semicolon is necessary to make the program run, it's required by the surrounding SAS code, not the macro call.
What are your thoughts or ideas on using the period at the end of your macro variable name [ex: &mymacro. ]?
The period acts a delimiter between the end of the macro variable name and any text that may immediately follow. It is only required when the macro variable reference is embedded in other text. Otherwise, it's a personal choice in programming style. I personally only use the . delimiter when it is required by the code.
What is the use for #&rank?
The value assigned to the macro variable rank will be placed in the code for you wherever the macro variable reference (&rank) is found in the code, just as if you had typed it yourself. For example, if you assigned a value of 1 to the macro variable rank, then this code:
title "Orders for #&rank &&name&rank";
...generates this title in the report:
Orders for #1 Eclipse Inc
Why are we calling %if %do %end?
The %IF %THEN statements are used to conditionally execute the TITLE2 statement based on the value of the ot macro variable. %DO and %END are required when the generated text contains semicolon characters. So because a whole step or steps are generated conditionally, the %DO/%END block is required.
How will I put multiple values inside the same macro sandwich %macro and %end at once?
In the example shown, there are multiple programs steps and multiple macro variables and references inside the %MACRO/ %MEND "sandwich".
Can you explain the hyphen after the INTO statement creating the macro variables in the PROC SQL code? It looks like it creates incremental macro variables - so incrementing up to the number of records in your dataset? Do you have to use numbers? Can you use "a" to get incremental macro variables using alphabetical order?
It creates a series of macro variables all with the same name prefix. The suffix must be numeric - "a" is not valid. For example, if there were 10 rows of data, :name1- would create macro variables name1, name2, name3 ... name 10. See minute 56:00 in the recording for visual explanation.
Is %local statement necessary to run?
%LOCAL is required if you want to ensure a macro variable remains local to the macro program - that is, to ensure it does not wind up in the global symbol table.
Do you have to mask the text in "%put" adding a superq or str()?
IF there are special characters in the macro variable value, yes. %SUPERQ is best for quoting macro variable contents, %STR or %NSTR for quoting hard-coded text (where you can see the special characters in the code). To be safe, I personally resolve macro variables with %SUPERQ when writing a macro application unless I intend to execute the result as code.
I need to add multiple values inside the same like ex:%supplierreport(ot=1 , rank=1) , Can I add it as %supplierreport(ot=1, 2 , 3 , rank=1, 2, 3)?
You cannot use commas as part of a macro parameter value - the macro processor will interpret that as a new positional parameter, not part of the current value. You can pass in multiple values separated by a different delimiter but, if you want access to the individual values, you will have to write code to parse them out of the parameter content yourself.
Minoperator was only available since SAS 9.2 before, it was not possible to use in. An alternative is to use the global option minoperator.
Yes, both of these options became available in SAS 9.2.
Can I replace a value of the local macro variable based up on the iteration loop?
Yes - a local variable’s value can be modified and resolved multiple times as long as the macro program remains running.
What is the difference between defining the macro as: %macro macroname (); ... %mend; versus %macro macroname(); ... %mend macroname;?
The macro name on the %mend is optional. I personally only use it for long macros where I won't be able to see the %macro statement while writing the program, or for programs containing many macro definitions to help me keep track of where I am.
Can I can add multiple values inside multiple lines? ex. %supplierreport(ot=1) %supplierreport(ot=2)?
This syntax is perfectly acceptable It will execute the macro program twice, once for each value supplied. You can also call a macro from another macro program. The other program might use a %DO loop, like this:
%do i=1 %to 3;
%sumreport(ot=&i)
%end;
What does - sign mean in "into :rank-"?
It creates a series of macro variables all with the same name prefix. For example, if there were 10 rows of data, into :rank1- would create macro variables rank1, rank2, rank3 ... rank10.
In writing macro codes, it feels confusing where % is needed. Is there a simple rule for where a "%" is needed?
All macro statements, and calls to macro programs or functions, require a % sign.
Were the examples presented today considered complex macros?
They were moderately complex.
Can you do &ot not in (1, 2, 3) instead of NOT &ot in (1 2 3)?
No, macro logic is not sophisticated enough to handle that. First the logical expression (&ot in 1 2 3) must resolve to TRUE or FALSE, then the NOT operator is applied. Also remember that the macro IN operator does not work with commas or parentheses in the expression. So the only correct form is: NOT(&OT in 1 2 3).
Is there any macro to automate reports in SAS to outlook?
There isn't one, but you can write one fairly easily. There are lots of examples online of code to send email from SAS. Here is some of the documentation on this. If this isn’t what you need, just search on sas.com.
Does the code work with SAS Enterprise Guide version 7.15 (7.100.5.5850) (32-bit)?
That depends on the version of SAS on the server Enterprise Guide connects to. If it's SAS 9.2 or higher, yes.
Does /minoperator stand for Macro In Operator? Will the macro fail if you use IN logic and you don't specify /minoperator?
Yes, and yes 🙂
When you created ERROR message, without specifying the font color "red", can SAS automatically use red color for the word "ERROR"?
Yes, when you print a message to the log that begins with ERROR: (all upper case and colon required) then the text will be colored red.
Does having PROC SQL into within a macro create only local macro variables?
You can create global variables with this technique by declaring the variables to be populated with a %GLOBAL statement before the PROC SQL step executes.
In your discussion of replacing spaces with underscore in a macro using %sysfunc, there was no semi-colon at the end of the %sysfunc(trawrd (.... line. Is a semi-colon not needed?)
No, a semi-colon is not needed.
Can you quickly once more explain the sysfunc code in the replacetext macro sandwich?
See on-demand recording at minute 58:34 for explanation.
Is there any course to take for financial modeling using SAS to do price * quantity plus actuals including outlook volumes?
I’m sorry, but I’m not aware of a specific course.
Why don't you use a semi-column at the end of your macro call?
A macro call never requires a semicolon. If a semicolon is necessary to make the program run, it's required by the surrounding SAS code, not the macro call.
What does blank look like for order_type?
When printed it appears as white space.
%local test; local variable test value is 1 for first iteration. local variable test value is 2 for second iteration, and so on. How can I write that in SAS code?
This can be accomplished with an iterative macro %DO loop. Something like this:
%macro myLoop;
%local test;
%do test=1 %to 10;
<sas code>
%end;
%mend myLoop;
This will create a local macro variable named test with a value of 1 for the first iteration, 2 for the second iteration, etc. For more information about the iterative macro %DO loop, see this page: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/p0ri72c3ud2fdtn1qzs2q9vvdiwk.htm
My coworkers often use something like "&var." when using macros - they put a period after calling the macro variable. Can you help explain why? I think it might be something with formatting?
It is only required when the macro reference is embedded in other text to delimit where the macro name ends and the surrounding text begins. For example:
%let prefix=Customer;
%put The variable names are &prefix_id and &prefix_name;
Without the . to identify where the name of the macro variable stops, the macro processor would try to resolve variables named prefix_id and prefix_name, which do not exist. Adding the . delimiter:
%put The variable names are &prefix._id and &prefix._name;
Allows the macro processor would try to properly resolve the prefix, variable in both cases, printing The variable names are Customer_id and Customer_name to the log.
It's a personal choice in programming style if the macro reference ins not embedded I other text. I personally only use the . delimiter when it is required by the code.
How a user can enter values of supplier rank from his/her computer thru an executable program?
There are a so many options for this, but they all depend on how the user is accessing / interacting with SAS. Here are some suggestions for each of the major SAS clients:
- The SAS Windowing Environment (PC SAS): write the macro application using %WINDOW and %DISPLAY statements to interactively gather information from the user. This SUGI paper has instructions and example code: https://support.sas.com/resources/papers/proceedings/proceedings/sugi27/p192-27.pdf
- Enterprise Guide: use prompts to gather user input. This tutorial can get your started: https://video.sas.com/detail/video/3829767505001/create-and-use-prompts-in-queries-and-tasks
- SAS Studio: create a user-defined task to gather the information. There is a free eLearning course available about how to create custom tasks at https://support.sas.com/edu/schedules.html?ctry=us&crs=CWTSS
Do you have any suggestions on things to keep in mind when updating existing code to include macros?
- Document everything! Take time to add comments or custom log notes that will explain the macro. For example, you could build logic into the macro so that if it is called with a ? as the first parameter, then detailed notes are printed in the log explaining the macro and parameters. Examples of this can be found in Mark Jordan’s SAS Macro repository on Gtihub: https://github.com/SASJedi/sas-macros
- Build in ‘safety precautions’
- Use conditional macro %IF statements to validate all parameters required to make the generated code run without error and to produce meaningful results.
- If parameter values are invalid, produce clear, custom error messages in the Log to help the user correct the problem and terminate macro execution with a %RETURN statement.
- When possible, auto-correct parameters when execution begins. For example, if a parameter is case sensitive, include statements in the macro to normalize the case and ensure program logic works as expected.
- Be careful to quote resolved macro values and use %QSYSFUNC to execute SAS functions when the results could possibly contain special characters.
- Thoroughly test the finished macro using valid parameter inputs, then with each individual parameter left blank, then with various invalid values. Make sure the macro produces useful error messages or valid results in every case.
Is there an advantage to using the parameter name in the macro call instead of just sending the value?
Named parameters are called keyword parameters. You can enter them in any order you like, but you have to enter the name=value pairs. Unnamed parameters are positional parameters. You don’t have to type the parameter name anymore, but you DO have to enter them in the proper order. Depends on your preference.
Just a quick correction to the error messages - it states that 'blank' is valid value for 'ot' but doesn't the null value trigger an error?
Good catch! You are correct. I fixed the error messages in the code to remove blank as a valid value.
Do you have any tips for debugging macro programs as you write them to find mistakes or issues?
Debugging macro programs can be tricky, but there are several things that help me. Starting with a functioning program without any macro references is always a good practice. I tend to use a lot of %PUT statements to write values of macro variables to the log to confirm they are assigned correctly (ex: %put &=rank;). I also typically turn on options such as SYMBOLGEN, MLOGIC, and MPRINT to view detailed messages in the log.
At the very start of your presentation, you talked about building a macro to use a data step function?
SAS offers hundreds of different functions to manipulate data. It offers a much smaller subset of macro functions (each begin with %) that can be used to modify the text in your program. If you would like to ‘borrow’ one of the regular SAS functions and use it like a macro function, that is possible with the %SYSFUNC macro function. For example, if you would like to insert the current date into a footnote, you could use this combination of %SYSFUNC and the TODAY function. The second argument of the %SYSFUNC function is an optional format that is applied to the result of the first argument:
footnote "Report Created on %sysfunc(today(),date9.)";
Would you explain a little more about using colons for both building macro variables and specifying a range in those variables... The variable names included a dash at the end, but the end of the range didn't--"topsupp1- :topsupp5"?
I suggest looking at this page for more detailed information about the INTO clause: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/n1y2jszlvs4hugn14nooftfrxhp3.htm
Rank defined as macro variable; then %do rank = 1 %to 5 ; How is it resolved without &rank?
The %DO macro loop statement defines the macro variable named rank and assigns the value 1-5, depending on the iteration of the loop. The code that is inside the %DO loop includes &rank, so in each of those instances &rank will resolve to the loop number. Visit this page to learn more about iterative %DO loops: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/p0ri72c3ud2fdtn1qzs2q9vvdiwk.htm
What if I want to use select into in SQL to create a macro variable but I need to keep the data in order? I have tried to put the order by clause, but it issues a warning in the log.
Using an ORDER BY clause in the SQL that creates macro variables works fine. Consider this macro program:
%macro orderedMacroVariables(sortBy);
proc sql noprint;
select distinct Type
into :T1-
from sashelp.cars
order by Type &sortby
;
quit;
%PUT NOTE: ORDER BY clause was "ORDER BY TYPE &sortBy";
%do i=1 %to &sqlobs;
%put NOTE- T&i=&&T&i;
%end;
%mend;
Calling the macro like this:
%orderedMacroVariables()
Produces this result in the Log:
NOTE: ORDER BY clause was "ORDER BY TYPE "
T1=Hybrid
T2=SUV
T3=Sedan
T4=Sports
T5=Truck
T6=Wagon
And calling the macro specifying DESC:
%orderedMacroVariables(desc)
Produces this result in the Log:
NOTE: ORDER BY clause was "ORDER BY TYPE desc"
T1=Wagon
T2=Truck
T3=Sports
T4=Sedan
T5=SUV
T6=Hybrid
What general number of records would cross the line for making the incremental variables efficient? 100? 1000? When would it be better to use a different method besides individual variables?
There is no meaningful general answer to this question. The number of macro variables that can be effectively and efficiently employed depends on a number of widely variable factors, including the size of the macro variables being created (does each variable contain an 8 character values or a 65,534 character value?), the amount of memory available in the SAS environment, and what other processes are being executed by SAS when using the macro variables.
Is there a way to create a fiscal year macro function using intnx?
I found this paper which might be helpful: https://support.sas.com/resources/papers/proceedings/proceedings/sugi25/25/cc/25p084.pdf
Recommended Resources
How to Create Macro Variables and Use Macro Functions
3 Steps to Build a SAS Macro Program
Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars.
