James Bond: “Give me the old fashioned target range, Quartermaster. “
Q: “Yes, well, it's called the future, so get used to it.”
In James Bond movies, it's Q's job to disguise wickedly effective technology as seemingly innocuous everyday objects. The power of those seemingly everyday objects, revealed at just the right moment, enabled 007 to pull off amazing feats and defeat the evil-doers.
In SAS programming macro is our secret weapon, enabling similarly amazing feats. But frequently, keeping "special characters" concealed until just the right moment is the key to victory. As a SAS programmer, I've been involved in many an epic struggle to debug a macro program. And more than once, a macro variable containing special characters becoming "unquoted" too soon proved to be my downfall. It's a delicate game, and is especially problematic when a macro uses values read from external files, the operating system, or when values were inserted into macro variables using DATA step CALL routines or PROC SQL’s SELECT INTO syntax. Macro’s “Q” functions can be our secret weapon, helping us keep those special characters under cover until we are ready to unveil their true power.
While processing text, several macro functions produce unquoted results even if the text they are processing was quoted when it was stored. Because of this, functions like %SCAN, %UPCASE, etc. may produce unexpected results. When producing text using SAS Data step functions via %SYSFUNC or when reading information from operating system file/path names, similar issues often arise. However, %SYSFUNC and several of the macro text processing functions have corresponding “Q” functions which produce quoted text instead of unquoted text. The Q functions generally quote the same characters as the macro function %NRBQUOTE.
For example, %SCAN and %QSCAN accept the same arguments, but the text produced by %QSCAN is quoted, while the text produced by %SCAN is not. For a more detailed look at the “Q” functions, see the SAS online documentation.
To illustrate the difference, let’s start with a simple example:
%local test two text;
%let test=%NRSTR(One &two and three.);
%let two=The wrong text;
/** Extract and print the second word, macro references inadvertently resolve **/
%let text=%scan(&test,2,%str( ));
%PUT NOTE: Resolved with SCAN: &text;
/** Extract and print the second word, without resolving macro references **/
%let text=%qscan(&test,2,%str( ));
%PUT NOTE: Resolved with QSCAN: &text;
Partial SAS Log:
NOTE: Resolved with SCAN: The wrong text
NOTE: Resolved with QSCAN: &two
One of our SAS Jedi Masters, Warren Repole (Author of “Don’t be a SAS Dinosaur”) says: “Using the Q versions of macro functions is a best practice, because they typically avoid many more problems than they introduce in your code. Likewise, when writing macro applications, avoid macro variable references (like &varname) – use %SUPERQ to resolve the value instead.”
To demonstrate why Warren and I advocate this approach, consider the following SAS macro which parses text from macro variables which were populated using the DATA step's CALL SYMPUTX() routine:
call symput('t',' oops - my bad!');
call symputx('customer','Our valued customer CS&T','L');
%PUT NOTE: The text is "&customer";
%PUT NOTE: SUPERQ says the text is "%superq(customer)";
%PUT NOTE: SCAN says this customer is %scan(&customer,-1,%STR( ));
%PUT NOTE: QSCAN says this customer is %qscan(&customer,-1,%STR( ));
%PUT NOTE: SCAN (with BQUOTE) says this customer is %scan(%BQUOTE(&customer),-1,%STR( ));
%PUT NOTE: QSCAN (with NRBQUOTE) says this customer is %qscan(%NRBQUOTE(&customer),-1,%STR( ));
%PUT NOTE: SCAN (with SUPERQ) says this customer is %scan(%SUPERQ(customer),-1,%STR( ));
%PUT NOTE: QSCAN (with SUPERQ) says this customer is %qscan(%SUPERQ(customer),-1,%STR( ));
Partial SAS Log:
NOTE: The text is "Our valued customer CS oops - my bad!"
NOTE: SUPERQ says the text is "Our valued customer CS&T"
NOTE: SCAN says this customer is bad!
NOTE: QSCAN says this customer is bad!
NOTE: SCAN (with BQUOTE) says this customer is bad!
NOTE: QSCAN (with NRBQUOTE) says this customer is bad!
NOTE: SCAN (with SUPERQ) says this customer is CS oops - my bad!
NOTE: QSCAN (with SUPERQ) says this customer is CS&T
Whew - what a lot of work to get the text I wanted without inadvertently resolving other macro variable values! As you can see, using the “Q” functions and %SUPERQ as my default approach to manipulating and resolving macro variables can help avoid many pitfalls.
Until next time, may the SAS be with you!
PS: You didn't think I'd leave you "codeless", did you? Of course not! You can download a ZIP file containing all of the SAS code used in this blog, plus a "bonus" macro which demonstrates how quoting problems can surface when reading information in from the operating system. Just click HERE for your code!