SQL Clauses for Macros

INTO Clause

Assigns values produced by PROC SQL to macro variables.

Type:

SELECT statement, PROC SQL

Syntax
Details
Comparisons
Examples
	Example 1: Storing Column Values in Declared Macro Variables
	Example 2: Storing Row Values in a List of Macro Variables
	Example 3: Storing Values of All Rows in one Macro Variable

Syntax

INTO : macro-variable-specification-1 < ..., : macro-variable-specification-n>

macro-variable-specification

names one or more macro variables to create or update. Precede each macro variable name with a colon (:). The macro variable specification can be in any one or more of the following forms:

: macro-variable

specify one or more macro variables. Leading and trailing blanks are not trimmed from values before they are stored in macro variables:

select style, sqfeet
   into :type, :size
   from sasuser.houses;

:macro-variable-1 - : macro-variable-n <NOTRIM> :macro-variable-1 THROUGH : macro-variable-n <NOTRIM> :macro-variable-1 THRU : macro-variable-n <NOTRIM>

specifies a numbered list of macro variables. Leading and trailing blanks are trimmed from values before they are stored in macro variables. If you do not want the blanks to be trimmed, use the NOTRIM option. NOTRIM is an option in each individual element in this form of the INTO clause, so you can use it on one element and not on another element:

select style, sqfeet
   into :type1 - :type4 notrim, :size1 - :size3
   from sasuser.houses;

:macro-variable SEPARATED BY 'characters ' <NOTRIM>

specifies one macro variable to contain all the values of a column. Values in the list are separated by one or more characters. This form of the INTO clause is useful for building a list of items. Leading and trailing blanks are trimmed from values before they are stored in the macro variable. If you do not want the blanks to be trimmed, use the NOTRIM option. You can use the DISTINCT option in the SELECT statement to store only the unique column (variable) values:

select distinct style
   into :types separated by ','
   from sasuser.houses;

Details

The INTO clause for the SELECT statement can assign the result of a calculation or the value of a data column (variable) to a macro variable. If the macro variable does not exist, INTO creates it. You can check the PROC SQL macro variable SQLOBS to see the number of rows (observations) produced by a SELECT statement.

The INTO clause can be used only in the outer query of a SELECT statement and not in a subquery. The INTO clause cannot be used when you are creating a table (CREATE TABLE) or a view (CREATE VIEW).

Macro variables created with INTO follow the scoping rules for the %LET statement. For more information, see Scopes of Macro Variables.

Values assigned by the INTO clause use the BEST12. format.

Comparisons

In the SQL procedure, the INTO clause performs a role similar to the SYMPUT routine.

Examples

Example 1: Storing Column Values in Declared Macro Variables

This example is based on the data set SASUSER.HOUSES and stores the values of columns (variables) STYLE and SQFEET from the first row of the table (or observation in the data set) in macro variables TYPE and SIZE. The %LET statements strip trailing blanks from TYPE and leading blanks from SIZE because this type of specification with INTO does not strip those blanks by default.

proc sql noprint;
   select style, sqfeet
      into :type, :size
      from sasuser.houses;

%let type=&type;
%let size=&size;

%put The first row contains a &type with &size square feet.;

When this program executes, the following is written to the SAS log:

The first row contains a RANCH with 1250 square feet.

Example 2: Storing Row Values in a List of Macro Variables

This example creates two lists of macro variables, TYPE1 through TYPE4 and SIZE1 through SIZE4, and stores values from the first four rows (observations) of the SASUSER.HOUSES data set in them. The NOTRIM option for TYPE1 through TYPE4 retains the trailing blanks for those values.

proc sql noprint;
   select style, sqfeet
      into :type1 - :type4 notrim, :size1 - :size4
      from sasuser.houses;

%macro putit;
   %do i=1 %to 4;
      %put Row&i: Type=**&&type&i**   Size=**&&size&i**;
   %end;
%mend putit;

%putit

When this program executes, these lines are written to the SAS log:

Row1: Type=**RANCH   **  Size=**1250**
Row2: Type=**SPLIT   **  Size=**1190**
Row3: Type=**CONDO   **  Size=**1400**
Row4: Type=**TWOSTORY**  Size=**1810**

Example 3: Storing Values of All Rows in one Macro Variable

This example stores all values of the column (variable) STYLE in the macro variable TYPES and separates the values with a comma and a blank.

proc sql;
   select distinct quote(style)
      into :types separated by ', '
      from sasuser.houses;

%put Types of houses=&types.;

When this program executes, this line is written to the SAS log:

Types of houses=CONDO, RANCH, SPLIT, TWOSTORY

Top of Page