Quoting in SAS® Macro Programming Q&A, Slides, and On-Demand Recording
Watch this Ask the Expert session to learn about quoting in macro programs.
You will learn:
- When and why quoting is necessary.
- The perils of improper quoting.
- Macro quoting techniques including: %STR, %NRSTR, %BQUOTE and %SUPERQ and the “Q” functions - %QSYSFUNC, %QSUBSTR, etc.
The questions from the Q&A segment held at the end of the webinar are listed below and the slides from the webinar are attached.
Q&A
Could you provide that hyperlink for the demo?
Absolutely: https://go.sas.com/AFineQuotation
I'm new to macros, so why not just keep mlogic and symbolgen on while debugging?
You can certainly keep those options on while writing and debugging your macro code. That's what I like to do. 🙂
How about coming up with a list of "Mark's Rules" that specify what we should do to bullet proof our code?
If I could give you just one rule for bullet-proofing your macros, it would be this: Whenever your text is being processed by the macro processor, if you specifically don’t that text to trigger additional macro processing, QUOTE it, using %SUPERQ to resolve macro variable values ( or at least %BQUOTE), the “Q” functions for processing text, and %NRSTR to protect text at compile time. Consistent quoting is the key to bullet-proof code.
Your macro may work great today, but a seemingly innocuous change in a file name or the data values used to build data-driven code, and bang – it all comes apart in the most annoying way and is a total bear to troubleshoot. It was so hard to develop that habit after years of using &macvar willy-nilly in my macro code, and I still find myself slipping from time to time… but my macros are so much easier to build, test, and maintain when I adhere to this single rule.
I missed how to NOT execute mlogic.
If you want to disable the mlogic option, just submit: options nomlogic;
I got lost. Can you explain what you said about what "you can and can't see in your code"?
Sure! Normally, what you can see in your code is just the text you typed yourself. For example, if you typed &T, you can see all of that in the code. If you don't want to trigger any macro action, you'll need a compile time function to mask that ampersand. To say to the macro processor “leave my text alone!”, just use %NRSTR to quote that text, and the macro processor will totally ignore that ampersand - it'll treat AT&T as pure text. If you're executing some code and AT&T gets extracted into the word scanner or macro processor, that ampersand a macro trigger and it will cause the macro processor to go look up the value for T. But in this case, you won't be able to see the &T in your code, because your program got it from somewhere else - maybe from a list or a data set. When you see these issues happening – where you get an unusual response or a macro variable resolves that you didn't expect – and you don't see that macro variable name anywhere in the code you typed, that's a problem with execution time resolution. You'll want to find where that's happening, then use %SUPERQ, %BQUOTE, or one of the “Q” functions like %QSCAN, to make sure things get quoted properly and your text is not mistaken for macro code. You can learn more about the “Q” functions in my 2011 blog post titled Jedi SAS Tricks: Macro Q functions
Hello, I saw you use a data step with symput, and it used || (concat). Can you please explain what this does?
SYMPUTX basically allows you to take data from the data step, generally from variables or things that you found in the PVV, and write them out to the macro symbol table, where they'll persist until later in the processing. What's going on here is SYMPUTX is going to take the first argument, before the comma, and use that as the variable name. This will create a macro variable called firm list. The second argument is a single text string. That will become the value of the macro variable firm list when SYMPUTX executes. Nice thing about call SYMPUTX is that it will automatically trim leading and trailing spaces off your values. If you're using variable names instead of literally quoted text like you see here, you don't have to worry about extra spaces in there when you get into macro. The double bars are concatenation operators. I use them because the line was really long, and I didn't want it to wrap around by itself. It glues together these three individual pieces to make one long string that I'm sticking into the variable named firm list.
Where can I find all the Macro functions?
SAS Help Center: Summary of Macro Quoting Functions and the Characters That They Mask
What is the difference between single and double quotation marks in SAS? Where is it better to use each one?
In the traditional data step code, you can use double and single quotes interchangeably, unless you get the macro processor involved. When you're trying to have the macro processor generate part of a SAS code text string, you'll need to use double quotation marks around the text. For example, if macro variable T holds the time and you want that inserted in your title statement, the &T needs to be inside of double quotes to be seen as a macro trigger. Double quotes allow the word scanner to tokenize the text between the quotes, and if macro triggers are found, they are passed to the macro processor for resolution. Text enclosed in single quotes is not separately tokenized, so macro triggers can’t be detected. Single-quoted text will always be set right to the compiler as an unmodified text literal.
Within the macro processor, quote characters are just text. Macro quoting functions are used instead of quotation marks to determine what text should trigger additional macro processing, and what text should be returned to the input stack as-is.
Can you explain a little more about the SELECT INTO method of creating a macro vars?
This question resulted in a longish, spontaneous demo that doesn't lend itself well to conversion into text. You can see the answer to this question towards the end of the recording - please watch that instead. Thanks 🙂
Can you please explain the Git Hub copying part?
The code uses PROC HTTP to download a file containing a macro definition from my sas-macros repository on Github. It then runs the program to compile the macro. The macro was used to unpack the ZIP file for this webinar and save the files to the location I specified in my SAS On Demand for Academics session. If you copy and run that code in SAS On Demand for Academics, it will install them for you, too. You only need a free SAS Profile to get access to SAS On Demand for Academics. And I have a lot of other useful, self-documenting macros in that repository. You can freely download those and use them as you see fit.
Is there an easy way to see all the local and global macro values created?
/* Print all LOCAL variables to the Log */
%put _LOCAL_;
/* Print all GLOBAL variables to the Log */
%put _GLOBAL_; Can you please share the SQL code that you wrote for creating series of macro variables?
The original code with the select into list that's included in that zip package so you should have the starter code in there.
%macro makeList(value);
/* Get all the values into a delimited list */
proc sql noprint;
select strip(Name)
into :firmList separated by '|'
from afq.Firms
where MktCap > %superq(value)
;
quit;
%put NOTE: The list is %SUPERQ(firmList);
%mend makeList;
%makeList(5000000000) Result:
NOTE: The list is AT&T|Crate & Barrel|Johnson & Johnson|Procter & Gamble
%macro makeSeries(value);
/* Get the values into a series of macro variables */
proc sql noprint;
select strip(Name)
into :firm1-
from afq.Firms
where MktCap > %superq(value)
;
quit;
%put NOTE: Macro variables firm1 through firm&sqlobs created.;
%do i=1 %to &sqlobs;
%put NOTE- firm&i is %superq(firm&i);
%end;
%mend makeSeries;
%makeSeries(5000000000) Result:
NOTE: Macro variables firm1 through firm4 created.
firm1 is AT&T
firm2 is Crate & Barrel
firm3 is Johnson & Johnson
firm4 is Procter & Gamble
In the result you showed, there was no beginning quote and ending quote for the firm list. I had a similar problem last week when the email address was "m&s@compny.com", do you have any recommended best practices for quoting (or not quoting?) for the macro text (or other common situations)?
The list produced by the SQL query (firmList) contained this text:
AT&T","Crate & Barrel","Johnson & Johnson","Procter & Gamble
It was deliberately created without a leading or trailing quote so I could resolve it in my code like this:
data new;
set afq.Firms;
where Name in ("&firmList");
run; The DATA step code needs to resolve the list as a series of text strings. The leading and trailing quotes are provided in the DATA step code, and the macro variable resolves between those quotes.
How do you create a macro variable in SQL like select distinct code, into :code_list SEPARATED BY "','" from pb_dexcom_dx;? How do you use this in query this macro variable?
proc sql noprint;
select strip(Code)
into :codeList separated by '","'
from pb_dexcom_dx
;
quit;
data new;
set my.otherData;
where code in ("&codeList");
run; Is there a way to see all the macro values in a data set or proc SQL table so they can be referenced later instead of in the Log?
As a general rule, we use macro variables for dynamic code generation. Writing the values to a permanent table kind of takes the “dynamic” out of that... However, you can see a snapshot of your SAS session’s current macro variables and values in PROC SQL by querying dictionary.macros. The same information can be accessed by other SAS processes using the metadata view sashelp.vmacro. For example, this PROC SQL query lists the macro variables created for this webinar:
proc sql;
select *
from dictionary.macros
where name in ('T','LIST')
;
quit; Result:
Note that the value for LIST is quoted, and you can see some of those “delta characters” we discussed in the webinar revealed in this report 😊
Recommended Resources
Blog posts:
- Macro Quoting Made Easy
- SAS Macro Quoting – A Look Behind the Scenes
- Don’t %QUOTE() Me on This: A Practical Guide to Macro Quoting Functions
SAS Documentation:
Please see additional resources in the attached slide deck.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
