CHAPTER  7

Writing Flexible Code with the SAS Macro Facility

7.1    Macro Concepts

7.2    Substituting Text with Macro Variables

7.3    Concatenating Macro Variables with Other Text

7.4    Creating Modular Code with Macros

7.5    Adding Parameters to Macros

7.6    Writing Macros with Conditional Logic

7.7    Using %DO Loops in Macros

7.8    Writing Data-Driven Programs with CALL SYMPUTX

7.9    Writing Data-Driven Programs with PROC SQL

7.10  Debugging Macro Errors

7.1     Macro Concepts

The SAS macro facility allows you to assign a name to a block of text and then use that name in your program wherever you want to insert the text. The SAS macro facility is sometimes considered an advanced topic relevant only to experienced SAS users. However, even new SAS users would do well to know a little about using macros. Fortunately, the basic concepts are not difficult to understand. This chapter introduces the most commonly used features of the SAS macro language.

Because programs containing macros take longer to write and debug than standard SAS code, you generally won’t want macros in short programs that will be run only a few times. But used properly, macros can make the development and maintenance of production programs much easier. They do this in several ways. For example, with macros you can make one small change in your program and have SAS echo that change throughout your program. In addition, macros allow you to write a piece of code once and use it over and over, in the same program or in different programs. You can even store macros in a central location and share them between programs and between programmers. Also, you can make your programs data driven, letting SAS decide what to do based on actual data values.

The macro processor  When you submit a standard SAS program, SAS compiles and then immediately executes it. But when you write a macro, there is an additional step. Before SAS can compile and execute your program, SAS must pass your macro statements to the macro processor, which “resolves” your macros, generating standard SAS code. Because you are writing a program that writes a program, this is sometimes called meta-programming.

Macro variables versus macros  The value of a macro variable is a single text string. This text string could be a variable name, a numeral, or any text that you want substituted into your program. The names of  macro variables are prefixed with an ampersand (&).

A macro, on the other hand, is a larger piece of a program that may contain complex logic including complete DATA and PROC steps and macro statements such as %DO, %END, and %IF-%THEN/%ELSE. The names of macros are prefixed with a percent sign (%).

When SAS users talk about “macros” they sometimes mean macros, and sometimes they mean macro processing in general. Macro variables are usually called macro variables.

 

Local versus global  Macro variables can have two kinds of “scope”—either local or global. Generally, a macro variable is local if it is defined inside a macro. A macro variable is generally global if it is defined in “open code”, which is everything outside a macro. You can use a global macro variable anywhere in your program, but you can use a local macro variable only inside its own macro. If you keep this in mind as you write your programs, you will avoid two common errors: trying to use a local macro variable outside its macro and accidentally creating local and global macro variables with the same name. It is possible to force a local macro variable to be global and vice versa, but this is generally not necessary and not covered in this book.

Turning on the macro processor  Before you can use macros, you must have the MACRO system option turned on. This option is usually turned on by default, but may be turned off, especially on large shared servers, because SAS runs slightly faster when it doesn’t have to bother with checking for macros. If you are not sure whether the MACRO option is on, you can find out by submitting these statements:

PROC OPTIONS OPTION = MACRO;

RUN;

Check your SAS log. If you see the option MACRO, then the macro processor is turned on, and you can use it. If you see NOMACRO there, you need to specify the MACRO option at invocation or in a configuration file. Specifying this type of option is system dependent. For details about how to do this, see the SAS Documentation for your operating environment.

Avoiding macro errors  There’s no question about it, macros can make your head hurt. You can avoid the macro migraine by developing your program in a piecewise fashion. First, write your program in standard SAS code. Then, when it’s bug-free, convert it to macro logic by adding one feature at a time. This modular approach to programming is always a good idea, but it’s critical with macros.

Quoting problems  One common mistake people make is to use single quotes around text where double quotes are needed. The macro processor doesn’t resolve macros inside single quotation marks. To get around this, use double quotation marks whenever you refer to a macro or macro variable and you want SAS to resolve it. For example, below are two TITLE statements containing a macro variable named &Month. If the value of &Month is January, then the macro processor will substitute January in the title with the double quotation marks, but not the title with single quotation marks.

Original statement	Statement after resolution
TITLE 'Report for &Month';	TITLE 'Report for &Month';
TITLE "Report for &Month";	TITLE "Report for January";

 

7.2     Substituting Text with Macro Variables

Macro variables may be the most straightforward and easy-to-use part of the macro facility, yet if you master only this one feature of macro programming, you will have greatly increased your flexibility as a SAS programmer. Suppose that you have a SAS program that you run once a week. Each time you run it you have to edit the program so it will select data for the correct range of dates and print the correct dates in the title. This process is time-consuming and prone to errors. (What if you accidentally delete a semicolon?!) Instead, you can use a macro variable to insert the correct date. Then you can have another cup of coffee while someone else, someone who knows very little about SAS, runs this program for you.

When SAS encounters the name of a macro variable, the macro processor simply replaces the name with the value of that macro variable. That value is a character constant that you specify.

Creating a macro variable with %LET  The simplest way to assign a value to a macro variable is with the %LET statement. The general form of this statement is:

%LET macro-variable-name = value;

where macro-variable-name must be 32 characters or fewer in length; start with a letter or underscore; and contain only letters, numerals, and underscores. Value is the text to be substituted for the macro variable name, and can be longer than you are ever likely to need─over 65,000 characters long. The following statements each create a macro variable:

%LET Iterations = 10;

%LET Country = New Zealand;

Notice that unlike an ordinary assignment statement, value does not require quotation marks even when it contains characters. Except for blanks at the beginning and end, which are trimmed, everything between the equal sign and the semicolon becomes part of the value for that macro variable.

Using a macro variable  To use a macro variable you simply add the ampersand prefix (&) and stick the macro variable name wherever you want its value to be substituted. Keep in mind that the macro processor doesn’t look for macros inside single quotation marks. To get around this, simply use double quotation marks. The following statements show possible ways to use the macro variables defined above:

DO i = 1 to &Iterations;

CountryName = "&Country";

After being resolved by the macro processor, these statements would become:

DO i = 1 to 10;

CountryName = "New Zealand";

Example  A grower of tropical flowers records information about each sale in a raw data file. The data include location, customer ID, date of sale, variety of flower, sale quantity, and sale amount.

1 240W 02-07-2020 Ginger    120  960

1 240W 02-10-2020 Protea    180 1710

2 356W 02-10-2020 Heliconia  60  720

2 356W 02-15-2020 Anthurium 300 1050

2 188R 02-16-2020 Ginger     24  192

1 240W 02-21-2020 Heliconia  48  600

1 240W 02-27-2020 Protea     48  456

2 356W 02-28-2020 Ginger    240 1980

Periodically, the grower needs a report about sales of a single variety. The macro variable in this program allows the grower to choose one variety without editing the WHERE and TITLE statements in the PROC step. Instead, he just types the name of the variety once, in the %LET statement.

%LET FlowerType = Ginger;

* Read the raw data and create a permanent SAS data set;

LIBNAME tropical 'c:MySASLib';

DATA tropical.flowersales;

   INFILE 'c:MyRawDataTropicalFlowers.dat';

   INPUT Location CustomerID $4. @8 SaleDate MMDDYY10. @19 Variety $9.

         SaleQuantity SaleAmount;

   FORMAT SaleDate MMDDYY10.;

RUN;

* Print the report using a macro variable;

PROC PRINT DATA = tropical.flowersales;

   WHERE Variety = "&FlowerType";

   FORMAT SaleAmount DOLLAR7.;

   TITLE "Sales of &FlowerType";

RUN;

The program starts with a %LET statement that creates a macro variable named &FlowerType, assigning to it a value of Ginger. Because &FlowerType is defined outside a macro, it is a global macro variable and can be used anywhere in this program. In this case, the value Ginger is substituted for &FlowerType in a WHERE statement and a TITLE statement. Here are the results:

Sales of Ginger

 

Obs	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	$960
5	2	188R	02/16/2020	Ginger	24	$192
8	2	356W	02/28/2020	Ginger	240	$1,980

This is a short program, so using a macro variable didn’t save much trouble. However, if you had a program 100 or even 1,000 lines long, a macro variable could be a blessing.

7.3     Concatenating Macro Variables with Other Text

The previous section described how you can use macro variables to increase the flexibility of your SAS programs. Macro variables hold pieces of text that you can use later to insert into your program. These pieces of text can be used alone, or they can be combined with other text.

Combining text with macro variables  When SAS encounters an ampersand (&) embedded in text, it will look for macro variable names starting with the first character after the ampersand until it encounters either a space, a semicolon, another ampersand, or a period. So, if you want to add text before your macro variable, simply concatenate the text with an ampersand and the macro variable name. If you want to add text after the macro variable, then you need to insert a period between the end of the macro variable name and the text. The period signals the end of the macro variable and will not be included in the resolved text. Concatenating two macro variables together does not require a period between the names, because the ampersand for the second macro variable signals the end of the first macro variable.

Here are examples with two macro variables, &Region and &MyName, defined as follows:

%LET Region = West;

%LET MyName = Sam;

SAS statement before resolution	SAS statement after resolution
Office = "NorthAmerica&Region";	Office = "NorthAmericaWest";
Office = "&Region.Coast";	Office = "WestCoast";
DATA &MyName..Sales;	DATA Sam.Sales;
DATA &MyName&Region.ern_Sales;	DATA SamWestern_Sales;

Automatic macro variables  Every time you invoke SAS, the macro processor automatically creates certain macro variables. These macro variables can be used in your programs just like macro variables that you create. Some examples of automatic macro variables are:

Variable name	Example	Description
&SYSDATE	18MAY20	the character value of the date that job or session began
&SYSDAY	Wednesday	the day of the week that job or session began
&SYSNOBS	312	number of observations in last SAS data set created

 

Example  This example uses the FLOWERSALES data set created in the previous section.

	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	960
2	1	240W	02/10/2020	Protea	180	1710
3	2	356W	02/10/2020	Heliconia	60	720
4	2	356W	02/15/2020	Anthurium	300	1050
5	2	188R	02/16/2020	Ginger	24	192
6	1	240W	02/21/2020	Heliconia	48	600
7	1	240W	02/27/2020	Protea	48	456
8	2	356W	02/28/2020	Ginger	240	1980

This program creates a macro variable named &SumVar which, when used with the prefix Sale, determines which variable will be summarized. When the macro variable has the value Quantity, as in this example, then the variable in the VAR statement of the PROC MEANS becomes SaleQuantity. The &SumVar macro variable also appears in the TITLE statement.

%LET SumVar = Quantity;

LIBNAME tropical 'c:MySASLib';

* Create RTF file with today's date in the file name;

ODS RTF PATH = 'c:MyRTFFiles' FILE = "FlowerSales_&SYSDATE..rtf";

* Summarize the sales for the selected variable;

PROC MEANS DATA = tropical.flowersales SUM MIN MAX MAXDEC=0;

   VAR Sale&SumVar;

   CLASS Variety;

   TITLE "Summary of Sales &SumVar by Variety";

RUN;

* Close the RTF file;

ODS RTF CLOSE;

The program creates an RTF file, and the name of the file depends on the date the SAS job or session begins. If the session begins on January 25, 2020, then the filename will be FlowerSales_25JAN20.rtf. Notice that there are two periods in the file specification in the ODS RTF statement. The first period signals the end of the macro variable name &SYSDATE, while the second period becomes part of the filename. Here is what the RTF file looks like opened in Microsoft Word.

7.4    Creating Modular Code with Macros

Anytime that you find yourself writing the same or similar SAS statements over and over, you should consider using a macro. A macro lets you package a piece of bug-free code and use it repeatedly within a single SAS program or in many SAS programs.

You can think of a macro as a kind of sandwich. The %MACRO and %MEND statements are like two slices of bread. Between those slices you can put any statements you want. The general form of a macro is:

%MACRO macro-name;

   macro-text

%MEND macro-name;

The %MACRO statement tells SAS that this is the beginning of a macro, while %MEND marks the end. Macro-name is a name you make up, and can be up to 32 characters in length, start with a letter or underscore, and contain only letters, numerals, and underscores. The macro-name in the %MEND statement is optional, but your macros will be easier to debug and maintain if you include it. That way there’s no question which %MACRO statement goes with which %MEND. Macro-text (also called a macro definition) is a set of SAS statements.

Invoking a macro  After you have defined a macro, you can invoke it by adding the percent sign prefix to its name, like this:

%macro-name

A semicolon is not required when invoking a macro, though adding one generally does no harm.

Example  Using the data from the previous section, this example creates a simple macro. The data include location, customer ID, date of sale, variety of flower, sale quantity, and sale amount.

	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	960
2	1	240W	02/10/2020	Protea	180	1710
3	2	356W	02/10/2020	Heliconia	60	720
4	2	356W	02/15/2020	Anthurium	300	1050
5	2	188R	02/16/2020	Ginger	24	192
6	1	240W	02/21/2020	Heliconia	48	600
7	1	240W	02/27/2020	Protea	48	456
8	2	356W	02/28/2020	Ginger	240	1980

The following program creates a macro named %Sample to sort the data by SaleQuantity and print the five observations with the largest sales. Then the program invokes the macro.

LIBNAME tropical 'c:MySASLib';

 

* Macro to print 5 largest sales;

%MACRO Sample;

   PROC SORT DATA = tropical.flowersales OUT = salesout;

      BY DESCENDING SaleQuantity;

   RUN;

   PROC PRINT DATA = salesout (OBS = 5);

      FORMAT SaleAmount DOLLAR7.;

      TITLE 'Five Largest Sales by Quantity';

   RUN;

%MEND Sample;

* Invoke the macro;

%Sample

Here is the output:

Five Largest Sales by Quantity

 

Obs	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	2	356W	02/15/2020	Anthurium	300	$1,050
2	2	356W	02/28/2020	Ginger	240	$1,980
3	1	240W	02/10/2020	Protea	180	$1,710
4	1	240W	02/07/2020	Ginger	120	$960
5	2	356W	02/10/2020	Heliconia	60	$720

This macro is fairly limited because it does the same thing every time. To increase the flexibility of macros, combine them with %LET statements or add parameters as described in the next section.

Macro autocall libraries  The macros in this book are defined and invoked inside a single program, but you can also store macros in a central location, called an autocall library. Macros in an autocall library can be shared by programs and programmers. Basically you save your macros as files in a directory or as members of a partitioned data set (depending on your operating environment), and use the MAUTOSOURCE and SASAUTOS= system options to tell SAS where to look for macros. Then you can invoke a macro even though the original macro does not appear in your program. For more information see the SAS Documentation.

7.5     Adding Parameters to Macros

Macros can save you a lot of trouble, allowing you to write a set of statements once and then use them over and over. However, you usually don’t want to repeat exactly the same statements. You may want the same report, but for a different data set, or product, or patient. Parameters allow you to do this.

Parameters are macro variables whose value you set when you invoke a macro. The simplest macros, like the macro in the previous section, have no parameters. To add parameters to a macro, you simply list the macro-variable names between parentheses in the %MACRO statement. Here is one of the possible forms of the parameter-list.

%MACRO macro-name (parameter-1= ,parameter-2= , . . . parameter-n=);

   macro-text

%MEND macro-name;

For example, a macro named %QuarterlyReport might start like this:

%MACRO QuarterlyReport(Quarter=,SalesRep=);

This macro has two parameters: &Quarter and &SalesRep. You could invoke the macro with this statement:

%QuarterlyReport(Quarter=3,SalesRep=Smith)

The SAS macro processor would replace each occurrence of the macro variable &Quarter with the value 3, and it would substitute Smith for &SalesRep.

Example  Using the tropical flower data again, suppose the grower often needs a report showing sales to an individual customer. The following program defines a macro that lets the grower select sales for a single customer and then sort the results. As before, the data contain the location, customer ID, date of sale, variety of flower, sale quantity, and sale amount.

	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	960
2	1	240W	02/10/2020	Protea	180	1710
3	2	356W	02/10/2020	Heliconia	60	720
4	2	356W	02/15/2020	Anthurium	300	1050
5	2	188R	02/16/2020	Ginger	24	192
6	1	240W	02/21/2020	Heliconia	48	600
7	1	240W	02/27/2020	Protea	48	456
8	2	356W	02/28/2020	Ginger	240	1980

The following program defines a macro named %Select, and then invokes the macro twice. This macro sorts and prints the FLOWERSALES data set, using parameters to create two macro variables named &Customer and &SortVar.

LIBNAME tropical 'c:MySASLib';

 

* Macro with parameters;

%MACRO Select(Customer=,SortVar=);

   PROC SORT DATA = tropical.flowersales OUT = salesout;

      BY &SortVar;

      WHERE CustomerID = "&Customer";

   RUN;

   PROC PRINT DATA = salesout;

      FORMAT SaleAmount DOLLAR7.;

      TITLE1 "Orders for Customer Number &Customer";

      TITLE2 "Sorted by &SortVar";

   RUN;

%MEND Select;

 

*Invoke the macro;

%Select(Customer = 356W, SortVar = SaleQuantity)

%Select(Customer = 240W, SortVar = Variety)

Here is the output:

Orders for Customer Number 356W

Sorted by SaleQuantity

Obs	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	2	356W	02/10/2020	Heliconia	60	$720
2	2	356W	02/28/2020	Ginger	240	$1,980
3	2	356W	02/15/2020	Anthurium	300	$1,050

 

Orders for Customer Number 240W

Sorted by Variety

Obs	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	$960
2	1	240W	02/21/2020	Heliconia	48	$600
3	1	240W	02/10/2020	Protea	180	$1,710
4	1	240W	02/27/2020	Protea	48	$456

7.6     Writing Macros with Conditional Logic

Combining macros and macro variables gives you a lot of flexibility, but you can increase that flexibility even more by adding macro statements such as %IF. Fortunately, many macro statements have parallel statements in standard SAS code so they should feel familiar. Here are the general forms of the statements used for conditional logic in macros:

%IF condition %THEN action;

   %ELSE %IF condition %THEN action;

   %ELSE action;

%IF condition %THEN %DO;

   SAS statements

%END;

The %IF-%THEN/%ELSE statements must be used inside a macro. The %IF-%THEN-%DO-%END statements can be used inside a macro, or starting with SAS 9.4M5, outside a macro in open SAS code as long as no other %IF-%THEN statements are nested within the %DO-%END group.

You may be wondering why anyone needs these statements. Why not just use the standard IF-THEN? You may indeed use standard IF-THEN statements in your macros, but you will use them for different actions. %IF statements can contain actions that standard IF statements can’t contain, such as complete DATA or PROC steps and even other macro statements. The %IF-%THEN statements don’t appear in the standard SAS code generated by your macro. Remember you are writing a program that writes a program.

For example, you could combine conditional logic with the &SYSDAY automatic variable, like this:

%IF &SYSDAY = Tuesday %THEN %LET Country = Belgium;

   %ELSE %LET Country = France;

If you run the program on Tuesday, the macro processor resolves the statements to:

%LET Country = Belgium;

If you run the program on any other day, then the macro processor resolves the statements to:

%LET Country = France;

Example  Using the tropical flower data again, this example shows a macro with conditional logic. The grower wants to print one report on Monday and a different report on Tuesday.

	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	960
2	1	240W	02/10/2020	Protea	180	1710
3	2	356W	02/10/2020	Heliconia	60	720
4	2	356W	02/15/2020	Anthurium	300	1050
5	2	188R	02/16/2020	Ginger	24	192
6	1	240W	02/21/2020	Heliconia	48	600
7	1	240W	02/27/2020	Protea	48	456
8	2	356W	02/28/2020	Ginger	240	1980

Here is the program which uses the &SYSDAY automatic macro variable to control which report is generated:

LIBNAME tropical 'c:MySASLib';

 

*This macro selects which report to run based on the day of the week;

%MACRO DailyReports;

   %IF &SYSDAY = Monday %THEN %DO;

      PROC PRINT DATA = tropical.flowersales;

         FORMAT SaleAmount DOLLAR7.;

         TITLE 'Monday Report: Current Flower Sales';

      RUN;

   %END;

   %ELSE %IF &SYSDAY = Tuesday %THEN %DO;

      PROC MEANS DATA = tropical.flowersales MEAN MIN MAX MAXDEC = 2;

         CLASS Variety;

         VAR SaleQuantity;

         TITLE 'Tuesday Report: Summary of Flower Sales';

      RUN;

   %END;

%MEND DailyReports;

 

%DailyReports

When the program is submitted on Tuesday, the macro processor will write this program:

LIBNAME tropical 'c:MySASLib';

PROC MEANS DATA = tropical.flowersales MEAN MIN MAX MAXDEC = 2;

   CLASS Variety;

   VAR SaleQuantity;

   TITLE 'Tuesday Report: Summary of Flower Sales';

RUN;

If you run this program on Tuesday the output will look like this:

Tuesday Report: Summary of Flower Sales

The MEANS Procedure

Analysis Variable: SaleQuantity
Variety	N Obs	Mean	Minimum	Maximum
Anthurium	1	300.00	300.00	300.00
Ginger	3	128.00	24.00	240.00
Heliconia	2	54.00	48.00	60.00
Protea	2	114.00	48.00	180.00

If you run this program on Monday, you will get the PROC PRINT output. If you run this program on another day of the week, such as Wednesday, you will get no errors, but also no output.

7.7     Using %DO Loops in Macros

Like the iterative DO group in the DATA step, %DO loops in macros can be useful anytime you find yourself writing the same, or similar, blocks of code over and over. The difference is that with using %DO loops in macros, you can include all types of SAS statements in the loop, not just DATA step statements. The iterative %DO statement can be used only inside a macro and not in open SAS code. Here is the general form of the statements used for iterative processing in macros:

%DO macro-variable-name = start-value %TO stop-value;

    macro-text

%END;

When the macro variable name is defined in the %DO statement, it does not start with an ampersand (&). But if you want to use the macro variable elsewhere in your macro, then, in those places, you need to include the ampersand. The start and stop values must be integers, or expressions or macro variables that resolve to integers. By default, the %DO loop will increment by one. If you want to increment by something other than one, then add a %BY:

%DO macro-variable-name = start-value %TO stop-value %BY increment;

    macro-text

%END;

It is important to remember that, as with other macro statements, the %DO and %END statements do not become part of the program that SAS executes.

For example, suppose that you have SAS data sets for ten years named SALES2011 through SALES2020 and you want to print each one. You could write ten separate PROC PRINTs, or you could include a %DO loop inside a macro:

%MACRO PrintYears;

  %DO Year = 2011 %TO 2020;

     PROC PRINT DATA = sales&Year;

     RUN;

  %END;

%MEND PrintYears;

Example  Using the tropical flower data again, this example shows how %DO loops in macros can be useful. For each location, the grower wants to calculate the sum of the variables SaleQuantity and SaleAmount and then store the results in two data sets, one for each location.

	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	960
2	1	240W	02/10/2020	Protea	180	1710
3	2	356W	02/10/2020	Heliconia	60	720
4	2	356W	02/15/2020	Anthurium	300	1050
5	2	188R	02/16/2020	Ginger	24	192
6	1	240W	02/21/2020	Heliconia	48	600
7	1	240W	02/27/2020	Protea	48	456
8	2	356W	02/28/2020	Ginger	240	1980

Here is the program:

LIBNAME tropical 'c:MySASLib';

 

*This macro creates summary data sets for each value of location;

%MACRO MeanSales;

  %DO Loc = 1 %TO 2;

    PROC MEANS DATA = tropical.flowersales NOPRINT;

      WHERE Location = &Loc;

      VAR SaleQuantity SaleAmount;

      OUTPUT OUT = salesloc&Loc

         SUM(SaleQuantity SaleAmount) = TotalQuantity TotalAmount;

    RUN;

  %END;

%MEND MeanSales;

 

%MeanSales

The %DO loop creates a macro variable named &Loc that takes on the values of 1 and 2. These values are then substituted in the WHERE statement and again in the name of the output data set for the PROC MEANS. The macro processor will write this program, which contains two PROC MEANS:

PROC MEANS DATA = tropical.flowersales NOPRINT;

   WHERE Location = 1;

   VAR SaleQuantity SaleAmount;

   OUTPUT OUT = salesloc1

      SUM(SaleQuantity SaleAmount) = TotalQuantity TotalAmount;

RUN;

PROC MEANS DATA = tropical.flowersales NOPRINT;

   WHERE Location = 2;

   VAR SaleQuantity SaleAmount;

   OUTPUT OUT = salesloc2

      SUM(SaleQuantity SaleAmount) = TotalQuantity TotalAmount;

RUN;

 

Here is the data set SALESLOC1:

	_TYPE_	_FREQ_	TotalQuantity	TotalAmount
1	0	4	396	3726

And here is the data set SALESLOC2:

	_TYPE_	_FREQ_	TotalQuantity	TotalAmount
1	0	4	624	3942

 

7.8     Writing Data-Driven Programs with CALL SYMPUTX

When you submit a SAS program containing macros it goes first to the macro processor which generates standard SAS code from the macro references. Then SAS compiles and executes your program. Not until execution—the final stage—does SAS see any actual data values. This is the tricky part of writing data-driven programs: SAS doesn’t know the values of your data until the execution phase, and by that time it is ordinarily too late. However, there are ways to have your digital cake and eat it too. These include CALL SYMPUTX (described here) and PROC SQL (see the next section).

CALL SYMPUTX takes a value from a DATA step and assigns it to a macro variable. You can then use this macro variable in later steps (in other words, not the same DATA step where you created the macro variable). To assign a value to a single macro variable, you use CALL SYMPUTX with this general form:

CALL SYMPUTX("macro-variable-name", value);

where macro-variable-name, enclosed in quotation marks, is the name of a macro variable, either new or old, and value is the value you want to assign to that macro variable. Value can be the name of a variable whose value SAS will use, or it can be a constant value enclosed in quotation marks. CALL SYMPUTX strips leading and trailing blanks from value, while the older CALL SYMPUT does not.

CALL SYMPUTX is often used in IF-THEN statements such as this:

IF Age >= 18 THEN CALL SYMPUTX("Status", "Adult");

   ELSE CALL SYMPUTX("Status", "Minor");

These statements create a macro variable named &Status and assign to it a value of Adult or Minor depending on the variable Age. The following CALL SYMPUTX uses a variable as its value:

IF TotalSales > 1000000 THEN CALL SYMPUTX("BestSeller", BookTitle);

This statement tells SAS to create a macro variable named &BestSeller, which is equal to the value of the variable BookTitle when TotalSales exceed 1,000,000.

Example  Here again are the flower sales data used in the previous sections.

	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	960
2	1	240W	02/10/2020	Protea	180	1710
3	2	356W	02/10/2020	Heliconia	60	720
4	2	356W	02/15/2020	Anthurium	300	1050
5	2	188R	02/16/2020	Ginger	24	192
6	1	240W	02/21/2020	Heliconia	48	600
7	1	240W	02/27/2020	Protea	48	456
8	2	356W	02/28/2020	Ginger	240	1980

In this example, the grower wants a program that will find the customer with the single largest order in dollars, and print all the orders for that customer.

LIBNAME tropical 'c:MySASLib';

 

* Sort the data by descending SaleAmount and save to a temporary data set;

PROC SORT DATA = tropical.flowersales OUT = salessorted;

   BY DESCENDING SaleAmount;

RUN;

 

* Find biggest order and pass the customer id to a macro variable;

DATA _NULL_;

   SET salessorted ;

   IF _N_ = 1 THEN CALL SYMPUTX("SelectedCustomer",CustomerID);

   STOP;

RUN;

 

PROC PRINT DATA = tropical.flowersales;

   WHERE CustomerID = "&SelectedCustomer";

   FORMAT SaleAmount DOLLAR7.;

   TITLE "Customer &SelectedCustomer Had the Single Largest Order";

RUN;

This program has several steps, but each step is fairly simple. First, PROC SORT sorts the data by descending SaleAmount and saves the data to a temporary SAS data set SALESSORTED. That way, the largest single order will be the first observation in the newly sorted data set.

The DATA step then uses CALL SYMPUTX to assign the value of the variable CustomerID to the macro variable &SelectedCustomer when _N_ equals 1 (the first iteration of the DATA step). Since that is all we need from this DATA step, we can use the STOP statement to tell SAS to end this DATA step. The STOP statement is not necessary, but it is efficient because it prevents SAS from reading the remaining observations for no reason.

When SAS reaches the RUN statement, SAS knows that the DATA step has ended so SAS executes the DATA step. At this point the macro variable &SelectedCustomer has the value 356W (the customer ID with the largest single order in dollars) and can be used in the PROC PRINT. The output looks like this:

Customer 356W Had the Single Largest Order

Obs	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
3	2	356W	02/10/2020	Heliconia	60	$720
4	2	356W	02/15/2020	Anthurium	300	$1,050
8	2	356W	02/28/2020	Ginger	240	$1,980

7.9     Writing Data-Driven Programs with PROC SQL

In the previous section we described how to write data-driven programs by creating macro variables in DATA steps using CALL SYMPUTX. But that is not the only way to write data-driven programs. You can also use the INTO clause in the SQL procedure to create macro variables, and then use those macro variables later in your program.

Here is the general form of PROC SQL to create a single macro variable from a variable in a data set:

PROC SQL NOPRINT;

   SELECT variable

     INTO :macro-variable-name

     FROM data-set-name;

QUIT;

where variable is the name of the variable whose value you want to store in a macro variable and macro-variable-name is the name of the macro variable you want to create. Note that the macro-variable-name is preceded with a colon (:) here. The FROM clause tells which SAS data set to use. If the result of the PROC SQL has more than one observation, then the first value of the variable will be stored in the macro variable. For this reason, you may want to limit the result of the PROC SQL to just one observation by using a summary function in the SELECT clause (as described in Section 6.8), or by using subsetting clauses such as WHERE (Section 3.9) or HAVING. The HAVING clause is similar to WHERE except you can include summary functions in the HAVING clause but not in WHERE.

For example, if you want the macro variable named &Entries to contain the number of observations from the data set CONTEST, you could use these SELECT and INTO clauses:

SELECT COUNT(*)

  INTO :Entries

  FROM contest;

Or, suppose that you have weather data and you want to put the date of the coldest day (variable RecordedDate) into a macro variable named &ColdestDate. To do this, you could use the HAVING clause and the MIN summary function to select the observation with the minimum value of the variable TempF:

SELECT RecordedDate

  INTO :ColdestDate

  FROM weatherdata

  HAVING TempF = MIN(TempF);

Example  Here again are the flower sales data. As with the example from the previous section, the grower wants a program that will find the customer with the single largest order in dollars, and print all the orders for that customer.

 

 

	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
1	1	240W	02/07/2020	Ginger	120	960
2	1	240W	02/10/2020	Protea	180	1710
3	2	356W	02/10/2020	Heliconia	60	720
4	2	356W	02/15/2020	Anthurium	300	1050
5	2	188R	02/16/2020	Ginger	24	192
6	1	240W	02/21/2020	Heliconia	48	600
7	1	240W	02/27/2020	Protea	48	456
8	2	356W	02/28/2020	Ginger	240	1980

 

This program reproduces the results from the previous section using PROC SQL instead of CALL SYMPUTX.

LIBNAME tropical 'c:MySASLib';

*Find customer with largest single sale amount;

PROC SQL NOPRINT;

  SELECT CustomerID

    INTO :SelectedCustomer

    FROM tropical.flowersales

    HAVING SaleAmount = MAX(SaleAmount);

QUIT;

PROC PRINT DATA = tropical.flowersales;

   WHERE CustomerID = "&SelectedCustomer";

   FORMAT SaleAmount DOLLAR7.;

   TITLE "Customer &SelectedCustomer Had the Single Largest Order";

RUN;

This program starts with a PROC SQL and the NOPRINT option since we only want to create a macro variable and do not want SQL to produce a report. The INTO clause creates a macro variable named &SelectedCustomer from the value of the variable, CustomerID, listed in the SELECT clause. The FROM clause specifies the input data set. The HAVING clause keeps the observation where SaleAmount is equal to the maximum value for SaleAmount in the data set. This ensures that the value of the macro variable &SelectedCustomer will be the customer ID having the highest value of the variable SaleAmount.

As in the previous section, the macro variable &SelectedCustomer is then used in the PROC PRINT to print all the orders for the customer ID with the largest single order in dollars. The output looks like this:

Customer 356W Had the Single Largest Order

Obs	Location	CustomerID	SaleDate	Variety	SaleQuantity	SaleAmount
3	2	356W	02/10/2020	Heliconia	60	$720
4	2	356W	02/15/2020	Anthurium	300	$1,050
8	2	356W	02/28/2020	Ginger	240	$1,980

 

 

7.10   Debugging Macro Errors

Many people find that writing macros is not that hard. Debugging them, however, is another matter. This section covers techniques to ease the debugging process.

Avoiding macro errors  As much as possible, develop your program in standard SAS code first. Then, when it is bug-free, add the macro logic one feature at a time. Add your %MACRO and %MEND statements. When that’s working, add your macro variables, one at a time, and so on, until your macro is complete and bug-free. Also, always remember to use double quotes around text that contains macro variables as macro variables are not resolved inside single quotes.

Listing macro variables  You can get a listing in the SAS log of all macro variables you have created along with their values and scope (local or global) by including the following in your program:

%PUT _USER_;

This statement can be very useful for debugging long, or complicated macros and can appear anywhere in your program.

System options for debugging macros  These five system options affect the kinds of messages SAS writes in your log. The default settings appear in bold.

MERROR | NOMERROR	When this option is on, SAS will issue a warning if you invoke a macro that SAS cannot find.
SERROR | NOSERROR	When this option is on, SAS will issue a warning if you use a macro variable that SAS cannot find.
MLOGIC | NOMLOGIC	When this option is on, SAS prints in your log details about the execution of macros.
MPRINT | NOMPRINT	When this option is on, SAS prints in your log the standard SAS code generated by macros.
SYMBOLGEN | NOSYMBOLGEN	When this option is on, SAS prints in your log the values of macro variables.

While you want the MERROR and SERROR options to be on at all times, you will probably want to turn on MLOGIC, MPRINT, and SYMBOLGEN one at a time and only while you are debugging since they tend to make your log hard to read. To turn them on (or off), use the OPTIONS statement, for example:

OPTIONS MPRINT NOSYMBOLGEN NOMLOGIC;

MERROR message  If SAS has trouble finding a macro, and the MERROR option is on, then SAS will print this message:

WARNING: Apparent invocation of macro SAMPL not resolved.

Check for a misspelled macro name.

SERROR message  If SAS has trouble resolving a macro variable in open code, and the SERROR option is on, then SAS will print this message:

WARNING: Apparent symbolic reference FLOWER not resolved.

Check for a misspelled macro variable name. If the name is spelled right, then the scope may be wrong. Check to see if you are using a local variable outside of its macro. See Section 7.1 for definitions of local and global macro variables.

MLOGIC messages  When the MLOGIC option is on, SAS prints messages in your log describing the actions of the macro processor. Here is a macro named %Sample:

%MACRO Sample(FlowerType=);

   PROC PRINT DATA = tropical.flowersales;

      WHERE Variety = "&FlowerType";

   RUN;

%MEND Sample;

%Sample(FlowerType = Anthurium)

If you run %Sample with the MLOGIC option, your log will look like this:

25   %Sample(FlowerType=Anthurium)

MLOGIC(SAMPLE):  Beginning execution.

MLOGIC(SAMPLE):  Parameter FLOWERTYPE has value Anthurium

MPRINT messages  When the MPRINT option is on, SAS prints messages in your log showing the SAS statements generated by your macro. If you run %Sample with the MPRINT option, your log will look like this:

37   %Sample(FlowerType=Anthurium)

MPRINT(SAMPLE):   PROC PRINT DATA = tropical.flowersales;

MPRINT(SAMPLE):   WHERE Variety = "Anthurium";

SYMBOLGEN messages  When the SYMBOLGEN option is on, SAS prints messages in your log showing the value of each macro variable after resolution. If you run %Sample with the SYMBOLGEN option, your log will look like this:

31   %Sample(FlowerType=Anthurium)