Chapter 6 Macro Language Functions

Introduction

Macro Character Functions

Macro Evaluation Functions

Macro Quoting Functions

Macro Variable Attribute Functions

Other Macro Functions

Using the %SYSFUNC and %QSYSFUNC Macro Functions

SAS Supplied Autocall Macro Programs Used Like Functions

 

The preceding chapters describe the basic structures of the macro programming language and the mechanics involved in processing macro language. This chapter describes the functions that are available in the macro programming language.

Macro functions greatly extend the use of macro variables and macro programming. Macro functions can be used in open code and in macro programs. The arguments of a macro function can be text strings, macro variables, macro functions, and macro program calls. The result of a macro function is always text. This result can be assigned to a macro variable. A macro function can also be inserted directly into your SAS statements to build SAS statements.

Most macro functions have SAS language counterparts. If you know how to write DATA step programs, you already have a familiarity with the style and structure of many of the macro functions.

Some of the tasks you can do with macro functions include:

• extracting substrings of macro variables

• searching for a string of characters in a macro variable

• temporarily converting macro values to numeric so that you can use the macro variables in calculations

• using SAS language functions and functions created with PROC FCMP in your macro language statements

• allowing semicolons to be treated as a text value rather than as a symbol to terminate a statement

This chapter classifies the macro functions into five categories: character, evaluation, quoting, macro variable attribute, and other. These categories and some of the functions in each category are briefly described below.

Additionally, SAS ships a library of autocall macro programs with its software, which may or may not be installed at your site. Autocall macro programs are uncompiled source code and text stored as entries in SAS libraries. This set of autocall macro programs can be used like macro language functions. This SAS supplied autocall macro program library is described at the end of this chapter. Also see Chapter 10 for more discussion on the application of autocall macro programs.

Macro character functions operate on strings of text or on macro variables. They can modify their arguments, return information about an argument, or return text values. Several of the character functions you might be familiar with in the SAS language have macro language counterparts. Table 6.1 lists the macro character functions.

Macro functions %SCAN, %SUBSTR, and %UPCASE each have a version that should be used instead if the result of the macro function might contain a special character or mnemonic operator. The names of these macro functions are %QSCAN, %QSUBSTR, and %QUPCASE.

Table 6.1 Macro character functions

Function	Action
%INDEX(source, string)	returns the position in source of the first character of string.
%LENGTH(string|text expression)	returns the length of string or the length of the results of the resolution of text expression.
%SCAN(argument,       n <,charlist<,modifiers>>)	searches for the nth word in argument, where argument can be text or an expression and the words in argument are separated by charlist. Specify modifiers to modify how %SCAN determines word boundaries in argument. Use %QSCAN when you need to mask special characters or mnemonic operators in the result.
%SUBSTR(argument,position<,length>)	extracts a substring of length characters from argument starting at position. Use %QSUBSTR when you need to mask special characters or mnemonic operators in the result.
%UPCASE(string|text expression)	converts character string or text expression to uppercase. Use %QUPCASE when you need to mask special characters or mnemonic operators in the result.

Example 6.1: Using %SUBSTR to Extract Text from a Macro Variable Value

Example 6.1 shows how the %SUBSTR function extracts text from strings of characters. The WHERE statement in the PROC MEANS step selects observations from the first day of the current month through the day the program was run. The current month is determined by extracting that information from the value of automatic macro variable SYSDATE.

proc means data=books.ytdsales;

  title “Sales for 01%substr(&sysdate,3,3) through &sysdate9”;

  where “01%substr(&sysdate,3)”d le datesold le “&sysdate”d;

  class section;

  var saleprice;

run;

After resolution of the macro variable references, the PROC MEANS step looks as follows when submitted on September 15, 2014.

proc means data=books.ytdsales;

  title “Sales for 01SEP through 15SEP2014”;

  where “01SEP13”d le datesold le “15SEP14”d;

  class section;

  var saleprice;

run;

Example 6.2: Using %SCAN to Extract the Nth Item from a Macro Variable Value

The %SCAN macro character function in Example 6.2 extracts a specific word from a string of words that are separated with blanks. The code specifies that %SCAN, through the value of REPMONTH, extract the third word from macro variable MONTHS.

One of the default delimiters for %SCAN is a blank. Therefore, Example 6.2 does not specify the optional third argument to %SCAN.

Under ASCII systems, the other default delimiters for %SCAN are:

! $ % & ( ) * + , - . / ; < ^|

Under EBCDIC systems, the other default delimiters for %SCAN are:

! $ % & ( ) * + , - . / ; < ¬ | ¢|

While not used in this simple example, you can add modifiers to %SCAN to alter how %SCAN determines boundaries between items in your argument string. For example, if you add the B modifier, the macro processor scans your argument string from right to left instead of the default direction of left to right.

%let months=January February March April May June;

%let repmonth=3;

proc print data=books.ytdsales;

  title “Sales Report for %scan(&months,&repmonth)”;

  where month(datesold)=&repmonth;

  var booktitle author saleprice;

run;

After resolution of the macro variable references, the PROC PRINT step becomes:

proc print data=books.ytdsales;

  title “Sales Report for March”;

  where month(datesold)=3;

  var booktitle author saleprice;

run;

Example 6.3: Using %UPCASE to Convert a Macro Variable Value to Uppercase

Macro program LISTTEXT in Example 6.3 lists all the titles sold that contain a specific text string. The text string is passed to the macro program through the parameter KEYTEXT. This text string might be in different forms in the title: lowercase, uppercase, or mixed case. Because of this, both the macro variable's value and the value of the data set variable BOOKTITLE are converted to uppercase. This increases the likelihood of matches when the two are compared.

%macro listtext(keytext);

  %let keytext=%upcase(&keytext);

  proc print data=books.ytdsales;

  title “Book Titles Sold Containing Text String &keytext”;

    where upcase(booktitle) contains “&keytext”;

    var booktitle author saleprice;

  run;

%mend;

%listtext(web)

When the macro program executes, the TITLE statement resolves to

Book Titles Sold Containing Text String WEB

The WHERE statement at execution resolves to

where upcase(booktitle) contains “WEB”;

The two macro evaluation functions, %EVAL and %SYSEVALF, evaluate arithmetic expressions and logical expressions. These expressions are comprised of operators and operands that the macro processor evaluates to produce a result. The arguments to one of these macro evaluation functions are temporarily converted to numbers so that a calculation (arithmetic or logical) can be completed. The macro evaluation function converts the result that it returns to text.

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.

The %EVAL function evaluates expressions using integer arithmetic. The %SYSEVALF function evaluates expressions using floating point arithmetic. Macro expressions are constructed with the same arithmetic and comparison operators found in the SAS language. A section in Chapter 7 discusses in more detail how to construct macro expressions.

The syntax of the %EVAL function is

%EVAL(arithmetic expression|logical expression)

The syntax of the %SYSEVALF function is

%SYSEVALF(arithmetic expression|logical expression

    <,conversion-type>)

By default, the result of the %SYSEVALF function is left as a number which the macro processor converts back to text. Otherwise, you can request %SYSEVALF to convert the result to a different format, as shown in Table 6.2. When you want to use one of these four conversion types, specify it as the second argument to %SYSEVALF.

Table 6.2 Conversion types that can be specified on the %SYSEVALF function

Conversion Type	Result that is returned by %SYSEVALF
BOOLEAN	0 if the result of the expression is 0 or null 1 if the result is any other value (The 0 and 1 are treated as text.)
CEIL	text that represents the smallest integer that is greater than or equal to the result of the expression
FLOOR	text that represents the largest integer that is less than or equal to the result of the expression
INTEGER	text that represents the integer portion of the expression’s result

For more discussion of the usage of %SYSEVALF and %EVAL, see Example 6.8 later in this chapter.

The %EVAL function does integer arithmetic. Therefore, this function treats numbers with decimal points as text. The %EVAL function generates an error when there are characters in the arguments that are supplied to %EVAL, as demonstrated by the second example in Table 6.3.

The statements in Table 6.3 show examples of the %EVAL and %SYSEVALF functions. The %PUT statements were submitted, and the results were written to the SAS log.

Table 6.3 Examples of %EVAL and %SYSEVALF evaluation functions

%PUT Statement	Results in SAS log
%put %eval(33 + 44);	77
%put %eval(33.2 + 44.1);	ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 33.2 + 44.1
%put %sysevalf(33.2 + 44.1);	77.3
%put  %sysevalf(33.2 + 44.1,integer);	77
%let a=3; %let b=10;
%put %eval(&b/&a);	3
%put %sysevalf(&b/&a);	3.3333333333333
%put %sysevalf(&b/&a,ceil);	4
%put %sysevalf(&b/&a,floor);	3
%put %sysevalf(&b/&a,boolean);	1
%let missvalue=.; %put %sysevalf(&b-&missvalue,               boolean);	NOTE: Missing values were generated as a result of performing an operation on missing values during %SYSEVALF expression evaluation. 0

 

Macro quoting functions mask special characters and mnemonic operators in your macro language statements so that the macro processor does not interpret them. The macro processor instead treats these items simply as text.

For example, you might want to assign a value to a macro variable that contains a character that the macro processor interprets as a macro trigger. The macro processor considers ampersands and percent signs followed by text as macro triggers. You must use a macro quoting function to tell the macro processor to ignore the special meaning of the ampersands and percent signs and instead treat them as text.

Consider what happens if you assign the name of the publisher, Doe&Lee Ltd., to a macro variable:

%let publisher=Doe&Lee Ltd.;

If you have not already defined a macro variable named LEE in your SAS session, you will see the following message displayed in your SAS log:

WARNING: Apparent symbolic reference LEE not resolved.

(If you had already defined a macro variable named LEE in your SAS session, you would not see the warning. Instead, the macro processor would resolve the reference to &LEE with the value assigned to the macro variable LEE.)

To prevent the macro processor from interpreting the ampersand as a macro trigger in the value being assigned to PUBLISHER, you must mask the value that you assign to the macro variable PUBLISHER. The macro quoting function %NRSTR correctly masks &LEE from view by the macro processor when it compiles the instruction. Therefore, when you apply %NRSTR to the text string, the macro processor ignores the ampersand as a macro trigger;does not attempt to resolve the value of the macro variable &LEE; and considers this use of the ampersand simply as text.

%let publisher=%nrstr(Doe&Lee Ltd.);

As it steps through its tasks in compiling and executing the %LET statement, the macro processor defines a global macro variable, PUBLISHER, and assigns the text Doe&Lee Ltd. to PUBLISHER.

The macro quoting functions can be grouped into two types based upon when they act: compilation and execution.

Table 6.4 lists the macro quoting functions. Chapter 8 discusses the topic of masking characters in macro programming more thoroughly, and it includes several examples that illustrate the concepts on how and when to apply the macro quoting functions.

Table 6.4 Macro quoting functions

Function	Action
%BQUOTE(character-string |         text expression)	Mask special characters and mnemonic operators in a character string or the value of resolved text expression at macro execution. Compared to %QUOTE, %BQUOTE does not require that unmatched quotation marks or unmatched parentheses be marked with a preceding percent sign (%).
%NRBQUOTE(character-string |         text expression)	Does the same as %BQUOTE and additionally masks ampersands (&) and percent signs (%). However, SAS recommends that you use %SUPERQ instead of %NRBQUOTE.
%QUOTE(character-string |         text expression)	Mask special characters and mnemonic operators in a character string or the value of resolved text expression at macro execution. Compared to %BQUOTE, %QUOTE requires that unmatched quotation marks and unmatched parentheses be marked with a preceding percent sign (%).
%NRQUOTE(character-string |          text expression)	Does the same as %QUOTE and additionally masks ampersands (&) and percent signs (%).However, SAS recommends that you use %SUPERQ instead of %NRQUOTE.
%STR(character-string)	Mask special characters and mnemonic operators in constant text at macro compilation.
%NRSTR(character-string)	Does the same as %STR and additionally masks ampersands (&) and percent signs (%).
%SUPERQ(macro-variable-name)	Masks all special characters including ampersands (&), percent signs (%), and mnemonic operators at macro execution and prevents further resolution of the value. Returns the value of a macro variable without attempting to resolve any macro program or macro variable references in the value.
%UNQUOTE(character-string |         text expression)	Unmasks all special characters and mnemonic operators in a value at macro execution.

 

Table 6.5 lists the three macro variable attribute functions that supply information about the existence and the domain (global vs. local) of macro variables. These functions can be especially useful when debugging problems with macro variable resolution.

Table 6.5 Macro variable attribute functions

Function	Action
%SYMEXIST(macro-variable-name)	Determines whether the named macro variable exists. The search starts with the most local symbol table, and the search proceeds up the hierarchy through other local symbol tables, ending the search at the global symbol table. If the macro variable exists, %SYMEXIST returns a value of 1; otherwise, it returns a 0.
%SYMGLOBL(macro-variable-name)	Determines whether the named macro variable is found in the global symbol table. If the macro variable exists in the global symbol table, %SYMGLOBL returns a value of 1; otherwise, it returns a 0.
%SYMLOCAL(macro-variable-name)	Determines whether the named macro variable is found in a local symbol table. The search starts with the most local symbol table, and the search proceeds up the hierarchy through other local symbol tables, ending the search at the local symbol table highest up in the hierarchy. If the macro variable exists in a local symbol table, %SYMLOCAL returns a value of 1; otherwise, it returns a 0.

Example 6.4: Using Macro Variable Attribute Functions to Determine Domain and Existence of Macro Variables

Chapter 5 discusses domains of macro variables. A macro variable can exist in either the global or local macro symbol table. You can successfully reference a macro variable stored in the global symbol table throughout your SAS session including within macro programs. There is only one global symbol table.

A local macro symbol table is created by executing a macro program that contains macro variables. If the macro variables do not already exist in the global table, macro variables defined in the macro program are stored in the local macro symbol table associated with the macro program. These local macro variables can be referenced only from within the macro program. The macro processor deletes a local macro symbol table when the macro program associated with the table ends. You can have more than one local macro symbol table at a time if one macro program calls another.

Example 5.4 in Chapter 5, which demonstrates domains of macro variables, is modified below in Example 6.4 to include the three functions described in Table 6.5 and to illustrate their use. This program introduces a new statement, %SYMDEL, which deletes macro variables from the global symbol table.

%* For example purposes only, ensure these two macro

   variables do not exist in the global symbol table;

%symdel glbsubset subset;

%macro makeds(subset);

  %global glbsubset;

  %let glbsubset=&subset;

   %* What is domain of SUBSET and GLBSUBSET inside MAKEDS?;

   %put ******** Inside macro program;

   %put Is SUBSET a local macro variable(0=No/1=Yes): %symlocal(subset);

   %put Is SUBSET a global macro variable(0=No/1=Yes): %symglobl(subset);

   %put Is GLBSUBSET a local macro variable(0=No/1=Yes): %symlocal(glbsubset);

   %put Is GLBSUBSET a global macro variable(0=No/1=Yes): %symglobl(glbsubset);

   %put ********;

  data temp;

    set books.ytdsales(where=(section=“&subset”));

    attrib qtrsold label='Quarter of Sale';

    qtrsold=qtr(datesold);

  run;

%mend makeds;

%makeds(Software)

%* Are SUBSET and GLBSUBSET in global symbol table?;

%put Does SUBSET exist (0=No/1=Yes): %symexist(subset);

%put Is SUBSET a global macro variable(0=No/1=Yes): %symglobl(subset);

%put Is GLBSUBSET a global macro variable(0=No/1=Yes): %symglobl(glbsubset);

proc tabulate data=temp;

  title “Book Sales Report Produced &sysdate9”;

  class qtrsold;

  var saleprice listprice;

  tables qtrsold all,

         (saleprice listprice)*(n*f=6. sum*f=dollar12.2) /

         box=“Section: &glbsubset”;

  keylabel all='** Total **';

run;

The following SAS log for the program shows how the functions %SYMLOCAL, %SYMGLOBL, and %SYMEXIST resolve in this example.

288  %* For example purposes only, ensure these two macro

289     variables do not exist in the global symbol table;

290

291  %symdel glbsubset subset;

WARNING: Attempt to delete macro variable GLBSUBSET failed.

         Variable not found.

WARNING: Attempt to delete macro variable SUBSET failed.

         Variable not found.

292

293  %macro makeds(subset);

294    %global glbsubset;

295    %let glbsubset=&subset;

296

297     %* What is domain of SUBSET and GLBSUBSET inside MAKEDS?;

298     %put ******** Inside macro program;

299     %put Is SUBSET a local macro variable(0=No/1=Yes):

299! %symlocal(subset);

300     %put Is SUBSET a global macro variable(0=No/1=Yes):

300! %symglobl(subset);

301     %put Is GLBSUBSET a local macro variable(0=No/1=Yes):

301! %symlocal(glbsubset);

302     %put Is GLBSUBSET a global macro variable(0=No/1=Yes):

302! %symglobl(glbsubset);

303     %put ********;

304

305    data temp;

306      set books.ytdsales(where=(section=“&subset”));

307      attrib qtrsold label='Quarter of Sale';

308      qtrsold=qtr(datesold);

309    run;

310  %mend makeds;

311

312  %makeds(Software)

******** Inside macro program

Is SUBSET a local macro variable(0=No/1=Yes): 1

Is SUBSET a global macro variable(0=No/1=Yes): 0

Is GLBSUBSET a local macro variable(0=No/1=Yes): 0

Is GLBSUBSET a global macro variable(0=No/1=Yes): 1

********

NOTE: There were 857 observations read from the data set

      BOOKS.YTDSALES.

      WHERE section='Software';

NOTE: The data set WORK.TEMP has 857 observations and 11

      variables.

NOTE: DATA statement used (Total process time):

      real time           0.03 seconds

      cpu time            0.00 seconds

313

314  %* Are SUBSET and GLBSUBSET in global symbol table?;

315  %put Does SUBSET exist (0=No/1=Yes):%symexist(subset);

Does SUBSET exist (0=No/1=Yes): 0

316  %put Is SUBSET a global macro variable(0=No/1=Yes):

316! %symglobl(subset);

Is SUBSET a global macro variable(0=No/1=Yes): 0

317  %put Is GLBSUBSET a global macro variable(0=No/1=Yes):

317! %symglobl(glbsubset);

Is GLBSUBSET a global macro variable(0=No/1=Yes): 1

318

319  proc tabulate data=temp;

320    title “Book Sales Report Produced &sysdate9”;

321    class qtrsold;

322    var saleprice listprice;

323    tables qtrsold all,

324           (saleprice listprice)*(n*f=6. sum*f=dollar12.2) /

325           box=“Section: &glbsubset”;

326    keylabel all='** Total **';

327  run;

NOTE: There were 857 observations read from the data set

      WORK.TEMP.

NOTE: PROCEDURE TABULATE used (Total process time):

      real time           0.12 seconds

      cpu time            0.03 seconds

 

The four macro functions described in this section (Table 6.6) do not fit into any of the four categories of macro functions described so far. These four functions do one of the following:

• apply SAS language functions to macro variables or text

• obtain information from the rest of SAS or the operating system in which the SAS session is running

The %SYSFUNC and %QSYSFUNC functions are especially useful in extending the use of the macro facility. These functions allow you to apply SAS language and user-written functions to your macro programming applications. Several examples of %SYSFUNC follow. Chapter 8 presents the topic of masking special characters and mnemonic operators, and it also includes an example that applies %QSYSFUNC. Macro function %QSYSFUNC does the same as %SYSFUNC, and it masks special characters and mnemonic operators in the result.

Table 6.6 Other macro functions

Function	Action
%SYSFUNC(function(argument(s)) <,format>)	executes SAS function or user-written function and returns the results to the macro facility (see also macro statement %SYSCALL)
%QSYSFUNC(function(argument(s)) <,format>)	does the same as %SYSFUNC and it also masks  special characters and mnemonic operators in the result
%SYSGET(host-environment-variable)	returns the value of host-environment-variable to the macro facility
%SYSPROD(SAS-product)	returns a code to indicate whether SAS-product is licensed at the site where SAS is currently running

Using the %SYSFUNC and %QSYSFUNC Macro Functions

The functions %SYSFUNC and %QSYSFUNC apply SAS programming language functions to text and macro variables in your macro programming. Providing access to the many SAS language functions in your macro programming applications, %SYSFUNC and %QSYSFUNC greatly extend the power of your macro programming.

Since these two functions are macro language functions and the macro facility is a text-handling language, the arguments to the SAS programming language function are not enclosed in quotation marks; it is understood that all arguments are text. Also, the values returned through the use of these two functions are considered text.

Functions cannot be nested within one call to %SYSFUNC and %QSYSFUNC. Each function must have its own %SYSFUNC or %QSYSFUNC call, and these %SYSFUNC and %QSYSFUNC calls can be nested.

Example 6.5: Using %SYSFUNC to Format a Date in the TITLE Statement

The TITLE statement in Example 6.5 shows how the elements of a date can be formatted using %SYSFUNC and the DATE SAS language function.

title

  “Sales for %sysfunc(date(),monname.) %sysfunc(date(),year.)”;

On January 30, 2014, the title statement would resolve to

Sales for January 2014

Example 6.6: Using %SYSFUNC to Execute a SAS Language Function and Assign the Result to a Macro Variable

Example 6.6 uses %SYSFUNC to access the SAS language function GETOPTION. The GETOPTION function displays the values of SAS options. The %SYSFUNC function invokes the GETOPTION function and returns the result to macro variable OPTVALUE. The %PUT statement lists the value assigned to OPTVALUE. The single parameter to GETOPTION is the name of the SAS option that should be checked.

%macro getopt(whatopt);

  %let optvalue=%sysfunc(getoption(&whatopt));

  %put Option &whatopt = &optvalue;

%mend getopt;

%getopt(number)

%getopt(orientation)

%getopt(date)

%getopt(symbolgen)

%getopt(compress)

The SAS log for Example 6.6 follows.

58   %getopt(number)

Option number = NUMBER

59   %getopt(orientation)

Option orientation = PORTRAIT

60   %getopt(date)

Option date = DATE

61   %getopt(symbolgen)

Option symbolgen = NOSYMBOLGEN

62   %getopt(compress)

Option compress = NO

Example 6.7: Using %SYSFUNC and the NOTNAME and NVALID SAS Language Functions to Determine If a Value Is a Valid SAS Variable Name

Since the macro language generates SAS code for you, a common task that macro programmers have is to construct SAS items such as variable names and format names. In doing so, it might be important to check the value that will be used to name the item to make sure that it does not contain any invalid characters or is too long.

Macro program CHECKVARNAME in Example 6.7 checks a SAS macro variable value to see if it can be used as a variable name. It uses both the NOTNAME and the NVALID SAS functions. The NOTNAME function finds the first position in the value that is an invalid character in naming a variable. The NVALID function determines if the value can be used as a variable name.

The second argument to NVALID in this example is V7. This argument requires three conditions to be true if it is to be determined to be valid:

• The value must start with a letter or underscore.

• All subsequent characters must be letters, underscores, or digits.

• Its length must be no greater than 32 characters.

The parameter passed to CHECKVARNAME is the prospective variable name that should be examined. The %SYSFUNC macro function is used in conjunction with the NOTNAME SAS language function and again with the NVALID SAS language function. The macro program writes messages to the SAS log about whether the value can be used as a variable name.

Example 6.7 calls CHECKVARNAME four times. The parameter values specified for the first two calls to CHECKVARNAME are valid SAS names. The parameter values specified in the third and fourth calls to CHECKVARNAME are not valid SAS names. The space in the parameter in the third call to CHECKVARNAME is invalid. The length of the parameter in the fourth call, as well as the exclamation point in the last position, makes the value invalid as a SAS name.

%macro checkvarname(value);

  %let position=%sysfunc(notname(&value));

  %put **** Invalid character in position: &position (0 means &value is okay);

  %let valid=%sysfunc(nvalid(&value,v7));

  %put

     **** Can &value be a variable name(0=No, 1=Yes)? &valid;

  %put;

  %put;

%mend checkvarname;

%checkvarname(valid_name)

%checkvarname( valid_name)

%checkvarname(invalid name)

%checkvarname(book_sales_results_for_past_five_years!)

The four calls to macro program CHECKVARNAME produce the following SAS log.

235  %checkvarname(valid_name)

**** Invalid character in position: 0 (0 means valid_name is okay)

**** Can valid_name be a variable name(0=No, 1=Yes)? 1

236  %checkvarname( valid_name)

**** Invalid character in position: 0 (0 means valid_name is okay)

**** Can valid_name be a variable name(0=No, 1=Yes)? 1

237  %checkvarname(invalid name)

**** Invalid character in position: 8 (0 means invalid name is okay)

**** Can invalid name be a variable name(0=No, 1=Yes)? 0

238  %checkvarname(book_sales_results_for_past_five_years!)

**** Invalid character in position: 39 (0 means book_sales_results_for_past_five_years! is okay)

**** Can book_sales_results_for_past_five_years! be a variable name(0=No, 1=Yes)? 0

Example 6.8:  Using %SYSFUNC to Apply a SAS Statistical Function to Macro Variable Values

This example uses %SYSFUNC to apply the SAS statistical function MEAN to four macro variable values and compute their mean. In addition to using a SAS language function in the macro programming environment, this example also illustrates several concepts of macro programming.

The values assigned to the four macro variables A, B, C, and D are treated as text values in the macro programming environment. However, note that the MEAN function interprets them as numbers. The %SYSEVALF function is not needed to temporarily convert the values to numbers in order to compute the mean. Note also that two periods follow &MEANSTAT in the %PUT statement. The first period terminates the macro variable reference. The second period appears in the text written to the SAS log.

%let a=1.5;

%let b=-2.0;

%let c=1.978;

%let d=-3.5;

%let meanstat=%sysfunc(mean(&a,&b,&c,&d));

%put ****** The mean of &a, &b, &c, and &d is &meanstat..;

After the above code is submitted, the following is written to the SAS log:

****** The mean of 1.5, -2.0, 1.978, and -3.5 is -0.5055.

A section earlier in this chapter describes the necessity of using the %EVAL and %SYSEVALF functions when you need to temporarily convert macro variable values to numbers to perform calculations. In Example 6.8, if you wanted to compute the mean using only macro language statements, you would need to use the %SYSEVALF function. You could not use the %EVAL function because the values include decimal places. Also, %EVAL would return an integer result, which would be inaccurate. Code that includes %SYSEVALF follows.

%let a=1.5;

%let b=-2.0;

%let c=1.978;

%let d=-3.5;

%let meanstat=%sysevalf( (&a+&b+&c+&d)/4);

%put ****** The mean of &a, &b, &c, and &d is &meanstat..;

After submitting this code, the macro processor writes the same text statement to the SAS log as the one generated by the code that uses %SYSFUNC and MEAN in the first group of statements in this example:

****** The mean of 1.5, -2.0, 1.978, and -3.5 is -0.5055.

Example 6.9: Using the %SYSFUNC Function to Apply Several SAS Language Functions That Obtain and Display Information about a Data Set

Example 6.9 uses the %SYSFUNC macro function and several SAS language file functions to obtain the last date and time that a data set was updated and to insert that descriptive information in the title of a report. It also uses %SYSFUNC to format the date/time value.

The name of the data set is assigned to macro variable DSNAME. The data set specified by the value of DSNAME is opened with the SAS language OPEN function. Then, the SAS language ATTRN function obtains the last update information by specifying the argument MODTE. The results of the ATTRN function are stored in the macro variable LASTUPDATE. Finally, the SAS language CLOSE function closes the data set.

The value returned by ATTRN is the SAS internal date/time value, and this value is formatted for display in the title with the DATETIME format. The format is applied to the macro variable value stored in LASTUPDATE with %SYSFUNC and the PUTN SAS language function. The second argument to PUTN is the format name DATETIME.

Note that none of the arguments to the SAS file functions are enclosed in quotation marks. This is because the macro facility is a text-handling language and it treats all values as text. The SAS language functions are underlined in this example.

%let dsname=books.ytdsales;

%let dsid=%sysfunc(open(&dsname));

%let lastupdate=%sysfunc(attrn(&dsid,modte));

%let rc=%sysfunc(close(&dsid));

proc report data=books.ytdsales nowd;

  title “Publisher List Report &sysdate9”;

  title2 “Last Update of &dsname: %sysfunc(putn(&lastupdate,datetime.))”;

  column publisher saleprice;

  define publisher / group;

  define saleprice / format=dollar11.2;

  rbreak after / summarize;

run;

Output 6.1 presents the output from Example 6.9.

Output 6.1 Output from Example 6.9

Autocall macro programs are uncompiled source code and text stored as entries in SAS libraries. SAS has written several of these useful macro programs and ships them with SAS software. Not all SAS sites, however, install this autocall macro library, and some autocall macro programs can be site-specific as well.

These macro programs can be used like macro functions in your macro programming. Many of the functions perform actions comparable to their similarly named SAS language counterpart. For example, one autocall macro program is %LOWCASE. This autocall macro program converts alphabetic characters in its argument to lowercase. Similarly, you could use %SYSFUNC and the LOWCASE SAS language function to do the same action. Chapter 10 discusses how you can save your macro programs in your own autocall libraries.

Table 6.7 lists several of the autocall macro programs. Autocall macro programs %CMPRES, %LEFT, and %LOWCASE each have a version that you should use if the result might contain a special character or mnemonic operator. The names of those autocall macro programs are: %QCMPRES, %QLEFT, and %QLOWCASE.

Table 6.7 Selected SAS supplied autocall macro programs

Function	Action
%CMPRES(text | text expression)	Remove multiple, leading, and trailing blanks from the argument. Use %QCMPRES if the result might contain a special character or mnemonic operator.
%DATATYP(text | text expression)	Returns the data type (CHAR or NUMERIC) of a value.
%LEFT(text | text expression)	Aligns an argument to the left by removing leading blanks. Use %QLEFT if the result might contain a special character or mnemonic operator.
%LOWCASE(text | text expression)	Changes a value from uppercase characters to lowercase. Use %QLOWCASE if the result might contain a special character or mnemonic operator.
%TRIM(text | text expression)	Remove trailing blanks from the argument. Use %QTRIM if the result might contain a special character or mnemonic operator.
%VERIFY(source | excerpt)	Returns the position of the first character unique to an expression.

Example 6.10: Determining with %VERIFY and %UPCASE If a Value Is in a Defined Set of Characters

This example examines the value of a macro variable to see if its value is a valid response to a survey question. Macro program CHECKSURVEY in the first section of code has one parameter, RESPONSE. The value of RESPONSE is examined, and the result of the examination is printed in the SAS log with the %PUT statement. A response to a survey question in this example must be a digit from 1 to 5 or 9, or a letter from A to E or Z.

The example converts the survey response value to uppercase with the %UPCASE macro function. It then examines the value with the %VERIFY autocall macro program. The %VERIFY autocall macro program returns the position in a text value of the first character that is not in the list supplied as the second argument. In this example, assume that survey responses are single characters, and only single characters will be specified as parameters to CHECKSURVEY. Therefore, the call to %VERIFY in this example can return only one of two values: zero (0) for a valid response and one (1) for an invalid response. The returned value of 1 corresponds to the first and only character specified as the parameter to CHECKSURVEY.

Since the value of macro variable RESPONSE is converted to uppercase, only the uppercase letters of the alphabet are specified in the list of valid responses assigned to the macro variable VALIDRESPONSES. Note that the string of valid values is not enclosed in quotation marks. Since %VERIFY is a macro program, it treats all values as text and therefore you do not enclose the text in quotation marks as you would when processing text in SAS language statements.

%macro checksurvey(response);

  %let validresponses=123459ABCDEZ;

  %let result=%verify(%upcase(&response),&validresponses);

  %put ******* Response &response is valid/invalid (0=valid 1=invalid): &result;

%mend checksurvey;

%checksurvey(f)

%checksurvey(a)

%checksurvey(6)

After submitting the preceding three calls to macro program CHECKSURVEY, the following is written to the SAS log:

175  %checksurvey(f)

******* Response f is valid/invalid (0=valid 1=invalid): 1

176  %checksurvey(a)

******* Response a is valid/invalid (0=valid 1=invalid): 0

177  %checksurvey(6)

******* Response 6 is valid/invalid (0=valid 1=invalid): 1

The same information can be obtained by using the %SYSFUNC macro function in conjunction with the VERIFY and UPCASE SAS language functions. The following program uses %SYSFUNC, VERIFY, and UPCASE.

Note that two calls to %SYSFUNC are made, once for each of the two SAS language functions. As mentioned at the beginning of this section, you cannot nest multiple calls to SAS language functions within one call to %SYSFUNC, but you can nest multiple %SYSFUNC calls.

This example nests only one %SYSFUNC call. When you need to have multiple %SYSFUNC calls, it might be easier to step through the processing by specifying multiple %LET statements rather than trying to nest several calls on one %LET statement. Doing so can prevent frustrating debugging tasks as you figure out the proper positioning of all the parentheses, commas, and arguments.

The SAS language functions are underlined in this revised version of Example 6.1.

%macro checksurvey(response);

  %let validresponses=123459ABCDEZ;

  %let result=

%sysfunc(verify(%sysfunc(upcase(&response)),&validresponses));

  %put ******* Response &response is valid/invalid (0=valid 1=invalid): &result;

%mend checksurvey;

%checksurvey(f)

%checksurvey(a)

%checksurvey(6)

After submitting the above statements, the following is written to the SAS log:

193  %checksurvey(f)

******* Response f is valid/invalid (0=valid 1=invalid): 1

194  %checksurvey(a)

******* Response a is valid/invalid (0=valid 1=invalid): 0

195  %checksurvey(6)

******* Response 6 is valid/invalid (0=valid 1=invalid): 1