02-22-2016 08:16 AM
I have created a little macro that does the following action :
Add quote for each element of the list and add a delimiter. The delimiter to add is specified through &outdelim. I would like also allow the user to specify the delimiter default that is in the input list.
After testing I got a strange issue when I specify that the input delimiter &indelim is blank. I creates an error. How to communicate to my macro that the delimiter is blank ?
%macro splitw(varsplit=, indel=, outdel=, outvar=) /DES="split word "; %global outsplit; %put %scan(&varsplit.,1,&indel.); /* In this version we assume that the delimitor is blank for COUNTW()*/ %let cw=%sysfunc(countw(&varsplit)); %let st=; %do i=1 %to %eval(&cw.-1); %let s&i= %sysfunc(quote(%scan(&varsplit.,&i.,&indel.))) &outdel.; %let st= &st. &&s&i. ; %end; %let s&cw= %sysfunc(quote(%scan(&varsplit.,&cw,&indel.))); %let &outvar=&st.&&s&cw.; %put &outsplit; %mend splitw; %splitw(varsplit=(ALBPHA BETA),indel=%str( ),outdel=%str(,),outvar=outsplit);
Any clue ?
02-22-2016 08:27 AM
02-22-2016 12:25 PM
Yes, the macro language is for SAS code generation, not data manipulation. But much of SAS code is lists: lists of variables, lists of values, lists of datasets, etc. As such, it is often useful to be able to manipulate those lists easily (adding/removing delimiters or quote marks), without needing to introduce a DATA step or PROC step.
So given a space-delimited list of variables (as SAS mostly wants) I can change to a comma delimited list of variables (as SQL wants) with just:
proc sql; select %seplist(&MyVarList,delim=%str(,)) from ...
Or given a list of values I can generate a quoted list of values for an IN operator with just:
data want; set have; if MyVar IN ( %seplist(&MyValueList,nest=QQ) ) ;
Understanding the generation of lists, and iteration over lists, and manipulation of lists is a critical skill to anyone learning the macro language.
02-22-2016 01:13 PM
Let me clarify then. What are lists, they are a series of data elements separated by delimiters. That descriptor in itself is enough to promote use of datasets, which are designed to handle data. This datastep programming has functions, strcutures and such like to deal with data, very few of which are to be found in macro programming. For instance all numeric data types, lists (or arrays as they could also be called). So lets take your first example. If I want to find all values from a given list, I would do it like this:
data list; list=1453; output; list=7987; output; run; proc sql; create table WANT as select * from HAVE where CODE in (select LIST from LIST); quit;
You will note its a straight forward subquery, nothing is hidden, you don't need to look at log output to see what code is generated, it is plain code and anyone who knows SAS should understand the above. One of the other benefits to this method is if you have to further processing. Say for instance, I find that all list elements over 2000 actually need to have 500 removed from them. Just imagine the mess that macro code would become converting to numeric each element, checking, %eval()'ing etc. Whereas the simple code I provide above would just require one tiny update:
proc sql; create table WANT as select * from HAVE where CODE in (select case when LIST > 2000 then LIST-500 else LIST end from LIST); quit;
That is the power of Base SAS, the ability to manipulate data quicky and efficiently, and allow complicated transformations across various structures.
In your second example, the datastep, you could of course use merging, which is another big power of Base SAS, or if you really want to have a list, then a simple symput from datastep can control that at the time the macro variable is needed.
Now nobody can say don't use macro, in much the same way as not to use Excel for data, as there is some really good functionality to be found within macro language, and code generation in general via many methods. However, that being said there does seem to be a consistent fallback position within SAS programming, in that "I can't seem to figure this out, lets jam it into macro", which creates a lot of unecessary and difficult to read code, which very often doesn't get documented or follow any form of development process. Take for instance the macro library, anyone who has started an new role will know what I am talking about. That area of code, built up of decades, very little documentation, held together by good will and more ampersands and percentage symbols than is proper. So often do I hear users talking about how they don't understand what these things do, or that they don't have XYZ functionality, or it doesn't do what is expected. It is ingrained that things have to be macro, personally my first question is always why, to which most of the time there is no answer, or the answer is because I couldn't do it in Base SAS - when at the end of the day, there is nothing which cannot be done in Base SAS, macro at the end of the day only generates Base SAS code.
So thats my standpoint in a nutshell, I prefer code to be as simple and visible as possible, even down to code formatting, anything to make the code simpler and easier to look at. So in terms of this:
"Understanding the generation of lists, and iteration over lists, and manipulation of lists is a critical skill to anyone learning the macro language."
To my mind learning Base SAS thoroughly, and be able to think about your data, how changing strucutures, and thinking about it in different ways, can lead to simpler solutions which are more robust and easier for end users to implement and manipulate.
End of opinionated long post, apologies original poster for sidetracking your thread.
02-22-2016 02:07 PM
Without belaboring the discussion too much, I think I agree with some of your main points.
I definitely agree that it is important for people to learn the SAS language well before they try learning the macro language (or any code generation techniques). When people make the jump to macro too soften, we often see macro %IF statements that should be data step IF statements, or %DO loops that could be replaced by BY statements. Agreed.
That said, after learning the SAS language, it's reasonable for developers to consider whether the benefits of learning the macro language outweigh the costs of the complexity inherent in code generation. And part of being a successful macro developer is knowing when a macro solution is appropriate, just as part of being a successful base SAS programmer is knowing when a data step/ SQL / hash / format solution is appropriate for a table lookup. I woudn't blame the macro language itself for the existence of poorly written or poorly documented macros, any more than I would blame the data step language for poorly written / poorly documented DATA steps.
I certainly don't object when people answer with "You don't need a macro for that... " Indeed I often respond to questions with "you don't need a fancy DATA step for that if you just normalize your data..... "
But for those who learn the macro language (or other code generating / information hiding techniques), often the resulting code is not only clearer and simpler to look at, it is modular, reusable, adaptable, testable, maintainable, etc etc. As the OP posted a pure-macro question, I assume the OP has decided that it is worth learning the macro language, and is looking for help in learning the macro language. I wouldn't want to see people discourage such a decision.
02-23-2016 09:08 AM
For some reason my initial reply with the reference for %seplist never posted yesterday, sorry.
%Seplist is an excellent function-style utitlity macro written by Richard DeVenezia. It's avaiable at:
It lets you do things like:
1 %let list=Alpha Beta Gamma;
3 %put %seplist(&list,dlm=%str(,));
4 %put %seplist(&list,dlm=%str(,),nest=QQ);
5 %put %seplist(&list,prefix=mylib.);
mylib.Alpha mylib.Beta mylib.Gamma
I suggest you review that macro definition, as it illustrates many good approaches to macro design, which you may want to incorporate into your own design (or simply use his).
02-22-2016 08:27 AM
Sorry, again why with the macro code? What do you achieve. If you have a list of values, put them in a dataset. Base SAS is designed to work with and manipulate data, macro language is designed to generate text. If at some reason you need to have a delimited list of data elements, then just call the base SAS SQL function on the dataset:
data parameters; param="abc"; output; param="def"; output; run; /* swap params over */ data parameters; set parameters; if substr(param,2,1)="b" then param="ert"; run; /* call list */ proc sql; select PARAM into :list separated by "','" from PARAMETERS; quit;
02-22-2016 08:34 AM - edited 02-22-2016 02:16 PM
ERROR: Expected close parenthesis after macro function invocation not found.
Is coming from the unmatched/unquoted parenthesis produced by %SCAN of the
You don't need the parenthesis or you need to remove them or include as delimiters.
Also you will probably want to use QSCAN which will also fix the current error but will not deal with the parenthesis.
I think once you get it working you will also need to consider other macro design issues and if you really want %GLOBAL.
02-22-2016 05:10 PM
Thanks Data_null__ !
Indeed, there are still little design issue as well here and there, for instance remove %sysfunc(quote( )) which I do not need.