Because the macro facility performs its tasks before SAS programs execute, the information that the macro facility supplies does not depend on values that are accessed or computed during the execution of a SAS program. However, sometimes it is necessary to access or create macro variables during the execution of a SAS program. There are several methods that enable the macro facility to create or access macro variables at execution time. In this chapter, you learn to use macro variables during execution of

In this chapter, you learn to

Before beginning this chapter, you should complete the following chapters:

In order to understand the problem with this example, you should consider how macro variable processing works in conjunction with SAS processing. Remember that when both macro language statements and SAS language statements occur in the same step, the macro processor executes macro language statements before any SAS language statements are executed.

Remember, you want to create a report that lists students who are enrolled in a specific course, according to data in the Sasuser.All data set, and you want to include a footnote in your report to indicate whether any student fees are unpaid. The following program uses SAS programming logic to determine which value is assigned to the macro variable foot. Then foot is referenced in the FOOTNOTE statement later in the program.

options symbolgen pagesize=30;
%let crsnum=3;
data revenue;
   set sasuser.all end=final;
   where course_number=&crsnum;
   total+1;
   if paid='Y' then paidup+1;
   if final then do;
      put total= paidup=; /* Write information
                            to the log. */
      if paidup<total then do;
         %let foot=Some Fees Are Unpaid; 
      end;
      else do; 
         %let foot=All Students Have Paid; 
      end;
   end;
run;


proc print data=revenue;
   var student_name student_company paid;
   title "Payment Status for Course &crsnum";
   footnote "&foot";
run;

In this example, the first %LET statement inside the DATA step is passed to the macro processor as soon as the word scanner encounters it. The macro processor then creates a macro variable named foot in the symbol table and assigns the value Some Fees Are Unpaid to the variable.

The word scanner then continues to read the program and passes the second %LET statement in the DATA step to the macro processor as well. This time, the macro processor reassigns the value All Students Have Paid to foot in the symbol table.

When the RUN statement in the DATA step is encountered, SAS recognizes that the step is complete, and executes it. Remember that at this point the DATA step no longer includes any of the %LET statements (which have already been executed by the macro processor). Because the %LET statements are always processed by the macro processor before the DATA step is executed, the value of foot will always be whatever the last %LET statement assigns.

Here is a representation of the program that is processed by the DATA step compiler as a result of the above code.

Table 10.1. Code After Substitution

We can solve this problem with the following information.

The DATA step provides functions and CALL routine that enable you to transfer information between an executing DATA step and the macro processor. You can use the SYMPUT routine to create a macro variable and to assign to that variable any value that is available in the DATA step.

When you use the SYMPUT routine to create a macro variable in a DATA step, the macro variable is not actually created and assigned a value until the DATA step is executed. Therefore, you cannot successfully reference a macro variable that is created with the SYMPUT routine by preceding its name with an ampersand within the same DATA step in which it is created.

In the next few sections you will see several examples of how the SYMPUT routine can be used in different situations.

In the SYMPUT routine, you use a literal string for

To use a literal with the SYMPUT routine, you enclose the literal string in quotation marks.

CALL SYMPUT('macro-variable', 'text');

Remember the previous example, in which you wanted to conditionally assign a value to the macro variable foot based on values that are generated during DATA step execution. You can use the SYMPUT routine with literal strings as both arguments in order to accomplish this.

options symbolgen pagesize=30;
%let crsnum=3;
data revenue;
   set sasuser.all end=final;
   where course_number=&crsnum;
   total+1;
   if paid='Y' then paidup+1;
   if final then do;
   if paidup<total then do;
      call symput('foot','Some Fees Are Unpaid'), 
   end;
   else do; 
      call symput('foot','All Students Have Paid'), 
   end;
end;
run;


proc print data=revenue;
   var student_name student_company paid;
   title "Payment Status for Course &crsnum";
   footnote "&foot";
run;

This time, the value assigned to foot is either Some Fees Are Unpaid or All Students Have Paid, depending on the value of the DATA step variable Paidup, because the value is assigned during the execution of the DATA step. When you submit this code, you get the following output.

You can assign the value of a DATA step variable as the value for a macro variable by using the DATA step variable's name as the second argument to the SYMPUT routine.

To use a DATA step variable as the value for a macro variable in the SYMPUT routine, you place the name of the DATA step variable after the name of the macro variable, separated by a comma. You do not enclose the name of the DATA step variable in quotation marks.

CALL SYMPUT('macro-variable',DATA-step-variable);

This form of the SYMPUT routine creates the macro variable named macro-variable and assigns to it the current value of DATA-step-variable.

When you use a DATA step variable as the second argument,

Once again, suppose you want to create a report about students who are enrolled in a particular course. This time, suppose you want to add a title that contains the course title and the course number, and you want to include a footnote that summarizes how many students have paid their fees.

In this example, a DATA step variable named paidup records the number of students that have paid, and a DATA step variable named total records the total number of students who are registered for the class. Macro variables are created to record the values of paidup, the value of total, and the value of Course_title. These macro variables are referenced later in the program.

%let crsnum=3;
data revenue;
   set sasuser.all end=final;
   where course_number=&crsnum;
   total+1;
   if paid='Y' then paidup+1;
   if final then do;
      call symput('numpaid',paidup); 
      call symput('numstu',total); 
      call symput('crsname',course_title); 
   end;
run;
proc print data=revenue noobs;
   var student_name student_company paid; 
   title "Fee Status for &crsname (#&crsnum)"; 
   footnote "Note: &numpaid Paid out of &numstu Students"; 
run;

This time the footnote shows the correct information for how many students have paid.

The SYMPUTX routine is very similar to the SYMPUT routine. In addition to creating a macro variable and assigning a value to it, the SYMPUTX routine also automatically removes leading and trailing blanks from both arguments. The SYMPUTX routine is available beginning in SAS 9.

Remember the example where you created a report about students who are enrolled in a particular course. This time, suppose you want the title to contain the course name and the course number, as well as the date on which the course was held. Also, you want the footnote to list the current amount of unpaid fees for the course.

In this example, three macro variables are created. The macro variable crsnum records the value of the DATA step variable Course_title. The macro variable date records the value of the DATA step variable Begin_date in MMDDYY10. format. Finally, the macro variable due uses the values of the DATA step variables paidup, total, and fee to record the current amount of unpaid fees in DOLLAR8. format. These macro variables are referenced later in the program in the title and footnote statements.

%let crsnum=3;
data revenue;
   set sasuser.all end=final;
   where course_number=&crsnum;
   total+1;
   if paid='Y' then paidup+1;
   if final then do;
      call symputx('crsname',course_title); 
      call symputx('date',put(begin_date,mmddyy10.)); 
      call symputx('due',put(fee*(total-paidup),dollar8.)); 
    end;
run;
proc print data=revenue;
   var student_name student_company paid; 
   title "Fee Status for &crsname (#&crsnum) Held &date"; 
   footnote "Note: &due in Unpaid Fees"; 
run;

If you had run the last example using listing output rather than HTML output, you would have seen extra blanks in the title between the course title and the course number, as well as in the footnote.

Table 10.2. SAS Listing Output

You do not see these blanks if you are using HTML output, but they are still stored in the value of your macro variable.

Remember that when a DATA step variable is used as the second argument in a SYMPUT routine, any leading or trailing blanks that are part of the DATA step variable's value are stored in the macro variable. Because the value of a macro variable is always a text string, numeric variables are automatically converted using the BEST12. format, and blanks are stored as part of the macro variable's value. In order to avoid including extra blanks, you need to use a DATA step function to remove them.

In these situations you can use DATA step functions before the SYMPUT routine executes, in order to

Often you will want to combine several DATA step functions in order to create a DATA step expression as the second argument of the SYMPUT routine.

CALL SYMPUT('macro-variable',expression);

When you use a DATA step expression as the second argument, its current value is evaluated according to the following rules:

In order to remove the extra blanks from the title and footnote of the previous example, you can use DATA step functions. To remove leading blanks from the macro variables numstu and numpaid, you can use the LEFT function to left-justify values and the TRIM function to remove any trailing blanks. To remove trailing blanks from crsname, you only need to use the TRIM function, because crsname was created from a character variable.

%let crsnum=3;
data revenue;
   set sasuser.all end=final;
   where course_number=&crsnum;
   total+1;
   if paid='Y' then paidup+1;
   if final then do;
      call symput('numpaid',trim(left(paidup))); 
      call symput('numstu',trim(left(total))); 
      call symput('crsname',trim(course_title)); 
   end;
run;


proc print data=revenue noobs;
   var student_name student_company paid;
   title "Fee Status for &crsname (#&crsnum)";
   footnote "Note: &numpaid Paid out of &numstu Students";
run;

Table 10.3. SAS Listing Output

Remember that the values of macro variables are always character strings. You have seen that in the DATA step the SYMPUT routine will perform automatic numeric-to-character conversion on any numeric value that you attempt to assign to a macro variable. Messages are written to the SAS log to alert you that automatic conversion has occurred. Remember that the SYMPUT routine automatically uses the BEST12. format for the conversion.

Sometimes you might want to have explicit control over the numeric-to-character conversion. The PUT function returns a character string that is formed by writing a value with a specified format.

You can use the PUT function to

Suppose you want to create a report that shows the amount of fees that are unpaid for a specific course. In the following example, you use the SYMPUT routine to format the value of the numeric variable Begin_date with the MMDDYY10. format and assign that value to the macro variable date. Then you also use another call to the SYMPUT routine to format the result of an expression involving Fee, total, and paidup as a dollar amount and assign that value to the macro variable due.

%let crsnum=3;
data revenue;
   set sasuser.all end=final;
   where course_number=&crsnum;
   total+1;
   if paid='Y' then paidup+1;
   if final then do;
     call symput('crsname',trim(course_title));
     call symput('date',put(begin_date,mmddyy10.)); 
     call symput('due', 
          trim(left(put(fee*(total-paidup),dollar8.)))); 
   end;
run;

You can use the macro variables date and due in a PROC PRINT step to create your report. The values of these macro variables appear in the report with the formatting that you assigned to them when you created them.

proc print data=revenue;
   var student_name student_company paid;
   title "Fee Status for &crsname (#&crsnum) Held &date"; 
   footnote "Note: &due in Unpaid Fees"; 
run;

Sometimes you might want to create multiple macro variables within one DATA step. For example, suppose you want to write a program that will list all of the scheduled dates for a particular course, using a macro variable to record the title of the course.

%let crsid=C005; 
data _null_;
   set sasuser.courses; 
   where course_code="&crsid"; 
   call symput('lsquo;title',trim(course_title)); 
run;


proc print data=sasuser.schedule noobs label; 
   where course_code="&crsid"; 
   var location begin_date teacher;
   title1 "Schedule for &title"; 

   options nodate nonumber;
run;

In this example, the value of the data set variable Course_title for the course whose Course_code is C005 is assigned as a value for the macro variable title.

In order to create a listing for a different course, you would need to change the %LET statement and resubmit the DATA step to assign a new value to title. Then you would need to resubmit the PROC PRINT step. Although you would need to resubmit both the DATA step and the PROC PRINT step, these two steps would be identical to the steps that you submitted for the first report. This is an extremely inefficient program.

%let crsid=C004; 
data _null_;
   set sasuser.courses; 
   where course_code=" &crsid"; 
   call symput('title',trim(course_title)); 
run;


proc print data=sasuser.schedule noobs label; 
   where course_code="&crsid"; 
   var location begin_date teacher;
   title1 "Schedule for &title"; 
   options nodate nonumber;
run;

Instead of executing separate DATA steps to update the same macro variable, you can create related macro variables in one DATA step. To create multiple macro variables, you use the SYMPUT routine with DATA step expressions for both arguments.

In this example, you use one call to the SYMPUT routine in order to create one macro variable for each value of the DATA step variable Course_code and to assign the corresponding value of Course_title to each macro variable. That is, for each observation in Sasuser.Courses, the macro processor will create a new macro variable. The new macro variable will have the same name as the value of the data set variable Course_code for that observation. The value of the new macro variable will be the value of the data set variable Course_title for that observation.

data _null_;
   set sasuser.courses;
   call symput(course_code, trim(course_title)); 

   run;

   %put _user_;

The SAS log shows that six observations were read from the data set Sasuser.Courses and that six global macro variables were created and were assigned values.

Table 10.4. SAS Log

You can then use these new macro variables to print listings of information for various courses, using only one DATA step, as follows:

data _null_;
   set sasuser.courses;
   call symput(course_code,trim(course_title));
run;


%let crsid=C005;
proc print data=sasuser.schedule noobs label;
   where course_code="&crsid";
   var location begin_date teacher;
   title1 "Schedule for &c005"; 
run;


%let crsid=C002;
proc print data=sasuser.schedule noobs label;
   where course_code="&crsid";
   var location begin_date teacher;
   title1 "Schedule for &c002"; 
run;

This is the output from the first PROC PRINT step.

This is the output from the second PROC PRINT step.

The program in this section is more efficient than the program shown in the previous section since the Sasuser.Courses data set is read only once in the latest example. However, there is still room for improvement.

In the last example, you saw how to use the SYMPUT routine to create a series of macro variables whose names are based on the values of Course_code. However, you still needed to modify the TITLE statement in each PROC PRINT step in order to print output for each course.

Suppose you want to write a PROC PRINT step that you can reuse without any modification to print information about each course. You can do this by using an indirect reference in the TITLE statement.

data _null_;
   set sasuser.courses;
   call symput(course_code,trim(course_title));
run;


%let crsid=C002;
proc print data=sasuser.schedule noobs label;
   where course_code="&crsid";
   var location begin_date teacher;
   title1 "Schedule for ???"; 
run;

In the example above, the macro variable C002 (as created by the SYMPUT routine) has a value of Structured Query Language. Therefore, the TITLE statement should reference a macro variable that will resolve to Structured Query Language. Remember that you want this reference to be flexible enough to apply to any of the macro variables that the SYMPUT routine creates, such as C003 or C004, by changing only the %LET statement.

To obtain the value Structured Query Language, you need to indirectly reference the macro variable C002 through a reference to the macro variable crsid. If the value of the macro variable crsid is C002, then you need to proceed in several steps:

This sequence seems to imply that you should use the reference &&crsid to convert the value of the macro variable crsid to the corresponding course description. However, the Forward Re-Scan rule indicates that this is not the correct solution.

The Forward Re-Scan rule can be summarized as follows:

According to the Forward Re-Scan rule, you need to use three ampersands in front of a macro variable name when its value matches the name of a second macro variable. This indirect reference resolves to the value of the second macro variable.

Suppose you want to use the macro variable crsid to indirectly reference the macro variable C002.

The following table shows several references along with their resolved values.

By preceding a macro variable reference with two ampersands, you delay the resolution of the reference until the second scan. The first time the reference is scanned, only the double ampersands will be resolved (to one ampersand). In order to create an indirect reference (a reference whose value is a reference to a different macro variable), you must use three ampersands. Therefore, to use an indirect reference that resolves to Structured Query Language, the original reference must be &&&crsid.

You can use indirect referencing to improve the last example. By using an indirect reference to the macro variable whose name is the same as the current value of the macro variable crsid, you can write a PROC PRINT step that you can reuse without modification in order to print a report for each different course.

options symbolgen;
data _null_;
   set sasuser.courses;
   call symput(course_code, trim(course_title));
run;


%let crsid=C005; 
proc print data=sasuser.schedule noobs label;
   where course_code="&crsid";
   var location begin_date teacher; 
   title1 "Schedule for &&&crsid"; 
run;


%let crsid=C002; 
proc print data=sasuser.schedule noobs label;
   where course_code="&crsid";
   var location begin_date teacher; 
   title1 "Schedule for &&&crsid"; 
run;

The SAS log shows the steps that lead to the resolution of these macro variables for each PROC PRINT step.

Table 10.5. SAS Log

This is the output from the first PROC PRINT step.

This is the output from the second PROC PRINT step.

Note that the PROC PRINT steps that produced these reports were identical. Only the %LET statement that precedes each PROC PRINT step and the resolved values of the macro variables changed.

Indirect referencing is especially useful when you are working with a series of related macro variables. In Chapter 9, "Introducing Macro Variables," on page 287, you learned how to combine multiple macro variable references in order to build new tokens. You can combine indirect macro variable references with other macro variable references as well. That is, you can use two ampersands in a reference when the value of one macro variable matches part of the name of a second macro variable.

You can create a series of macro variables, teach1 to teachn, each containing the name of the instructor who is assigned to a specific course.

options symbolgen;
data _null_;
   set sasuser.schedule;
   call symput('teach'||left(course_number),
               trim(teacher));
run;

Then, you can reference one of these variables when a course number is designated. If you designate a course number in a %LET statement, you can use multiple ampersands in order to create a reference to the teachn macro variable that corresponds to the current course number.

%let crs=3; 
proc print data=sasuser.register noobs;
   where course_number=&crs;
   var student_name paid;
   title1 "Roster for Course &crs"; 
   title2 "Taught by &&teach&crs"; 
run;

The SAS log shows the steps that lead to the resolution of the reference &&teach&crs.

Table 10.6. SAS LOG

This is the output from the example.

Earlier you learned how to use the SYMPUT routine to create macro variables in a DATA step. You are also familiar with using a macro variable reference such as &macvar to obtain the value of a macro variable before a DATA step executes. Now, suppose you want to obtain the value of a macro variable during DATA step execution. You can obtain a macro variable's value during DATA step execution by using the SYMGET function. The SYMGET function returns the value of an existing macro variable.

You can use the SYMGET function to obtain the value of a different macro variable for each iteration of a DATA step. In this example, the data set variable Teacher is assigned the value of the macro variable teachn for each observation in the Sasuser.Register data set, where n is the value of the data set variable Course_number for that observation.

Part of the SAS output that this program creates is shown below. Notice that the new data set Teachers contains a variable named Teacher and that the values of this variable are the same as the values of the macro variables teach1-teach3 in the global symbol table above.

You have seen how to create macro variables during DATA step execution. You can also create or update macro variables during the execution of a PROC SQL step. Remember that the SELECT statement in a PROC SQL step retrieves and displays data. The INTO clause in a SELECT statement enables you to create or update macro variables.

When you create or update macro variables during execution of a PROC SQL step, you might not want any output to be displayed. The PRINT | NOPPRINT option specifies whether a SELECT statement's results are displayed in output. PRINT is the default setting.

This form of the INTO clause does not trim leading or trailing blanks. Also, the INTO clause cannot be used when you create a table or a view.

You can create a macro variable named totalfee that contains the total of all course fees, and use this macro variable in a later step. You use the NOPRINT option to suppress the output from the PROC SQL step.

proc sql noprint;
   select sum(fee) format=dollar10. into :totalfee 
      from sasuser.all;
quit;
%let totalfee=&totalfee;


proc means data=sasuser.all sum maxdec=0;
   class  course_title;
      var fee; 
      title  "Grand Total for All Courses Is &totalfee"; 
run;

The output from this PROC MEANS step shows the sum of all course fees in the DOLLAR10. format.

Earlier you learned how to create a series of related macro variables during execution of the DATA step by using the SYMPUT routine. Sometimes you might want to create a series of related macro variables during execution of a PROC SQL step. You can use the INTO clause to create one new macro variable for each row in the result of the SELECT statement.

You can create a series of macro variables that contain the course code, location, and starting date of the first three courses that are scheduled in 2002. In this example, the macro variables crsid1-crsid3 are assigned values of the data set variable Course_code from each of the first three rows of the PROC SQL result:

proc sql;
   select course_code, location, begin_date format=mmddyy10.
   into :crsid1-:crsid3, 
        :place1-:place3, 
        :date1-:date3 
      from sasuser.schedule
      where year(begin_date)=2002
      order by begin_date;
quit;

This is the result of the PROC SQL step.

This is a representation of the symbol table after this PROC SQL step has run.

If you do not know how many macro variables will be created, you can issue a query to determine how many macro variables are needed and to create a macro variable to store that number. You can then run the query, using the macro variable as the suffix of the final macro variable in each series of macro variables.

Suppose you want to create ranges of macro variables that contain the course code, location, and starting date of all courses that are scheduled in 2002. You can issue a query to determine how many courses are listed in the Sasuser.Schedule data set. You can use the INTO clause to assign the resulting number to a macro variable named numrows. Then you can combine a reference to numrows with the text "crsid" in order to create the final macro variable in each series of macro variables that you create in a later SELECT statement.

proc sql noprint;
   select  count(*) into :numrows 
      from sasuser.schedule
      where year(begin_date)=2002; 
   %let numrows=&numrows; 
   %put There are &numrows courses in 2002;
   select course_code, location,
          begin_date format=mmddyy10. 
      into :crsid1-:crsid&numrows, 
           :place1-:place&numrows, 
           :date1-:date&numrows
      from sasuser.schedule
      where year(begin_date)=2002
      order by begin_date;
   %put _user_;
quit;

The SAS log shows that numrows is assigned a value of 4 in the first SELECT statement. The %PUT statement at the end of the program shows the names and values of all the macro variables that are created in the second SELECT statement.

Table 10.7. SAS Log

Sometimes, during execution of a PROC SQL step, you might want to create one macro variable that will hold all values of a certain data set variable. You can use an alternate form of the INTO clause in order to take all of the values of a column (variable) and concatenate them into the value of one macro variable.

You can use the SQL procedure to create one macro variable named sites that contains the names of all training centers that appear in the Sasuser.Schedule data set. The names will be separated by blanks.

proc sql noprint;
   select distinct location  into :sites separated by ' ' 
      from sasuser.schedule;
quit;

Here is a representation of the macro variable sites as it is stored in the global symbol table after this PROC SQL step has run.

Now you can use the new macro variable in a title.

proc means data=sasuser.all sum maxdec=0;
   var fee;
   title1 'Total Revenue';
   title2 "from Course Sites:  &sites"; 
run;

This is the output from the PROC MEANS step.

The SYMPUTN routine enables you to create a macro variable during execution of an SCL program and to assign a numeric value to it.

Suppose the SCL variable unitvar has a value of unit and the SCL variable unitnum has a numeric value of 200. To create a macro variable whose name is the value of unitvar (in this case, unit) and assign a value equal to the value of the SCL variable unitnum (in this case, 200) you submit the following statement within a SUBMIT block:

call symputn(unitvar, unitnum);

Similarly, to create a macro variable named unitvar and assign a numeric value of 500 to it, you submit the following statement within a SUBMIT block.

call symputn('unitvar', 500);

The SYMGETN function enables you to obtain the numeric value of a macro variable during execution of an SCL program.

Suppose the SCL variable unitvar has a value of unit, the macro variable unit has a value of 200, and the macro variable unitvar has a value of 500. The first statement below creates an SCL variable named unitnum and assigns to it a value of 200. The second statement creates an SCL variable named unit and assigns it a value of 500.

unitnum=symgetn(unitvar);
unit=symgetn('unitvar'),

CALL SYMPUT(macro-variable,text);

PUT(source,format.)

CALL SYMPUT(expression1,expression2);

CALL SYMPUTN('macro-variable', value);

CALL SYMPUTX(macro-variable,text);

SYMGET(macro-variable)

SYMGETN('macro-variable');

PROC SQL NOPRINT;

           SELECT column1,<column2,...>

                    INTO :macro-variable-1<,:macro-variable-2,...>

                    FROM table-1 | view-1

                    < WHERE expression>

                    < other clauses>;

QUIT;

PROC SQL NOPRINT;

           SELECT column1

                    INTO :macro-variable-1 - :macro-variable-n

                    FROM table-1 | view-1

                    < WHERE expression>

                    < other clauses>;

QUIT;

PROC SQL NOPRINT;

           SELECT column1

                    INTO :macro-variable-1

                    SEPARATED BY 'delimiter1'

                    FROM table-1 | view-1

                    < WHERE expression>

                    < other clauses>;

QUIT;