Chapter 7 Macro Expressions and Macro Programming Statements
Constructing Macro Expressions
Understanding Arithmetic Expressions
Understanding Logical Expressions
Understanding the IN Operator As Used in Macro Language Statements
Conditional Processing with the Macro Language
Iterative Processing with the Macro Language
Writing Iterative %DO Loops in the Macro Language
Conditional Iteration with %DO %UNTIL
Conditional Iteration with %DO %WHILE
This chapter presents information about macro expressions and macro language statements, and it shows you how to accomplish several programming techniques with them. This chapter also shows you how to construct expressions and use macro language statements to conditionally process SAS steps, branch to different sections of a macro program, and perform iterative processing. The examples in this chapter use several of the functions described in Chapter 6. Chapter 8 continues the discussion of macro programming techniques with the topic of applying quoting functions to mask special characters and mnemonic operators.
Macro language statements communicate your instructions to the macro processor. With macro language statements, you can write macro programs that conditionally or repetitively execute sections of code. Many macro language statements have a SAS language counterpart. The syntax and function of similarly named statements are usually the same or very similar.
Remember, however, that macro language statements build SAS programs and are processed before the SAS programs they build. Macro language statements are not part of the DATA step programming language. They operate in a different context. They write SAS programs.
Macro language statements can be grouped into two types:
• statements that can be used either in open code or inside a macro program
• statements that can be used only inside a macro program
Tables 7.1 and 7.2 list most of the macro language statements. Some are shown by example in this chapter. Detailed reference information on these statements is in SAS Macro Language: Reference.
Table 7.3 lists the macro language statements that can be used to display windows and supply values to macro variables during macro execution, including prompting users for values. Discussion of this material is beyond the scope of this book. For detailed information on writing macro code that includes these statements, see SAS Macro Language: Reference.
As an aid in remembering the type of a macro language statement, observe that the statements in Table 7.1 work on macro variables or act as definition type statements. These statements can be used either in open code or inside a macro program.
On the other hand, most of the macro language statements in Table 7.2 are active programming statements that control processing and work in conjunction with other statements. These statements can be used only inside a macro program.
Table 7.1 Macro language statements that can be used either in open code or inside a macro program
|
Statement |
Action |
|
%* comment; |
Add descriptive text to your macro code. |
|
%COPY |
Copy specified items from a SAS stored compiled macro library catalog. |
|
%GLOBAL |
Create macro variables that are stored in the global symbol table and that will be available throughout the SAS session. |
|
%LET |
Create a macro variable and/or assign it a value. |
|
%MACRO |
Begin the definition of a macro program. |
|
%MEND |
End the definition of a macro program. |
|
%PUT |
Write text or macro variable values to the SAS log. |
|
%SYMDEL |
Delete the specified macro variable(s) from the global symbol table. |
|
%SYSCALL |
Invoke a SAS or user-written call routine using macro variables as the arguments (see also the %SYSFUNC macro function). |
|
%SYSEXEC |
Execute operating system commands immediately and return the success or failure status of the command to automatic macro variable SYSRC. |
|
%SYSLPUT |
Create a new macro variable or modify the value of an existing macro variable on a remote host or server. Used with SAS/CONNECT. |
|
%SYSRPUT |
Assign the value of a macro variable on a remote host to a macro variable on the local host. Used with SAS/CONNECT. |
Table 7.2 lists the macro language statements that can be used only inside a macro program.
Table 7.2 Macro language statements that can be used only inside a macro program
|
Statement |
Action |
|
%ABORT |
Stop the macro program that is executing along with the current DATA step, SAS job, or SAS session. |
|
%DO |
Signal the beginning of a %DO group; the statements that follow form a block of code that is terminated with a %END statement. |
|
%DO, iterative
|
Repetitively execute a section of macro code by using an index variable and the keywords %TO and %BY; the section of macro code is terminated with a %END statement. The %TO specification is required, and the %BY specification is optional. |
|
%DO %UNTIL |
Repetitively execute a section of macro code until the macro expression that follows the %UNTIL is true; the section of macro code is terminated with a %END statement. |
|
%DO %WHILE |
Repetitively execute a section of macro code while the macro expression that follows the %WHILE is true; the section of macro code is terminated with a %END statement. |
|
%END |
Terminate a %DO group. |
|
%GOTO |
Branch macro processing to the specified macro label within the macro program. |
|
%IF-%THEN / %ELSE |
Conditionally process the section of macro code that follows the %THEN when the result of the macro expression that follows %IF is true. When the macro expression that follows %IF is false, the section of macro code that follows the %ELSE is executed. |
|
%label: |
Identify a section of macro code; typically used as the destination of a %GOTO statement. |
|
%LOCAL |
Create macro variables that are available only to the macro program in which the %LOCAL statement was issued. |
|
%RETURN |
Cause normal termination of the currently executing macro program. |
Table 7.3 lists the macro language statements that can be used to display windows and supply values to macro variables during macro execution. These statements can be included in open code and inside macro programs.
Table 7.3 Macro language statements that can be used to display windows and prompt users for input
|
Statement |
Action |
|
%DISPLAY |
Display a macro window. |
|
%INPUT |
Supply values to macro variables during macro execution. |
|
%WINDOW |
Define a customized window. |
Previous chapters have shown examples of text expressions. A text expression is any combination of text, macro variables, macro functions, or macro program calls. This section describes two more types of macro expressions: arithmetic and logical.
Arithmetic and logical macro expressions are comprised of operators and operands that the macro processor evaluates to produce a result. Arithmetic expressions use arithmetic operators such as plus signs and minus signs. Logical expressions use logical operators such as greater than signs and equal signs.
Arithmetic and logical expressions in the macro language are constructed similarly to expressions in the SAS programming language. Both macro expressions and SAS language expressions use the same arithmetic and logical operators. The exceptions to this are that the MIN, MAX, and concatenation (||) operators are not available in the macro language. The IN operator requires that you set the MINOPERATOR option. The same precedence rules also apply. Parentheses act to group expressions and control the order of evaluation of expressions.
The section on macro evaluation functions in Chapter 6 presents several examples of arithmetic expressions when using %EVAL and %SYSEVALF. Subsequent sections in this chapter show examples that require arithmetic and logical expressions.
Table 7.4 lists the operators in order of precedence of evaluation of arithmetic and logical operators. The symbols for the NOT and NE operators depend on your computer system. Do not place percent signs in front of the mnemonic operators.
Table 7.4 Arithmetic and logical operators and their precedence in the macro language
|
Operator |
Mnemonic |
Action |
Precedence Rating |
|
** |
|
exponentiation |
1 |
|
+ - |
|
positive prefix negative prefix |
2 2 |
|
¬ ^ ~ |
NOT |
logical not* |
3 |
|
* / |
|
multiplication division |
4 4 |
|
+ - |
|
addition subtraction |
5 5 |
|
< <= = # ¬= ~= ^= > >= |
LT LE EQ IN NE GT GE |
less than less than or equal to equal equal to one of a list not equal* greater than greater than or equal to |
6 6 6 6 6 6 6 |
|
& |
AND |
logical and |
7 |
|
| |
OR |
logical or |
8 |
* The symbol to use depends on your keyboard.
Since the macro language is a text-based language, working with numbers is the exception. Therefore, special considerations are needed when you write expressions that use numbers and you want to use them in calculations.
The macro evaluation functions described in Chapter 6, %EVAL and %SYSEVALF, temporarily convert their arguments to numbers in order to resolve arithmetic expressions.
Several macro language statements and functions require numeric or logical expressions. These elements automatically invoke the %EVAL function to convert the expressions from text.
The macro functions that automatically invoke %EVAL around the expressions supplied to them are
• %SUBSTR and %QSUBSTR
• %SCAN and %QSCAN
The macro language statements that automatically invoke %EVAL around the expressions supplied to them are
• %DO
• %DO %UNTIL
• %DO %WHILE
• %IF
Therefore, when you use these functions and statements, explicitly coding the %EVAL function around the macro arithmetic expression is redundant.
Refer to the section in Chapter 6 on macro evaluation functions for examples of arithmetic macro expressions. Many examples in this chapter include arithmetic expressions as well.
A logical expression in the macro language compares two macro expressions. These macro expressions consist of text, macro variables, macro functions, arithmetic expressions, and other logical expressions. If the comparison is true, the result is a value of one (1). If the comparison is false, the result is zero (0). Expressions that resolve to integers other than zero (0) are also considered true. Expressions that resolve to zero (0) are false. The comparison operators in Table 7.4 construct logical expressions in the macro language.
As the macro processor resolves a macro expression, it places a %EVAL around each of the operands in the expression to temporarily convert the operands to integers. If an operand cannot be an integer, the macro facility then treats all operands in the expression as text. Comparisons are then based on the sort sequence of characters in the host operating system.
When you want numbers with decimal points to be compared as numbers and not compared as text, place the %SYSEVALF function around the logical expression. The %SYSEVALF function with the BOOLEAN conversion type acts like a logical expression because it yields a true-false result of one (1) or zero (0). Logical expressions are used in conditional processing. Examples of logical expressions and conditional processing are provided in the next section.
As you write your macro programs, you will have situations where you want to execute a section of code when a macro variable can have any one of the values in a set of values. Looking for one of the values involves writing the multiple conditions and connecting them with the OR operator:
%if &month=JANUARY or &month=APRIL or &month=AUGUST or
&month=DECEMBER %then %do;
… statements to execute when one of the conditions is true…
%end;
You can simplify the %IF statement if you use the IN operator as shown in the following statement. The list of acceptable values follow the IN operator.
%if &month in JANUARY APRIL AUGUST DECEMBER %then %do;
… statements to execute when one of the conditions is true…
%end;
The way you specify your list of values depends on the value of the SAS option MINDELIMITER. This can be set either with the OPTIONS statement or as an option in the %MACRO statement when you define your macro program. A value specified for MINDELIMITER on the %MACRO statement overrides the value of the MINDELIMITER= SAS option for the duration of execution of the macro program. The default value for MINDELIMITER is a blank, and this default value is used in the %IF statement above.
An example of specifying a %MACRO statement with the MINDELIMITER= option follows where the delimiter is a comma (,). The delimiter must be a single character enclosed in single quotation marks.
%macro lists(author) / mindelimiter=',';
You will need to set the SAS system option MINOPERATOR. The SAS system option MINOPERATOR | NOMINOPERATOR controls whether the SAS Macro Facility recognizes the word “IN” (case insensitive) and special symbol # as infix operators when it evaluates logical or integer expressions. The default setting is NOMINOPERATOR.
You can set MINOPERATOR | NOMINOPERATOR with either an OPTIONS statement or by adding the option to the %MACRO statement that defines the macro program where you need to evaluate an IN expression. The setting that you specify on the %MACRO statement overrides the system option setting for the duration of execution of the macro program.
A basic feature of any programming language is conditional execution of code. SAS macro language uses %IF-%THEN/%ELSE statements to control execution of sections of code. The sections of code that can be selected include macro language statements or text.
Remember that text in the macro facility can be SAS language statements like DATA steps and PROC steps. Thus, within your macro programs, based on evaluation of conditions you set, you can direct the macro processor to submit specific SAS statements for execution. With this capability, one macro program can contain many SAS language statements and steps, and the macro program can be used repeatedly to manage various processing tasks.
The syntax of the %IF statement is
%IF expression %THEN action;
<%ELSE elseaction;>
Multiple %ELSE statements can be specified to test for multiple conditions. The expression that you write is usually a logical expression. The macro processor invokes the %EVAL function around the expression and resolves the expression to true or false. When the evaluation of the expression is true, action is executed. When the evaluation of the expression is false and a %ELSE statement is specified, elseaction is executed.
Example 7.1: Using Logical Expressions
This example illustrates evaluation of logical expressions. Macro program COMP2VARS has two parameters. Four different types of logical expression evaluations that compare the two parameters are made with each call to COMP2VARS. Example 7.1 calls macro program COMP2VARS three times.
Note that the sort sequence of your operating system determines the outcome. These examples were run under Windows where in ASCII a lowercase letter comes before an uppercase letter; in EBCDIC, uppercase letters sort before lowercase letters.
%macro comp2vars(value1,value2);
%put COMPARISION 1:;
%if &value1 ne &value2 %then
%put &value1 is not equal to &value2..;
%else %put &value1 equals &value2..;
%put COMPARISION 2:;
%if &value1 > &value2 %then
%put &value1 is greater than &value2..;
%else %if &value1 < &value2 %then
%put &value1 is less than &value2..;
%else %put &value1 equals &value2..;
%put COMPARISION 3:;
%let result=%eval(&value1 > &value2);
%if &result=1 %then
%put EVAL result of &value1 > &value2 is TRUE.;
%else %put EVAL result of &value1 > &value2 is FALSE.;
%put COMPARISION 4:;
%let result=%sysevalf(&value1 > &value2);
%if &result=1 %then
%put SYSEVALF result of &value1 > &value2 is TRUE.;
%else %put SYSEVALF result of &value1 > &value2 is FALSE.;
%mend comp2vars;
*----First call to COMP2VARS;
%comp2vars(3,4)
*----Second call to COMP2VARS;
%comp2vars(3.0,3)
*----Third call to COMP2VARS;
%comp2vars(X,x)
The SAS log for % COMP2VARS (3,4) follows.
63 %comp2vars(3,4)
COMPARISON 1:
3 is not equal to 4.
COMPARISON 2:
3 is less than 4.
COMPARISON 3:
EVAL result of 3 > 4 is FALSE.
COMPARISON 4:
SYSEVALF result of 3 > 4 is FALSE.
The SAS log for % COMP2VARS (3.0,3) follows.
65 %comp2vars(3.0,3)
COMPARISON 1:
3.0 is not equal to 3.
COMPARISON 2:
3.0 is greater than 3.
COMPARISON 3:
EVAL result of 3.0 > 3 is TRUE.
COMPARISON 4:
SYSEVALF result of 3.0 > 3 is FALSE.
The SAS log for % COMP2VARS (X,x) follows.
67 %comp2vars(X,x)
COMPARISON 1:
X is not equal to x.
COMPARISON 2:
X is less than x.
COMPARISON 3:
EVAL result of X > x is FALSE.
COMPARISON 4:
SYSEVALF result of X > x is FALSE.
Example 7.2: Using Macro Language to Select SAS Steps for Processing
Example 7.2 shows how you can instruct the macro processor to select certain SAS steps. Macro program REPORTS contains code for two types of reports: a summary report and a detail report. The first parameter, REPTYPE, determines which report to produce.
The expected values for REPTYPE are either SUMMARY or DETAIL. The second parameter, REPMONTH, is to be specified as the numeric value of the month for which to produce the report.
When REPTYPE is specified as SUMMARY, the first PROC TABULATE step executes. If REPMONTH is equal to the last month of a quarter (March, June, September, or December), then the second PROC TABULATE step executes.
When REPTYPE is specified as DETAIL, the PROC TABULATE steps are skipped and only the PROC PRINT step in the %ELSE section executes.
This example calls macro program REPORTS twice. The first call to REPORTS requests a summary report for September. Both PROC TABULATE steps execute since September is the last month in the third quarter.
The second call to REPORTS requests a detail report for October. Macro program REPORTS executes a PROC PRINT step that lists the detailed information for October.
%macro reports(reptype,repmonth);
%let lblmonth=
%sysfunc(mdy(&repmonth,1,%substr(&sysdate,6,2)),monname.);
%*----Begin summary report section;
%if %upcase(&reptype)=SUMMARY %then %do;
%*----Do summary report for report month;
proc tabulate data=books.ytdsales;
title “Sales for &lblmonth”;
where month(datesold)=&repmonth;
class section;
var listprice saleprice;
tables section,
(listprice saleprice)*(n*f=6. sum*f=dollar12.2);
run;
%*----If end of quarter, also do summary report for qtr;
%if &repmonth=3 or &repmonth=6 or &repmonth=9 or
&repmonth=12 %then %do;
%let qtrstart=%eval(&repmonth-2);
%let strtmo=
%sysfunc(mdy(&qtrstart,1,%substr(&sysdate,6,2)),monname.);
proc tabulate data=books.ytdsales;
title “Sales for Quarter from &strtmo to &lblmonth”;
where &qtrstart le month(datesold) le &repmonth;
class section;
var listprice saleprice;
tables section,
(listprice saleprice)*(n*f=6. sum*f=dollar12.2);
run;
%end;
%end;
%*----End summary report section;
%*----Begin detail report section;
%else %if %upcase(&reptype)=DETAIL %then %do;
%*----Do detail report for month;
proc print data=books.ytdsales;
where month(datesold)=&repmonth;
var booktitle cost listprice saleprice;
sum cost listprice saleprice;
run;
%end;
%*----End detail report section;
%mend reports;
*----First call to REPORTS does a Summary report for September;
%reports(Summary,9)
*----Second call to REPORTS does a Detail report for October;
%reports(Detail,10)
The first call to REPORTS specifies summary reports for September. Macro program REPORTS submits the following code, which is shown after resolution of the macro variables.
proc tabulate data=books.ytdsales;
title “Sales for September”;
where month(datesold)=9;
class section;
var listprice saleprice;
tables section,(listpric saleprice)*(n*f=6. sum*f=dollar12.2);
run;
proc tabulate data=books.ytdsales;
title “Sales for Quarter from July to September”;
where 7 le month(datesold) le 9;
class section;
var listprice salepric;
tables section,(listprice saleprice)*(n*f=6. sum*f=dollar12.2);
run;
The second call to REPORTS specifies a detail report for October. Macro program REPORTS submits the following code, which is shown after resolution of the macro variables.
proc print data=books.ytdsales;
where month(datesold)=10;
var title cost listprice saleprice;
sum cost listprice saleprice;
run;
Example 7.3: Using %IF-%THEN/%ELSE Statements to Modify and Select Statements within a Step
Example 7.3 shows how %IF-%THEN/%ELSE statements can select the statements within a step to submit for processing. The previous example (Example 7.2) selected different steps, but did not select different statements within the step.
Macro program PUBLISHERREPORT in Example 7.3 constructs a PROC REPORT step that summarizes information about publishers. It has one parameter REPTYPE that can take one of three values: BASIC, DETAIL, and QUARTER. These values each specify a different report by requesting display and computation of different columns in the PROC REPORT step.
All three reports list the values of data set variables PUBLISHER and SALEPRICE. Following is a description of the actions that the macro program takes for each of the three possible parameter values.
REPTYPE=BASIC: Compute and display PROFIT for each value of PUBLISHER and overall. Do not display COST, but use it in the COMPUTE block to compute the value of PROFIT. Specify option NOPRINT on the DEFINE statement for COST.
REPTYPE=DETAIL: Compute and display PROFIT for each value of PUBLISHER and overall. Compute the N statistic and label this column “Number of Titles Sold.” Display COST and use it in the COMPUTE block to calculate the value of PROFIT.
REPTYPE=QUARTER: Compute and display PROFIT for each value of PUBLISHER and overall. Display COST and use it in the COMPUTE block to compute the value of PROFIT. Define DATESOLD as an ACROSS variable and format the values of DATESOLD as calendar quarters. Define SALEPRICE2 as an alias for SALEPRICE and nest SALEPRICE2 underneath DATESOLD. Underneath each of the four values displayed for DATESOLD, display the sum of SALEPRICE2. These columns are the totals of SALEPRICE for each quarter.
A FOOTNOTE statement displays information about the processing. It prints the name of the macro program using automatic macro variable &SYSMACRONAME, and it lists the value of parameter REPTYPE.
Example 7.3 calls macro program PUBLISHERREPORT three times, once for each of the three valid values of REPTYPE. The first %LET statement in the macro program converts the value of REPTYPE to uppercase, making coding of the %IF statement easier so that only one possible value has to be examined.
The macro language statements that select SAS language code are in bold.
%macro publisherreport(reptype);
%let reptype=%upcase(&reptype);
title “Publisher Report”;
footnote
“Macro Program: &sysmacroname Report Type: &reptype”;
proc report data=books.ytdsales nowd;
column publisher saleprice cost profit
%if &reptype=DETAIL %then %do;
n
%end;
%else %if &reptype=QUARTER %then %do;
datesold,(saleprice=saleprice2)
%end;
define publisher / group;
define saleprice / analysis sum format=dollar11.2;
define cost / analysis sum format=dollar11.2
%if &reptype=BASIC %then %do;
noprint
%end;
;
define profit / computed format=dollar11.2 'Profit';
%if &reptype=DETAIL %then %do;
define n / 'Number of Titles Sold';
%end;
%else %if &reptype=QUARTER %then %do;
define saleprice2 / 'Quarter Sale Price Total';
define datesold / across ' ' format=qtr.;
%end;
compute profit;
profit=saleprice.sum-cost.sum;
endcomp;
rbreak after / summarize;
compute after;
publisher='Total for All Publishers';
endcomp;
run;
%mend publisherreport;
%* First call to PUBLISHERREPORT, do BASIC report;
%publisherreport(basic)
%* Second call to PUBLISHERREPORT, do DETAIL report;
%publisherreport(detail)
%* Third call to PUBLISHERREPORT, do QUARTER report;
%publisherreport(quarter)
First call to PUBLISHERREPORT: The PROC REPORT step that PUBLISHERREPORT submits when REPTYPE=BASIC follows. The features unique to the version specified by REPTYPE=BASIC are in bold.
title “Publisher Report”;
footnote “Macro Program: PUBLISHERREPORT Report Type: BASIC”;
proc report data=books.ytdsales nowd;
column publisher saleprice cost profit;
define publisher / group;
define saleprice / analysis sum format=dollar11.2;
define cost / analysis sum format=dollar11.2 noprint;
define profit / computed format=dollar11.2 'Profit';
compute profit;
profit=saleprice.sum-cost.sum;
endcomp;
rbreak after / summarize;
compute after;
publisher='Total for All Publishers';
endcomp;
run;
Second call to PUBLISHERREPORT: The PROC REPORT step that PUBLISHERREPORT submits when REPTYPE=DETAIL follows. The features unique to the version specified by REPTYPE=DETAIL are in bold.
title “Publisher Report”;
footnote
“Macro Program: PUBLISHERREPORT Report Type: DETAIL”;
proc report data=books.ytdsales nowd;
column publisher saleprice cost profit n;
define publisher / group;
define saleprice / analysis sum format=dollar11.2;
define cost / analysis sum format=dollar11.2;
define profit / computed format=dollar11.2 'Profit';
define n / 'Number of Titles Sold';
compute profit;
profit=saleprice.sum-cost.sum;
endcomp;
rbreak after / summarize;
compute after;
publisher='Total for All Publishers';
endcomp;
run;
Third call to PUBLISHERREPORT: The PROC REPORT step that PUBLISHERREPORT submits when REPTYPE=QUARTER follows. The features unique to the version specified by REPTYPE=QUARTER are in bold.
title “Publisher Report”;
footnote “Macro Program: PUBLISHERREPORT Report Type: QUARTER”;
proc report data=books.ytdsales nowd;
column publisher saleprice cost profit
datesold,(saleprice=saleprice2);
define publisher / group;
define saleprice / analysis sum format=dollar11.2;
define cost / analysis sum format=dollar11.2 ;
define profit / computed format=dollar11.2 'Profit';
define saleprice2 / 'Quarter Sale Price Total';
define datesold / across ' ' format=qtr.;
compute profit;
profit=saleprice.sum-cost.sum;
endcomp;
rbreak after / summarize;
compute after;
publisher='Total for All Publishers';
endcomp;
run;
Example 7.4: Writing %IF-%THEN/%ELSE Statements That Use the IN Operator
Macro program VENDORTITLES in Example 7.4 defines a TITLE2 statement based on the value of the parameter PUBLISHER. Assume multiple publishers use the same vendor. Rather than writing multiple logical expressions on the %IF statement and connecting them with the OR operator, this example uses the IN operator. The multiple publishers mapping to one vendor are listed after the IN operator, and names of the publishers are separated by exclamation points.
The default delimiter is a space. Since some values for PUBLISHER contain spaces, a delimiter other than a space must be used to separate publisher names.
To find out the delimiter setting of your SAS session, check the value of the MINDELIMITER SAS option. To use a different delimiter, such as the exclamation point, specify the new delimiter either as a SAS option or specify it on the MINDELIMITER= option of the %MACRO statement. The MINDELIMITER= option on the %MACRO statement overrides the current setting of the MINDELIMITER SAS option during execution of the macro program.
Note that the MINOPERATOR SAS option must be in effect as well when Example 7.4 is submitted. This option controls whether the SAS Macro Facility recognizes the word “IN” (case insensitive) and the special symbol # as infix operators when it evaluates logical or integer expressions. Example 7.4 starts with an OPTIONS statement that sets MINOPERATOR.
options minoperator;
%macro vendortitles(publisher) / mindelimiter='!';
title “Vendor-Publisher Report”;
%if &publisher in
AMZ Publishers!Eversons Books!IT Training Texts
%then %do;
title2 “Vendor for &publisher is Baker”;
%end;
%else %if &publisher in
Northern Associates Titles!Professional House Titles
%then %do;
title2 “Vendor for &publisher is Mediasuppliers”;
%end;
%else %do;
title2 “Vendor for &publisher is Basic Distributor”;
%end;
%mend vendortitles;
%vendortitles(AMZ Publishers)
%vendortitles(Mainst Media)
The first call to VENDORTITLES defines the following TITLE2 statement:
title2 “Vendor for AMZ Publishers is Baker”;
The second call to VENDORTITLES defines the following TITLE2 statement:
title2 “Vendor for Mainst Media Publishers is Basic Distributor”;
The iterative processing statements in the macro language instruct the macro processor to repetitively process sections of code. The macro language includes %DO loops, %DO %UNTIL loops, and %DO %WHILE loops. With iterative processing, you can instruct the macro processor to write many SAS language statements, DATA steps, and PROC steps. The three types of iterative processing statements are described below. These statements can be used only from within a macro program.
The iterative %DO macro language statement instructs the macro processor to execute a section of code repeatedly. The number of times the section executes is based on the value of an index variable. The index variable is a macro variable. You define the start value and stop value of this index variable. You can also control the increment of the steps between the start value and the stop value; by default, the increment value is one.
The syntax of an iterative %DO loop follows.
%DO macro-variable=start %TO stop <%BY increment>;
macro language statements and/or text
%END;
Do not put an ampersand in front of the index variable name in the %DO statement even though the index variable is a macro variable. However, any reference to it later within the loop requires an ampersand in front of the reference.
The start and stop values and the %BY values are integers or macro expressions that can be resolved to integers. If you want to increment the index macro variable by something other than 1, follow the stop value with the %BY keyword and the increment value. The increment value is either an integer or a macro expression that can be resolved to an integer.
Example 7.5: Building PROC Steps with Iterative %DO Loops
Example 7.5 uses the iterative %DO to generate several PROC TABULATE and PROC SGPLOT steps. Macro program MULTREP generates statistics and a bar chart for each year between the bounds on the %DO statement. In this example, PROC TABULATE and PROC SGPLOT are each executed three times: once for 2011, once for 2012, and once for 2013.
%macro multrep(startyear,stopyear);
%do yrvalue=&startyear %to &stopyear;
title “Sales Report for &yrvalue”;
proc tabulate data=sales.year&yrvalue;
class section;
var cost listprice saleprice;
tables section all='Total',
(cost listprice saleprice)*
(n*f=6. (min max sum)*f=dollar8.2);
run;
proc sgplot data= sales.year&yrvalue;
hbar section / response=saleprice stat=sum datalabel;
yaxis display=(nolabel noticks);
run;
%end;
%mend multrep;
*----Produce 3 sets of reports: one for 2011, one for 2012,
*----and one for 2013;
%multrep(2011,2013)
After the macro processor processes the macro language statements and resolves the macro variables references, the following SAS program is submitted.
title “Sales Report for 2011”;
proc tabulate data=sales.year2011;
class section;
var cost listprice saleprice;
tables section all='Total',(cost listprice saleprice)*
(n*f=6. (min max sum)*f=dollar8.2);
run;
proc sgplot data=sales.year2011;
hbar section / response=saleprice stat=sum datalabel;
yaxis display=(nolabel noticks);
run;
title “Sales Report for 2012”;
proc tabulate data=sales.year2012;
class section;
var cost listprice saleprice;
tables section all='Total',(cost listprice saleprice)*
(n*f=6. (min max sum)*f=dollar8.2);
run;
proc sgplot data=sales.year2012;
hbar section / response=saleprice stat=sum datalabel;
yaxis display=(nolabel noticks);
run;
title “Sales Report for 2013”;
proc tabulate data=sales.year2013;
class section;
var cost listprice saleprice;
tables section all='Total',(cost listprice saleprice)*
(n*f=6. (min max sum)*f=dollar8.2);
run;
proc sgplot data=sales.year2013;
hbar section / response=saleprice stat=sum datalabel;
yaxis display=(nolabel noticks);
run ;
Example 7.6: Building SAS Statements within a Step with Iterative %DO Loops
Iterative %DO statements can build SAS statements within a SAS DATA step or SAS PROC step. Macro program SUMYEARS in Example 7.6 concatenates several data sets in a DATA step. The first %DO loop constructs the names of the data sets that the DATA step concatenates.
Note that a semicolon is not placed after the reference to the data set within the first %DO loop. A semicolon placed after the data set reference would terminate the SET statement on the first iteration. On each subsequent iteration, a semicolon after the data set reference would make the data set reference look like a SAS statement, and this results in errors.
The second %DO loop creates the macro variable YEARSTRING that contains the values of all the processing years. Each iteration of the second %DO loop concatenates the current iteration’s value for YEARVALUE to the current value of YEARSTRING.
%macro sumyears(startyear,stopyear);
data manyyears;
set
%do yearvalue=&startyear %to &stopyear;
sales.year&yearvalue
%end;
;
run;
%let yearstring=;
%do yearvalue=&startyear %to &stopyear;
%let yearstring=&yearstring &yearvalue;
%end;
proc sgplot data=manyyears;
title “Charts Analyze Data for: &yearstring”;
hbar section / response=saleprice stat=sum datalabel;
yaxis display=(nolabel noticks);
run;
%mend sumyears;
*----Concatenate three data sets: one from 2011, one from;
*----2012, and one from 2013;
%sumyears(2011,2013)
The macro processor resolves the call to YEARLYCHARTS as follows.
data manyyears;
set sales.year2011 sales.year2012 sales.year2013;
run;
proc gchart data=manyyears;
title “Charts Analyze Data for: 2011 2012 2013”;
hbar section / sumvar=saleprice type=sum;
run;
quit;
With %DO %UNTIL, a section of code is executed until the condition on the %DO %UNTIL statement is true. The syntax of %DO %UNTIL is
%DO %UNTIL (expression);
macro language statements and/or text
%END;
The expression on the %DO %UNTIL statement is a macro expression that resolves to a true-false value. The macro processor evaluates the expression at the bottom of each iteration. Therefore, a %DO %UNTIL loop always executes at least once.
Example 7.7: Building SAS Steps with %DO %UNTIL Loops
Example 7.7 demonstrates the use of %DO %UNTIL. Macro program MOSALES computes statistics for each month in the list of values passed to the program. When a list of month values is not specified, MOSALES computes statistics for all observations in the analysis data set.
Example 7.7 defines macro program MOSALES with the PARMBUFF option. This %MACRO statement option is described at the end of Chapter 4. The PARMBUFF option allows you to specify a varying number of parameter values. The macro processor assigns the list of values to the automatic macro variable SYSPBUFF. Macro program MOSALES parses SYSPBUFF and submits a PROC MEANS step for each month value specified in the list of parameter values.
The %SCAN function selects each month value from SYSPBUFF. The macro variable LISTINDEX determines which item in the list of months the %SCAN function should select, and the program increments it by 1 at the bottom of the %DO %UNTIL loop. Observations are selected for processing with a WHERE statement.
When a list of parameter values is not specified, as in the second call to MOSALES, the macro program does an overall PROC MEANS step and it does not apply a WHERE statement to the step. This overall PROC MEANS is accomplished by taking advantage of the features of %DO
%UNTIL: a %DO %UNTIL loop executes at least once. When parameter values are not specified, the following occurs:
• The %SCAN function is not able to extract any text from SYSPBUFF so the result of the evaluation of the %DO %UNTIL condition is true.
• The value of REPMONTH is assigned a null value.
• The code within the %DO %UNTIL loop executes once.
• The first TITLE statement and the WHERE statement do not execute because REPMONTH is null.
Example 7.7 calls MOSALES twice. The first call to MOSALES submits three PROC MEANS steps: one for March, one for May, and one for October. The second call to MOSALES submits one PROC MEANS step, a summarization of all the observations in the data set.
%macro mosales / parmbuff;
%let listindex=1;
%do %until (%scan(&syspbuff,&listindex) eq );
%let repmonth=%scan(&syspbuff,&listindex);
proc means data=books.ytdsales n sum maxdec=0;
%if &repmonth ne %then %do;
title “Sales during month &repmonth”;
where month(datesold)=&repmonth;
%end;
%else %do;
title “Overall Sales”;
%end;
class section;
var saleprice;
run;
%let listindex=%eval(&listindex+1);
%end;
%mend;
*----First call to MOSALES: produce stats for March, May, and
*----October;
%mosales(3 5 10)
*----Second call to MOSALES: produce overall stats;
%mosales()
The first call to MOSALES requests statistics for March, May, and October. The macro processor generates the following SAS program.
proc means data=books.ytdsales n sum maxdec=0;
title “Sales during month 3”;
where month(datesold)=3;
class section;
var saleprice;
run;
proc means data=books.ytdsales n sum maxdec=0;
title “Sales during month 5”;
where month(datesold)=5;
class section;
var saleprice;
run;
proc means data=books.ytdsales n sum maxdec=0;
title “Sales during month 10”;
where month(datesold)=10;
class section;
var saleprice;
run;
The second call to MOSALES does not specify any months. Therefore, the %DO %UNTIL loop executes once, generates overall statistics, and selects the second TITLE statement. The SAS program that the macro processor creates from this call follows:
proc means data=books.ytdsales n sum maxdec=0;
title “Overall Sales”;
class section;
var saleprice;
run;
With %DO %WHILE, a section of code is executed while the condition on the %DO %WHILE statement is true. The syntax of %DO %WHILE is:
%DO %WHILE (expression);
macro language statements and/or text
%END;
The expression on the %DO %WHILE statement is a macro expression that resolves to a true-false value. The macro processor evaluates the expression at the top of the loop. Therefore, it is possible that a %DO %WHILE loop does not execute. This occurs when the condition starts out as false.
Example 7.8: Building SAS Steps with %DO %WHILE Loops
Example 7.8 shows an application of %DO %WHILE. Macro program MOYRSALES computes sales statistics for a month in specific years. It has two parameters: YEARLIST and REPMONTH.
The parameter YEARLIST is defined to be a list of years for which to compute sales statistics. The second parameter, REPMONTH, is the month within each year for which to compute the statistics. The program is written to expect only one value for REPMONTH, and it is assumed that it will be a number between one and twelve.
This example’s call to MOYRSALES requests statistics for May in 2011 and in 2013. The %DO %WHILE loop executes twice, once for each year. The %SCAN function selects each year from YEARLIST. The macro variable YEARIDX determines which item the %SCAN function should select, and the program increments YEARIDX by one at the bottom of the %DO %WHILE loop.
The %DO %WHILE loop does not execute a third time. On the third iteration, the %SCAN function does not find a third year. Therefore, the macro expression on the %DO %WHILE statement resolves to false, and this stops the loop.
%macro moyrsales(yearlist,repmonth);
%let yearidx=1;
%do %while (%scan(&yearlist,&yearidx) ne );
%let saleyear=%scan(&yearlist,&yearidx);
proc means data=sales.year&saleyear n sum maxdec=0;
title “Sales during &repmonth/&saleyear”;
where month(datesold)=&repmonth;
class section;
var saleprice;
run;
%let yearidx=%eval(&yearidx+1);
%end;
%mend moyrsales;
%moyrsales(2011 2013,5)
After resolution by the macro processor, the SAS code submitted for compilation and execution is as follows. A PROC MEANS step is created for each year.
proc means data=sales.year2011 n sum maxdec=0;
title “Sales during 5/2011”;
where month(datesold)=5;
class section;
var saleprice;
run;
proc means data=sales.year2013 n sum maxdec=0;
title “Sales during 5/2013”;
where month(datesold)=5;
class section;
var saleprice;
run;
;
Since the %DO %WHILE loop executes only while the condition on the statement is true, consider what happens if years are specified on the call to %MOYRSALES as follows:
%moyrsales(,5)
The %DO %WHILE loop in this situation does not execute because the condition on the %DO %WHILE statement is never true. No processing is done and no messages are written to the SAS log.
When you want to branch to a different section of a macro program, label the text and use a %GOTO statement. The %GOTO statement directs processing to that labeled text. Labeled text and the %GOTO statement are allowed only in macro programs. Macro language statements, macro expressions, and constant text can be labeled. Macro text is labeled as follows:
%label: macro-text
Place the label before the macro text that you want to identify. The label is any valid SAS name. Precede the label with a percent sign (%) and follow the label name with a colon (:). The colon tells SAS to treat %label as a statement label and not to invoke a macro program that is named %label.
The syntax of the %GOTO statement is
%GOTO label;
On the %GOTO statement, you can specify the label as text or as a macro expression that resolves to the label name. Do not put a percent sign in front of the label on the %GOTO statement. If you do specify a percent sign, the macro processor interprets that as a request to execute a macro program that has the name of your label.
Example 7.9: Using %GOTO to Branch in a Macro Program
Example 7.9 shows how labels and %GOTO statements can be used. Macro program DETAIL starts out by determining if the data set named by its first parameter, DSNAME, exists. If it does, it executes a PROC PRINT step listing the variables specified by the second parameter, VARLIST. When the step ends, the program branches to the label %FINISHED.
If the data set specified by DSNAME does not exist, the program skips over the PROC PRINT step and branches to the label %NODATASET. The program then writes a message to the SAS log, determines the libref of the data set specified by DSNAME, and executes a PROC DATASETS step that lists the data sets in the library specified by the data set’s libref. The output from PROC DATASETS might help in figuring out the problem in the value specified for DSNAME.
The third parameter, NUMOBS=, is a keyword parameter that is initialized to MAX. The value assigned to NUMOBS specifies the number of observations to list with PROC PRINT. The value MAX indicates that all observations in data set DSNAME will be printed.
This example calls DETAIL three times. The code that executes is described below.
%macro detail(dsname,varlist,numobs=max);
%* Does DSNAME exist?;
%let foundit=%sysfunc(exist(&dsname));
%if &foundit le 0 %then %goto nodataset;
title “PROC PRINT of &dsname”;
proc print data=&dsname(obs=&numobs);
var &varlist;
run;
%goto finished;
%nodataset:
%put ERROR: **** Data set &dsname not found. ****;
%put;
%* Find the data set libref. If it is not;
%* specified, assume a temporary data set;
%* and assign WORK to DSLIBREF;
%let period=%index(&dsname,.);
%if &period gt 0 %then
%let dslibref=%scan(&dsname,1,.);
%else %let dslibref=work;
proc datasets library=&dslibref details;
run;
quit;
%finished:
%mend detail;
*----First call to DETAIL, data set exists;
%detail(books.ytdsales,datesold booktitle saleprice,obs=5)
*----Second call to DETAIL, data set does not exist;
%detail(books.ytdsaless,datesold booktitle saleprice)
%*----Third call to DETAIL, look for data set in WORK library;
%detail(ytdsales,datesold booktitle saleprice)
First call to DETAIL: The first call to the macro program DETAIL executes a PROC PRINT of the data set since data set BOOKS.YTDSALES exists. The PROC PRINT step lists the variables specified in VARLIST, and it lists the first five observations in BOOKS.YTDSALES. After PROC PRINT completes processing, the program skips over the section labeled as %NODATASET and branches to the section labeled %FINISHED. The macro processor generates the following code:
title “PROC PRINT of books.ytdsales(obs=5)”;
proc print data=books.ytdsales;
var datesold booktitle saleprice;
run;
Second call to DETAIL: The data set name is misspelled in the second call to DETAIL. Assume a data set with this misspelled name does not exist in the library specified by BOOKS. The program skips the PROC PRINT section and executes the section labeled with %NODATASET. The macro processor writes an error message in red to the SAS log that data set BOOKS.YTDSALESS does not exist. The program determines that a permanent data set was specified for DSNAME so it executes a PROC DATASETS step on the library specified in DSNAME. The following PROC DATASETS code is submitted.
proc datasets library=books details;
run;
quit;
Third call to DETAIL: The value of DSNAME in the third call to DETAIL is YTDSALES. A libref for this data set is not specified, which implies that the data set to be processed is in the WORK directory. If YTDSALES exists in the WORK directory, then the PROC PRINT step executes. If YTDSALES does not exist in the WORK directory, the program skips over the PROC PRINT step and branches to the section labeled as %NODATASET. The statements that immediately follow the %NODATASET label examine the value of DSNAME and determine if it contains a libref. If it does not, the program assigns a libref of WORK to the value of DSLIBREF. It then executes the PROC DATASETS step and lists the SAS data files in the WORK directory.
For the third call, if YTDSALES exists in the WORK directory, the macro program submits the following code:
title “PROC PRINT of ytdsales”;
proc print data=ytdsales(obs=max);
var datesold booktitle saleprice;
run;
If YTDSALES does not exist in the WORK directory, the macro program submits the following code:
proc datasets library=work details;
run;
quit;