INTO Clause

Stores the value of one or more columns for use later in another PROC SQL query or SAS statement.

Restriction:	An INTO clause cannot be used in a CREATE TABLE statement.
See:	Using the PROC SQL Automatic Macro Variables

Syntax

Required Arguments

Details

Example

Syntax

INTO macro-variable-specification
<, … macro-variable-specification>

Required Arguments

macro-variable

specifies a SAS macro variable that stores the values of the rows that are returned.

macro-variable-specification

is one of the following:

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

stores the values that are returned into a single macro variable.

macro-variable<TRIMMED>

stores the values that are returned into a single macro variable.

macro-variable-1 - macro-variable-n<NOTRIM>

stores the values that are returned into a range of macro variables.

Tip When you specify a range of macro variables, the SAS Macro Facility creates only the number of macro variables that are needed. For example, if you specify :var1-:var9999 and only 55 variables are needed, only :var1-:var55 is created. The SQLOBS automatic variable is useful if a subsequent part of your program needs to know how many variables were actually created. In this example, SQLOBS would have the value of 55.

macro-variable-1 - <NOTRIM>

stores the values that are returned into a range of macro variables.

Tip	If you do not know how many variables you might need, you can create a macro variable range without specifying an upper bound for the range. The SQLOBS macro variable can be used if a subsequent part of your program needs to know how many variables were actually created.

NOTRIM

protects the leading and trailing blanks from being deleted from values that are stored in a range of macro variables or multiple values that are stored in a single macro variable.

SEPARATED BY 'character'

specifies a character that separates the values of the rows.

TRIMMED

trims the leading and trailing blanks from values that are stored in a single macro variable.

Details

Use the INTO clause only in the outer query of a SELECT statement, not in a subquery.
When storing a value in a single macro variable, PROC SQL preserves leading or trailing blanks. The TRIMMED option can be used to trim the leading and trailing blanks from values that are stored in a single macro variable. However, if values are stored in a range of macro variables, or if the SEPARATED BY option is used to store multiple values in a single macro variable, PROC SQL trims leading or trailing blanks unless you specify the NOTRIM option.
You can put multiple rows of the output in macro variables. You can use the PROC SQL macro variable SQLOBS to determine the number of rows that are produced by a query expression. For more information about SQLOBS, see Using the PROC SQL Automatic Macro Variables.

Note: The SQLOBS macro variable is assigned a value after the SELECT statement executes.
Values assigned by the INTO clause use the BEST8. format.

Example: INTO Clause

These examples use the PROCLIB.HOUSES table:

title 'PROCLIB.HOUSES Table';
proc sql;
   select * from proclib.houses;

PROCLIB.HOUSES Table

With the macro-variable-specification, you can do the following:

You can create macro variables based on the first row of the result.

proc sql noprint;
   select style, sqfeet
      into :style, :sqfeet
      from proclib.houses;

%put &style &sqfeet;

The results are written to the SAS log:

1   proc sql noprint;
2      select style, sqfeet
3         into :style, :sqfeet
4         from proclib.houses;
5
6   %put &style &sqfeet;
CONDO         900

You can use the TRIMMED option to remove leading and trailing blanks from values that are stored in a single macro variable.

proc sql noprint;
   select distinct style, sqfeet
      into :s1, :s2 TRIMMED
      from proclib.houses;
%put &s1 &s2;
%put There were &sqlobs distinct values.;

The results are written to the SAS log:

1     proc sql noprint;
2       select distinct style, sqfeet
3          into :s1, :s2 TRIMMED
4          from proclib.houses;
5    %put &s1 &s2;
CONDO    900
6    %put There were &sqlobs distinct values.;
There were 1 distinct values.

You can create one new macro variable per row in the result of the SELECT statement. This example shows how you can request more values for one column than for another. The hyphen is used in the INTO clause to imply a range of macro variables. You can use either of the keywords THROUGH or THRU instead of a hyphen.

The following PROC SQL step puts the values from the first four rows of the PROCLIB.HOUSES table into macro variables:
```
proc sql noprint;
select distinct Style, SqFeet
   into :style1 - :style3, :sqfeet1 - :sqfeet4
   from proclib.houses;

%put &style1 &sqfeet1;
%put &style2 &sqfeet2;
%put &style3 &sqfeet3;
%put &sqfeet4;
```
The %PUT statements write the results to the SAS log:
```
1   proc sql noprint;
2   select distinct style, sqfeet
3      into :style1 - :style3, :sqfeet1 - :sqfeet4
4      from proclib.houses;
5
6   %put &style1 &sqfeet1;
CONDO 900
7   %put &style2 &sqfeet2;
CONDO 1000
8   %put &style3 &sqfeet3;
RANCH 1200
9   %put &sqfeet4;
1400
```

You can use a hyphen in the INTO clause to specify a range without an upper bound.

proc sql noprint;
select distinct Style, SqFeet
   into :style1 - , :sqfeet1 - 
   from proclib.houses;

%put &style1 &sqfeet1;
%put &style2 &sqfeet2;
%put &style3 &sqfeet3;
%put &sqfeet4;

The results are written to the SAS log:

1 proc sql noprint;
2 select distinct Style, SqFeet
3       into :style1 - , :sqfeet1 -
4       from proclib.houses;
5
6   %put &style1 &sqfeet1;
CONDO 900
7   %put &style2 &sqfeet2;
CONDO 1000
8   %put &style3 &sqfeet3;
RANCH 1200
9   %put &sqfeet4;
1400

You can concatenate the values of one column into one macro variable. This form is useful for building up a list of variables or constants. The SQLOBS macro variable is useful to reveal how many distinct variables there were in the data processed by the query.
```
proc sql noprint;
   select distinct style
      into :s1 separated by ','
      from proclib.houses;
%put &s1;
%put There were &sqlobs distinct values.;
```
The results are written to the SAS log:
```
3    proc sql noprint;
4       select distinct style
5          into :s1 separated by ','
6          from proclib.houses;
7
8   %put &s1

CONDO,RANCH,SPLIT,TWOSTORY
There were 4 distinct values.
```

You can use leading zeros in order to create a range of macro variable names, as shown in the following example:

proc sql noprint;
   select SqFeet
      into :sqfeet01 - :sqfeet10
   from proclib.houses;

%put &sqfeet01 &sqfeet02 &sqfeet03 &sqfeet04 &sqfeet05;
%put &sqfeet06 &sqfeet07 &sqfeet08 &sqfeet09 &sqfeet10;

The results are written to the SAS log:

 11   proc sql noprint;
 12      select sqfeet
 13         into :sqfeet01 - :sqfeet10
 14      from proclib.houses;

15   %put &sqfeet01 &sqfeet02 &sqfeet03 &sqfeet04 &sqfeet05;
900 1000 1200 1400 1600
16   %put &sqfeet06 &sqfeet07 &sqfeet08 &sqfeet09 &sqfeet10;
1800 2100 3000 1940 1860

You can prevent leading and trailing blanks from being trimmed from values that are stored in macro variables. By default, when storing values in a range of macro variables, or when storing multiple values in a single macro variable (with the SEPARATED BY option), PROC SQL trims the leading and trailing blanks from the values before creating the macro variables. If you do not want leading and trailing blanks to be trimmed, specify the NOTRIM option, as shown in the following example:

proc sql noprint;
   select style, sqfeet
      into :style1 - :style4 notrim,
           :sqfeet separated by ',' notrim
      from proclib.houses;

%put *&style1* *&sqfeet*;
%put *&style2* *&sqfeet*;
%put *&style3* *&sqfeet*;
%put *&style4* *&sqfeet*;

The results are written to the SAS log, as shown in the following output:

3   proc sql noprint;
4      select style, sqfeet
5         into :style1 - :style4 notrim,
6              :sqfeet separated by ',' notrim
7         from proclib.houses;
8
9   %put *&style1* *&sqfeet*;
*CONDO   * *     900,    1000,    1200,    1400,    1600,    1800,    2100,
 3000,    1940,    1860*
10  %put *&style2* *&sqfeet*;
*CONDO   * *     900,    1000,    1200,    1400,    1600,    1800,    2100,
 3000,    1940,    1860**
11  %put *&style3* *&sqfeet*;
*RANCH   * *     900,    1000,    1200,    1400,    1600,    1800,    2100,
 3000,    1940,    1860**
12  %put *&style4* *&sqfeet*;
*RANCH   * *     900,    1000,    1200,    1400,    1600,    1800,    2100,
 3000,    1940,    1860**</log>
</logBlock>