Chapter Contents

Previous

Next

INTO

Type:

SELECT statement, PROC SQL

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

specifies one or more macro variables. Leading and trailing blanks are not trimmed from values before they are stored in macro variables. For example,

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. For example,

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

:macro-variable SEPARATED BY 'character(s) ' <NOTRIM>

specifies one macro variable to contain all the values of a column. Values in the list are separated by character(s). 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 on the SELECT statement to store only the unique column (variable) values. For example,

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 Chapter 5, "Scope of Macro Variables," in SAS Macro Language: Reference.

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 Explicitly-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.;

Executing this program writes 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

Executing this program writes these lines 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.;

Executing this program writes this line to the SAS log:

Types of houses=CONDO, RANCH, SPLIT, TWOSTORY

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.