O'Reilly logo

SAS 9.4 Macro Language, 5th Edition by

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required