SAS 9.4 Macro Language, 5th Edition

Chapter 18

SQL Clauses for Macros

SQL Clauses for Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297

Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297

INTO Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297

SQL Clauses for Macros

Structured Query Language (SQL) is a standardized, widely used language for retrieving

and updating data in databases and relational tables.

Dictionary

INTO Clause

Assigns values produced by PROC SQL to macro variables.

Type: SELECT statement, PROC SQL

Syntax

INTO : macro-variable-specification-1 <, : macro-variable-specification-2 …>

Required Argument

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;

297

: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 “%LET Statement” on page 322.

Values assigned by the INTO clause use the BEST8. 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;

298 Chapter 18 • SQL Clauses for Macros