This week’s author tip is from Robert Virgile and his book “SAS Macro Language Magic: Discovering Advanced Techniques”. Virgile chose this tip because discovering and developing this technique will help you make the most of MACROS.
- Sas Macros Cheat Sheet Excel
- Macro Sas Put
- Sas Macros Basics
- Sas Macros Cheat Sheet Printable
- Sas Graphs Cheat Sheet
We hope you find this tip useful. You can also read an excerpt from Virgile’s book.
- The following points summarize the key similarities and differences between R and SAS: Both systems have been designed for statistical programming and analytics. The R environment contains only one programming language. In the SAS System multiple “sub-languages” are used (SAS/BASE with its data step, Macro language, IML etc.).
- Macro Variables. Macro variables hold the value of text strings. The easiest way to assign a value to a macro variable is using%let:%let macvar = Hello!!;%put The value of macvar is &macvar; The value of macvar is Hello!!. Note that: – The value of a macro variable is referenced using & – Text without%’s or &’s (called.
Macros Tip #1: A “Most Commonly Asked” Question
Institute for Digital Research and Education. Search this website. R; Stata; SAS; SPSS; Mplus; Other Packages.
One macro language question arises again and again. How can I get macro language to perform a %DO loop over a list of values? For example, this syntax is illegal in macro language:
But what if that is exactly what you need: execute a %DO loop for each name on the list? Macro language lets you work around this limitation, as long as you program the logic yourself. Here are the steps you’ll need to take.
First, create a macro variable holding the list of names. A %LET statement might look like this:
Of course, there are many ways to generate a list of values. You could pass it as a parameter:
Or, if a data set stores the names, SQL can retrieve them into a macro variable:
Once the list of names is ready, execute the %DO loop for each name. A few considerations come into play here:
- While a variety of approaches exist, none of them take very long to execute. It might be advisable to select a simple method, rather than a faster-running method.
- All of these statements must appear within a macro definition. %DO loops and %LOCAL statements are not permitted elsewhere.
Begin with a more complex approach:
When &i is 1, %SCAN assigns the first word in &NAME_LIST as the value of &NEXT_NAME. When it is 2, %SCAN assigns the second word. After %SCAN reads all the words in &NAME_LIST, it returns a null value and the %DO loop ends. Is the code difficult to read? As always, beauty is in the eye of the beholder.
An alternative approach has macro language count the number of names in the list. While entire macros have been written for this purpose, advances in macro language make counting words a straightforward task:
The DATA step function COUNTW counts the number of words in a list, and %SYSFUNC permits macro language to use most DATA step functions.
Of course, complications arise. Will your logic still work when:
- The list is empty?
- An item on the list is a significant word in macro language? For example, what if &NEXT_NAME is NE or OR?
Those issues can arise, but are beyond the scope of this tip. This tip is to understand the steps needed to instruct macro language to process each item on a list of values.
For more information about the macro language and the magic you can create with it, check out Robert Virgile’s book “SAS Macro Language Magic: Discovering Advanced Techniques”.
Have you ever tried to pass comma-delimited values to SAS macro or to a SAS macro function? How can SAS distinguish commas separating parameters or arguments from commas separating parts of the values?
Passing comma-delimited value as an argument to a SAS macro function
Let’s say you want to extract the first word from the following string of characters (these words represent column names in the SASHELP.CARS data table):
make, model, type, origin
If you run the following code:
%let firstvar = %scan(make, model, type, origin, 1);
you get is the following ERROR in your SAS log:
ERROR: Macro function %SCAN has too many arguments.
That is because %scan macro function sees and treats those make, model, type and origin as arguments since commas between them are interpreted as argument separators.
Even if you “hide” your comma-delimited value within a macro variable, it still won’t do any good since the macro variable gets resolved during macro compilation before being passed on to a macro or macro function for execution.
%let mylist = make, model, type, origin;
%let firstvar = %scan(&mylist, 1);
You will still get the same ERROR:
ERROR: Macro function %SCAN has too many arguments.
Passing comma-delimited value as a parameter to a SAS macro
Try submitting the following code that passes your macro variable value to a SAS macro as a parameter:
You will get another version of the SAS log ERROR:
ERROR: All positional parameters must precede keyword parameters.
NOTE: Line generated by the macro variable 'MYLIST'.
1 type, origin
----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
In this case, macro %subset gets as confused as the %scan function above because your macro variable will get resolved during macro compilation, and SAS macro processor will see the macro invocation as:
%subset(dsname=SASHELP.CARS, varlist=make, model, type, origin)
treating each comma as a parameter separator.
All this confusion happens because SAS functions’ arguments and SAS macros’ parameters use commas as their separators, while resolved macro variables introduce their own values’ comma delimiters into the functions/macros constructs’ picture, thus wreaking havoc on your SAS program.
It’s time for a vacation
But don’t panic! To fight that chaos, you need to take a vacation. Not a stay-home, do-nothing vacation, but some serious vacation, with faraway destination and travel arrangements. While real vacation is preferable, an imaginary one would do it too. I mean to start fighting the mess with comma-separated values, pick your destination, book your hotel and flight, and start packing your stuff.
Do you have a “vacation items list”? In my family, we have an individual vacation list for every family member. How many items do you usually take with you? Ten, twenty, a hundred?
Regardless, you don’t show up at the airport checkpoint with a pile of your vacation items. That would’ve been too messy. I don’t think you would be even allowed boarding with an unpacked heap of your stuff. You come to an airport neatly rolling a single item that is called a suitcase. Well, I suppose that some of you may have two of them, but I can’t imagine more than that.
You only started your fantasy vacation, you haven’t even checked in to your flight, but you have already have a solution in your sight, a perfect combine-and-conquer solution for passing comma-delimited values. Even if you have not yet realized that it’s in your plain view.
Sas Macros Cheat Sheet Excel
Thinking inside the box
Forget about “thinking outside the box” metaphor. You can’t solve all your problems with a single strategy. Sometimes, you need to turn your thinking on its head to solve, or even to see the problem.
As for your airport check-in, instead of thinking outside the box, you thought “inside the box” and brought your many items “boxed” as a single item – a suitcase. A container, in a broader sense.
That is exactly how we are going to approach our comma-delimited lists problem. We are going to check them in to a macro or a macro function as a single, boxed item. Just like this:
Or like this:
Not surprisingly, SAS macro language provides a variety of these wonder boxes for many special occasions collectively known as macro quoting functions. Personally, I would prefer calling them “macro masking functions,” as they have nothing to do with “quoting” per se and have everything to do with masking various characters during macro compilation or macro processing. But that is what “macro quoting” means – masking, boxing, - similar to “quoting” a character string to make it a single entity.
Forget about the “thinking outside the box” metaphor. You can’t solve all your problems with a single strategy. Sometimes, you need to turn your thinking on its head to solve, or even to see the problem.Different macro quoting functions mask different special characters (+ - , / ; = etc.) and mnemonics (AND OR GT EQ etc.) so that the macro facility interprets them as text instead of as language symbols.
Here are all 7 SAS macro quoting functions, two of which work at macro compilation - %STR() and %NRSTR(), while other 5 work at macro execution - %QUOTE() and %NRQUOTE(), %BQUOTE() and %NRBQUOTE(), and %SUPERQ().
You may look up what symbols they mask and the timing they apply (macro compilation vs. macro execution) in this macro quoting functions summary. You may also want to look at the following cheat sheet: Deciding When to Use a Macro Quoting Function and Which Function to Use.
As general rule of thumb, use macro quoting functions at compilation time when you mask text constants - (make, model, type, origin); use macro quoting functions at execution time when you mask macro or macro variable references containing & or % - (&mylist).
NOTE: There are many other SAS macro functions that besides their main role also perform macro quoting, e.g. %QSCAN(), %QSUBSTR() and others; they all start with %Q.
Masking commas within a comma-delimited value passed as an argument or a parameter
It turns out that to mask (or to “box”) comma-separated values in a macro function or a SAS macro, any macro quoting function will work. In this case I would suggest using the simplest (and shortest) %STR(). %STR() applies during macro compilation and serves as a perfect “box” for our comma-delimited values to hide (mask) commas to receiving macro function or a macro does not confuse them with its own commas separating arguments / parameters.
With it we can re-write our above examples as:
%let firstvar = %scan(%str(make, model, type, origin), 1);
%put &=firstvar;
SAS log will produce exactly what we expected:
FIRSTVAR=make
Macro Sas Put
Similarly, we can call the above SAS macro as:
%subset(dsname=SASHELP.CARS, varlist=%str(make, model, type, origin) )
It will run without ERRORs and produce a print of the SASHELP.CARS data table with 4 columns specified by the varlist parameter value:
Masking commas within a macro variable value passed as an argument or parameter
When you assign a comma-delimited list as a value to a macro variable, we want to mask commas within the resolved value during execution. Any of the execution time macro quoting functions will mask comma.
Again, in case of multiple possibilities I would use the shortest one - %QUOTE().
Sas Macros Basics
With it we can re-write our above examples as:
But just keep in mind that the remaining 4 execution time macro quoting functions - %NRQUOTE(), %BQUOTE(), %NRBQUOTE() and %SUPERQ() - will work too.
NOTE: The syntax of the %SUPERQ() function is quite different from the rest of the pack. The %SUPERQ() macro function takes as its argument either a macro variable name without an ampersand or a macro text expression that yields a macro variable name without an ampersand.
Get it going
Sas Macros Cheat Sheet Printable
I realize that macro quoting is not a trivial matter. That is why I attempted to explain its concept on a very simple yet powerful use case. Hope you will expand on this to empower your SAS coding skills.
Sas Graphs Cheat Sheet