Chapter 8

Interfaces with the Macro Facility

Interfaces with the Macro Facility

An interface with the macro facility is not part of the macro processor but rather a SAS software feature that enables another portion of the SAS language to interact with the macro facility during execution. For example, a DATA step interface enables you to access macro variables from the DATA step. Macro facility interfaces are useful because, in general, macro processing happens before DATA step, SQL, SCL, or SAS/CONNECT execution. The connection between the macro facility and the rest of SAS is not usually dynamic. But by using an interface to the macro facility, you can dynamically connect the macro facility to the rest of SAS.

Note: The %SYSFUNC and %QSYSFUNC macro functions enable you to use SAS language functions with the macro processor. The %SYSCALL macro statement enables you to use SAS language CALL routines with the macro processor. These elements of the macro language are not considered true macro facility interfaces and they are discussed in this section. See “Macro Language Elements” on page 159 for more information about these macro language elements.

DATA Step Interfaces

Interacting with the Macro Facility during DATA Step Execution

DATA step interfaces consist of eight tools that enable a program to interact with the macro facility during DATA step execution. Because the work of the macro facility takes place before DATA step execution begins, information provided by macro statements has already been processed during DATA step execution. You can use one of the DATA step interfaces to interact with the macro facility during DATA step execution. You can use DATA step interfaces to do the following:

• pass information from a DATA step to a subsequent step in a SAS program

• invoke a macro based on information available only when the DATA step executes

• resolve a macro variable while a DATA step executes

• delete a macro variable

• pass information about a macro variable from the macro facility to the DATA step

The following table lists the DATA step interfaces by category and their uses.

Table 8.1 DATA Step Interfaces to the Macro Facility

Category	Tool	Description
Execution	CALL EXECUTE routine	Resolves its argument and executes the resolved value at the next step boundary (if the value is a SAS statement) or immediately (if the value is a macro language element).
Resolution	RESOLVE function	Resolves the value of a text expression during DATA step execution.
Deletion	CALL SYMDEL routine	Deletes the indicated macro variable named in the argument.
Information	SYMEXIST function	Returns an indication as to whether the macro variable exists.
Read or Write	SYMGET function	Returns the value of a macro variable during DATA step execution.
Information	SYMGLOBL function	Returns an indication as to whether the macro variable is global in scope.
Information	SYMLOCAL function	Returns an indication as to whether the macro variable is local in scope.
Read or Write	CALL SYMPUT routine	Assigns a value produced in a DATA step to a macro variable.

CALL EXECUTE Routine Timing Details

CALL EXECUTE is useful when you want to execute a macro conditionally. But you must remember that if CALL EXECUTE produces macro language elements, those elements execute immediately. If CALL EXECUTE produces SAS language statements, or if the macro language elements generate SAS language statements, those statements execute after the end of the DATA step’s execution.

Note: Macro references execute immediately and SAS statements do not execute until after a step boundary. You cannot use CALL EXECUTE to invoke a macro that contains references for macro variables that are created by CALL SYMPUT in that macro.

Example of Using CALL EXECUTE Incorrectly

In this example, the CALL EXECUTE routine is used incorrectly:

data prices;  /* ID for price category and actual price */
   input code amount;
   datalines;
56 300
99 10000
24 225
;

%macro items;
   %global special;
   %let special=football;
%mend items;

data sales;    /* incorrect usage */
   set prices;
   length saleitem $ 20;
   call execute('%items'),
   saleitem="&special";
run;

In the DATA SALES step, the assignment statement for SALEITEM requires the value of the macro variable SPECIAL at DATA step compilation. CALL EXECUTE does not produce the value until DATA step execution. Thus, you receive a message about an unresolved macro variable, and the value assigned to SALEITEM is &special.

In this example, it would be better to eliminate the macro definition (the %LET macro statement is valid in open code) or move the DATA SALES step into the macro ITEMS. In either case, CALL EXECUTE is not necessary or useful. Here is one version of this program that works:

data prices;  /* ID for price category and actual price */
   input code amount;
   datalines;
56 300
99 10000
24 225
;

%let special=football;  /* correct usage */

data sales;
   set prices;
   length saleitem $ 20;
   saleitem="&special";
run;

The %GLOBAL statement is not necessary in this version. Because the %LET statement is executed in open code, it automatically creates a global macro variable. (See “Scopes of Macro Variables” on page 47 for more information about macro variable scopes.)

Example of Common Problem with CALL EXECUTE

This example shows a common pattern that causes an error.

/* This version of the example shows the problem. */

data prices;         /* ID for price category and actual price */
   input code amount;
   cards;
56 300
99 10000
24 225
;
data names;      /* name of sales department and item sold */
   input dept $ item $;
   datalines;
BB  Boat
SK  Skates
;

%macro items(codevar=);  /* create macro variable if needed */
   %global special;
   data _null_;
      set names;
      if &codevar=99 and dept='BB' then call symput('special', item);
   run;
%mend items;

data sales;  /* attempt to reference macro variable fails */
   set prices;
   length saleitem $ 20;
   if amount > 500 then
      call execute('%items(codevar=' || code || ')' );
   saleitem="&special";
run;

In this example, the DATA SALES step still requires the value of SPECIAL during compilation. The CALL EXECUTE routine is useful in this example because of the conditional IF statement. But as in the first example, CALL EXECUTE still invokes the macro ITEMS during DATA step execution — not during compilation. The macro ITEMS generates a DATA _NULL_ step that executes after the DATA SALES step has ceased execution. The DATA _NULL_ step creates SPECIAL, and the value of SPECIAL is available after the _NULL_ step ceases execution, which is much later than when the value was needed.

This version of the example corrects the problem:

/* This version solves the problem. */

data prices;  /* ID for price category and actual price */
   input code amount;
   datalines;
56 300
99 10000
24 225
;

data names;  /* name of sales department and item sold */
   input dept $ item $;
   cards;
BB  Boat
SK  Ski
;
%macro items(codevar=);   /* create macro variable if needed */
   %global special;
   data _null_;
      set names;
      if &codevar=99 and dept='BB' then
         call symput('special', item);
   run;
%mend items;

data _null_;  /* call the macro in this step */
   set prices;
   if amount > 500 then
      call execute('%items(codevar=' || code || ')' );
run;

data sales;  /* use the value created by the macro in this step */
   set prices;
   length saleitem $ 20;
   saleitem="&special";
run;

This version uses one DATA _NULL_ step to call the macro ITEMS. After that step ceases execution, the DATA _NULL_ step generated by ITEMS executes and creates the macro variable SPECIAL. Then the DATA SALES step references the value of SPECIAL as usual.

Using SAS Language Functions in the DATA Step and Macro Facility

The macro functions %SYSFUNC and %QSYSFUNC can call SAS language functions and functions written with SAS/TOOLKIT software to generate text in the macro facility. %SYSFUNC and %QSYSFUNC have one difference: the %QSYSFUNC masks special characters and mnemonics and %SYSFUNC does not. For more information about these functions, see “%SYSFUNC and %QSYSFUNC Functions” on page 278.

%SYSFUNC arguments are a single SAS language function and an optional format. See the following examples:

%sysfunc(date(),worddate.)
%sysfunc(attrn(&dsid,NOBS))

You cannot nest SAS language functions within %SYSFUNC. However, you can nest %SYSFUNC functions that call SAS language functions, as in the following statement:

%sysfunc(compress(%sysfunc(getoption(sasautos)),%str(%)%(%')))

This example returns the value of the SASAUTOS= system option, using the COMPRESS function to eliminate opening parentheses, closing parentheses, and single quotation marks from the result. Note the use of the %STR function and the unmatched parentheses and quotation marks that are marked with a percent sign (%).

All arguments in SAS language functions within %SYSFUNC must be separated by commas. You cannot use argument lists preceded by the word OF.

Because %SYSFUNC is a macro function, you do not need to enclose character values in quotation marks as you do in SAS language functions. For example, the arguments to the OPEN function are enclosed in quotation marks when the function is used alone but do not require quotation marks when used within %SYSFUNC.

Here are some examples of the contrast between using a function alone and within %SYSFUNC:

• dsid = open("Sasuser.Houses","i");

• dsid = open("&mydata","&mode");

• %let dsid = %sysfunc(open(Sasuser.Houses,i));

• %let dsid = %sysfunc(open(&mydata,&mode));

You can use %SYSFUNC and %QSYSFUNC to call all of the DATA step SAS functions except the ones that are listed in table Table 17.2 on page 279. In the macro facility, SAS language functions called by %SYSFUNC can return values with a length up to 32K. However, within the DATA step, return values are limited to the length of a data set character variable.

The %SYSCALL macro statement enables you to use SAS language CALL routines with the macro processor, and it is described in “Macro Statements” on page 291.

Interfaces with the SQL Procedure

Using PROC SQL

Structured Query Language (SQL) is a standardized, widely used language for retrieving and updating data in databases and relational tables. SAS software’s SQL processor enables you to do the following:

• create tables and views

• retrieve data stored in tables

• retrieve data stored in SQL and SAS/ACCESS views

• add or modify values in tables

• add or modify values in SQL and SAS/ACCESS views

INTO Clause

SQL provides the INTO clause in the SELECT statement for creating SAS macro variables. You can create multiple macro variables with a single INTO clause. The INTO clause follows the same scoping rules as the %LET statement. See “Macro Variables” on page 21 for a summary of how macro variables are created. For further details and examples relating to the INTO clause, see “INTO Clause” on page 287.

Controlling Job Execution

PROC SQL also provides macro tools to do the following:

• stop execution of a job if an error occurs

• execute programs conditionally based on data values

The following table provides information about macro variables created by SQL that affect job execution.

Table 8.2 Macro Variables That Affect Job Execution

Macro Variable	Description
SQLEXITCODE	Contains the highest return code that occurred from some types of SQL insert failures. This return code is written to the SYSERR macro variable when PROC SQL terminates.
SQLOBS	Contains the number of rows or observations produced by a SELECT statement.
SQLOOPS	Contains the number of iterations that the inner loop of PROC SQL processes.
SQLRC	Contains the return code from an SQL statement. For return codes, see SAS SQL documentation.
SQLXMSG	Contains descriptive information and the DBMS-specific return code for the error that is returned by the pass-through facility.
SQLXRC	contains the DBMS-specific return code that is returned by the pass-through facility.

Interfaces with the SAS Component Language

Using an SCL Program

You can use the SAS macro facility to define macros and macro variables for an SCL program. Then, you can pass parameters between macros and the rest of the program. Also, through the use of the autocall and compiled stored macro facilities, macros can be used by more than one SCL program.

Note: Macro modules can be more complicated to maintain than a program segment because of the symbols and macro quoting that might be required. Also, implementing modules as macros does not reduce the size of the compiled SCL code. Program statements generated by a macro are added to the compiled code as if those lines existed at that location in the program.

The following table lists the SCL macro facility interfaces.

Table 8.3 SCL Interfaces to the Macro Facility

Category	Tool	Description
Read or Write	SYMGET	Returns the value of a global macro variable during SCL execution.
	SYMGETN	Returns the value of a global macro variable as a numeric value.
	CALL SYMPUT	Assigns a value produced in SCL to a global macro variable.
	CALL SYMPUTN	Assigns a numeric value to a global macro variable.

Note: It is inefficient to use SYMGETN to retrieve values that are not assigned with SYMPUTN. It is also inefficient to use & to reference a macro variable that was created with CALL SYMPUTN. Instead, use SYMGETN. In addition, it is inefficient to use SYMGETN and CALL SYMPUTN with values that are not numeric.

For details about these elements, see “DATA Step Call Routines for Macros” on page 231 and “DATA Step Functions for Macros” on page 241.

How Macro References Are Resolved by SCL

An important point to remember when using the macro facility with SCL is that macros and macro variable references in SCL programs are resolved when the SCL program compiles, not when you execute the application. To further control the assignment and resolution of macros and macro variables, use the following techniques:

• If you want macro variables to be assigned and retrieved when the SCL program executes, use CALL SYMPUT and CALL SYMPUTN in the SCL program.

• If you want a macro call or macro variable reference to resolve when an SCL program executes, use SYMGET and SYMGETN in the SCL program.

Referencing Macro Variables in Submit Blocks

In SCL, macro variable references are resolved at compile time unless they are in a Submit block. When SCL encounters a name prefixed with an ampersand (&) in a Submit block, it checks whether the name following the ampersand is the name of an SCL variable. If so, SCL substitutes the value of the corresponding variable for the variable reference in the submit block. If the name following the ampersand does not match any SCL variable, the name passes intact (including the ampersand) with the submitted statements. When SAS processes the statements, it attempts to resolve the name as a macro variable reference

To guarantee that a name is passed as a macro variable reference in submitted statements, precede the name with two ampersands (for example, &&DSNAME). If you have both a macro variable and an SCL variable with the same name, a reference with a single ampersand substitutes the SCL variable. To force the macro variable to be substituted, reference it with two ampersands (&&).

Considerations for Sharing Macros between SCL Programs

Sharing macros between SCL programs can be useful, but it can also raise some configuration management problems. If a macro is used by more than one program, you must keep track of all the programs that use it so that you can recompile all of them each time the macro is updated. Because SCL is compiled, each SCL program that calls a macro must be recompiled whenever that macro is updated.

CAUTION:

Recompile the SCL program. If you fail to recompile the SCL program when you update the macro, you run the risk of the compiled SCL being out of sync with the source.

Example Using Macros in an SCL Program

This SCL program is for an example application with the fields BORROWED, INTEREST, and PAYMENT. The program uses the macros CKAMOUNT and CKRATE to validate values entered into fields by users. The program calculates the payment, using values entered for the interest rate (INTEREST) and the sum of money (BORROWED).

/* Display an error message if AMOUNT */
   /* is less than zero or larger than 1000. */
%macro ckamount(amount);

   if (&amount < 0) or (&amount > 1000) then
      do;
         erroron borrowed;
         _msg_='Amount must be between $0 and $1,000.';
         stop;
      end;
   else erroroff borrowed;
%mend ckamount;

   /* Display an error message if RATE */
   /* is less than 0 or greater than 1.5 */
%macro ckrate(rate);
   if (&rate < 0) or (&rate > 1) then
      do;
         erroron interest;
         _msg_='Rate must be between 0 and 1.5';
         stop;
      end;
   else erroroff interest;
%mend ckrate;

   /*  Open the window with BORROWED at 0 and INTEREST at .5.  */
INIT:
   control error;
   borrowed=0;
   interest=.5;
return;

MAIN:
      /*  Run the macro CKAMOUNT to validate  */
      /*  the value of BORROWED.              */
   %ckamount(borrowed)
      /*  Run the macro CKRATE to validate  */
      /* the value of INTEREST.             */
   %ckrate(interest)
      /*  Calculate payment.  */
   payment=borrowed*interest;
return;

TERM:
return;

SAS/CONNECT Interfaces

Using %SYSRPUT with SAS/CONNECT

The %SYSRPUT macro statement is submitted with SAS/CONNECT to a remote host to retrieve the value of a macro variable stored on the remote host. %SYSRPUT assigns that value to a macro variable on the local host. %SYSRPUT is similar to the %LET macro statement because it assigns a value to a macro variable. However, %SYSRPUT assigns a value to a variable on the local host, not on the remote host where the statement is processed. The %SYSRPUT statement places the macro variable in the current scope of the local host.

Note: The names of the macro variables on the remote and local hosts must not contain a leading ampersand.

The %SYSRPUT statement is useful for capturing the value of the automatic macro variable SYSINFO and passing that value to the local host. SYSINFO contains return-code information provided by some SAS procedures. Both the UPLOAD and the DOWNLOAD procedures of SAS/CONNECT can update the macro variable SYSINFO and set it to a nonzero value when the procedure terminates due to errors. You can use %SYSRPUT on the remote host to send the value of the SYSINFO macro variable back to the local SAS session. Thus, you can submit a job to the remote host and test whether a PROC UPLOAD or DOWNLOAD step has successfully completed before beginning another step on either the remote host or the local host.

To use %SYSRPUT, you must have invoked a remote SAS windowing environment session by submitting the DMR option with the SAS command. For details about using %SYSRPUT, see the SAS/CONNECT documentation.

To create a new macro variable or to modify the value of an existing macro variable on a remote host or a server, use the %SYSLPUT macro statement.

Example Using %SYSRPUT to Check the Value of a Return Code on a Remote Host

This example illustrates how to download a file and return information about the success of the step. When remote processing is completed, the job checks the value of the return code stored in RETCODE. Processing continues on the local host if the remote processing is successful. In this example, the %SYSRPUT statement follows a PROC DOWNLOAD step, so the value returned by SYSINFO indicates the success of the PROC DOWNLOAD step:

/* This code executes on the remote host. */
rsubmit;
   proc download data=remote.mydata out=local.mydata;
   run;
         /* RETCODE is on the local host. */
         /* SYSINFO is on the remote host. */
   %sysrput retcode=&sysinfo;
endrsubmit;

   /* This code executes on the local host. */
%macro checkit;
   %if &retcode = 0 %then
      %do;
         further processing on local host
      %end;
%mend checkit;

%checkit

To determine the success or failure of a step executed on a remote host, use the %SYSRPUT macro statement to check the value of the automatic macro variable SYSERR.

For more details and syntax of the %SYSRPUT statement, see “%SYSRPUT Statement” on page 330.

Using %SYSLPUT with SAS/CONNECT

The %SYSLPUT statement is a macro statement that is submitted in the client session to assign a value that is available in the client session to a macro variable that can be accessed from the server session. If you are signed on to multiple server sessions, %SYSLPUT submits the macro assignment statement to the most recently used server session. If you are signed on to only one server session, %SYSLPUT submits the macro assignment statement to that server session. If you are not signed on to any session, an error condition results. Like the %LET statement, the %SYSLPUT statement assigns a value to a macro variable. Unlike %LET, the %SYSRPUT statement assigns a value to a variable in the server session rather than in the client session where the statement is executed. The %SYSRPUT statement stores the macro variable in the Global Symbol Table in the server session.

For details about using %SYSLPUT, see the SAS/CONNECT documentation.

Example Using %SYSLPUT

%SYSLPUT enables you to dynamically assign values to variables that are used by macros that are executed in a server session. The macro statement %SYSLPUT is used to create the macro variable REMID in the server session and to use the value of the client macro variable RUNID. The REMID variable is used by the %DOLIB macro, which is executed in a server session. This process finds out which operating system-specific library assignment should be used in the server session.

%macro assignlib (runid);

  signon rem &runid
  %syslput remid=&runid
  rsubmit rem &runid
     %macro dolib;
        %if (&runid eq 1) %then %do;
           libname mylib 'h:';
           %end;
        %else %if (&runid eq 2) %then %do;
           libname mylib '/afs/some/unix/path';
           %end;
     %mend;
     %dolib;
  endrsubmit;

%mend;