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.

When storing values into a range of macro variables, or when using the SEPARATED BY option to store multiple values in one macro variable, the INTO clause of PROC SQL trims any leading and trailing blanks. Use the NOTRIM option if you want the blanks to be preserved. This treatment of leading and trailing blanks is in contrast to assigning the value of a DATA step variable for a macro variable in the SYMPUT routine.

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 are 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 do not know the number of courses. If you assign an arbitrarily large number as the suffix of the final macro variable range, only macro variables corresponding to the query result set are created. The macro variable SQLOBS is assigned a value reflecting the number of rows in the result set, matching the number of macro variables created in each range.

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

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

114  proc sql noprint;
115    select course_code, location,
116        begin_date format=mmddyy10.
117      into :crsid1-:crsid999,
118        :place1-:place999,
119        :date1-:date999
120      from sasuser.schedule
121      where year(begin_date)=2002
122      order by begin_date;
123    %let numrows=&sqlobs;
124    %put There are &numrows courses in 2002;
There are 4 courses in 2002
125    %put _user_;
GLOBAL SQLOBS 4
GLOBAL CRSID2 C004
GLOBAL SQLOOPS 20
GLOBAL CRSID3 C005
GLOBAL DATE4 03/25/2002
GLOBAL PLACE1 Dallas
GLOBAL CRSID1 C003
GLOBAL PLACE2 Boston
GLOBAL PLACE3 Seattle
GLOBAL SYS_SQL_IP_ALL -1
GLOBAL SYS_SQL_IP_STMT
GLOBAL CRSNUM 3
GLOBAL DATE 01/08/2001
GLOBAL DATE1 01/07/2002
GLOBAL CRSID4 C006
GLOBAL DATE2 01/21/2002
GLOBAL DATE3 02/25/2002
GLOBAL NUMPAID 14
GLOBAL SQLXOBS 0
GLOBAL SQLRC 0
GLOBAL NUMROWS 4
GLOBAL NUMSTU 20
GLOBAL CRSNAME Local Area Networks
GLOBAL DUE $3,900
GLOBAL SQLEXITCODE 0
GLOBAL PLACE4 Dallas
126  quit;

Sometimes, during execution of a PROC SQL step, you might want to create one macro variable that holds 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.

This form of the INTO clause removes leading and trailing blanks from each value before performing the concatenation of values.

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 are 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.