In my previous article, we developed a basic understanding of SAS Macros and SAS Macro variables. We also looked at how macros can be used to simplify repetitive tasks.
SAS macros provide us with the flexibility to use a piece of code multiple times by simply calling the macro. This flexibility can be exploited to reach next level of sophistication with use of conditional statements and loops using macro statements such as %IF, %DO. These macro statements should always be called inside the macro. If you have not read my previous article, I would strongly recommend that you do that before proceeding further.
As the name implies, conditional processing is used when we want to execute a piece of code based on the output of single or multiple conditions.
Here, ELSE part of the syntax is optional.
Both syntax work in similar manner – the only difference is, first one executes only one statement after THEN or ELSE, whereas the second syntax can execute multiple statements.
Conditional Macro Expression:
I am referring to the expression in the condition as Conditional macro expression. In some cases, it is similar to SAS expression:
- Arithmetic operators
- Logical Operators ( do not use %sign before AND or OR)
- Comparison operators
- It is also case sensitive
Dissimilarities compared to SAS Expression:
- Character operands are not quoted (Single or double)
- Ranges 20000 <= &Salary <=5000 do not work, we should write it as &Salary >= 20000 AND &Salary <=50000
- IN operator not requires parentheses.
Let’s say, we have transactional data for a company which deals in food business. Here manager requires details of all the sales on daily basis except on MONDAY. On Monday, he want a additional summary report for item wise total sales (People call it as MONDAY Report!)
Here we will first define a separate macro for Daily and Monday reports (you can combine these together also).
Now on daily basis it will call Macro “Daily” and check if today is MONDAY then it will run MACRO “Monday”. Similarly if we have multiple statements to execute after %IF-%Then then we should go for %IF-%THEN-%DO-%END.
Loops are used to create a dynamic program, which executes for a number of iterations, based on some conditions (called conditional iteration). These statements are also valid only inside a macro.
Key things about syntax: –
- Index variable should be a macro variable.
- Start, Stop and Increment Value can be any macro expression that resolves to integers.
- %BY statement is optional (default increment value is 1).
- %While and %Until work similar, basic difference between these two is %WHILE first evaluates the criteria then executes loop whereas %UNTIL first executes loop i.e. %UNTIL evaluate at least once.
Let’s say we have a series of SAS data sets YR1990 – YR2013 that contain business detail and now we want to calculate the average sales for each of these years.
If we will not use the SAS macro then we need to write PROC MEANS steps for each of the year, which is not a good idea.
Now let’s look at how we can use SAS Macros to do this job easily:
We can achieve this with %UNTIL and %WHILE.
Conditional statements and loops empower SAS Macros to perform conditional and iterative tasks. They appear similar to SAS conditional statements and loops and they work similar in some cases. The major difference between the two is %if-%then’ can ONLY be used inside the MACRO program (whether inside the DATA step, or outside) and ‘if-then’ is ONLY used inside the DATA step (whether MACRO or non-MACRO).
Have you used iterations and loops within macros to make your SAS codes more efficient? If yes, please feel free to share your experience through the comments below.