The SQL Procedure

SELECT Statement

Selects columns and rows of data from tables and views.

See also: table-expression, query-expression

SELECT <DISTINCT> object-item <,object-item>...

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

FROM from-list

<WHERE sql-expression>

<GROUP BY group-by-item
<, group-by-item>...>

<HAVING sql-expression>

<ORDER BY order-by-item
<,order-by-item>...>;

SELECT Clause

Lists the columns that will appear in the output.

SELECT <DISTINCT> object-item <,object-item>...

object-item is one of the following:

*

case-expression <AS alias>

column-name <AS alias>
<column-modifier <column-modifier>...>

sql-expression <AS alias>
<column-modifier <column-modifier>...>

table-name.*

table-alias.*

view-name.*

view-alias.*

Arguments

case-expression

See CASE expression.

column-modifier

See column-modifier.

column-name

See column-name.

DISTINCT

eliminates duplicate rows.

Featured in:

Producing All the Possible Combinations of the Values in a Column

sql-expression

See sql-expression.

table-alias

is an alias for a PROC SQL table.

table-name

specifies a PROC SQL table.

view-name

specifies any type of SAS data view.

view-alias

specifies the alias for any type of SAS data view.

Asterisk(*) Notation

The asterisk (*) represents all columns of the table(s) listed in the FROM clause. When an asterisk is not prefixed with a table name, all the columns from all tables in the FROM clause are included; when it is prefixed (for example, table-name.* or table-alias.*), all the columns from that table only are included.

Column Aliases

A column alias is a temporary, alternate name for a column. Aliases are specified in the SELECT clause to name or rename columns so that the result table is clearer or easier to read. Aliases are often used to name a column that is the result of an arithmetic expression or summary function. An alias is one word only. If you need a longer column name, use the LABEL= column-modifier, as described in column-modifier. The keyword AS is required with a column alias to distinguish the alias from other column names in the SELECT clause.

Column aliases are optional, and each column name in the SELECT clause can have an alias. After you assign an alias to a column, you can use the alias to refer to that column in other clauses.

If you use a column alias when creating a PROC SQL view, the alias becomes the permanent name of the column for each execution of the view.

INTO Clause

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

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

:macro-variable-specification is one of the following:

:macro-variable <SEPARATED BY 'character' <NOTRIM>>;

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

Arguments

macro-variable: specifies a SAS macro variable that stores the values of the rows that are returned.
NOTRIM: protects the leading and trailing blanks from being deleted from the macro variable value when the macro variables are created.
SEPARATED BY 'character': specifies a character that separates the values of the rows.

Details

Use macro-variable-specification only in the outer query of a SELECT statement and not in a subquery.
You can put multiple rows of the output into macro variables. You can check the PROC SQL macro variable SQLOBS to see the number of rows produced by a query-expression. See Using Macro Variables Set by PROC SQL for more information on SQLOBS.

Examples

These examples use the PROCLIB.HOUSES table:

The SAS System 1 Style SqFeet ------------------ CONDO 900 CONDO 1000 RANCH 1200 RANCH 1400 SPLIT 1600 SPLIT 1800 TWOSTORY 2100 TWOSTORY 3000

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

proc sql;
   select distinct style
      into :s1 separated by ','
      from proclib.houses;

%put &s1;

The results are written to the SAS log:

3 proc sql; 4 select distinct style 5 into :s1 separated by ',' 6 from proclib.houses; 7 8 %put &s1 CONDO,RANCH,SPLIT,TWOSTORY

The leading and trailing blanks are trimmed from the values before the macro variables are created. If you do not want the blanks to be trimmed, add NOTRIM, 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 Macro Variable Values.

Macro Variable Values

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* 10 %put *&style2* *&sqfeet*; *CONDO * * 900, 1000, 1200, 1400, 1600, 1800, 2100, 3000* 11 %put *&style3* *&sqfeet*; *RANCH * * 900, 1000, 1200, 1400, 1600, 1800, 2100, 3000* 12 %put *&style4* *&sqfeet*; *RANCH * * 900, 1000, 1200, 1400, 1600, 1800, 2100, 3000*

FROM Clause

Specifies source tables or views.

Featured in: Creating a Table and Inserting Data into It, Joining Two Tables, Joining Three Tables, and Querying an In-Line View

FROM from-list

from-list is one of the following:

table-name <<AS> alias>

view-name <<AS> alias>

joined-table

(query-expression) <<AS> alias
<(column <,column>...)>>

CONNECTION TO

Arguments

column: names the column that appears in the output. The column names that you specify are matched by position to the columns in the output.
CONNECTION TO: See CONNECTION TO.
joined-table: See joined-table.
query-expression: See query-expression.
table-name: specifies a PROC SQL table.
view-name: specifies any type of SAS data view.

Table Aliases

A table alias is a temporary, alternate name for a table that is specified in the FROM clause. Table aliases are prefixed to column names to distinguish between columns that are common to multiple tables. Table aliases are always required when joining a table with itself. Column names in other kinds of joins must be prefixed with table aliases or table names unless the column names are unique to those tables.

The optional keyword AS is often used to distinguish a table alias from other table names.

In-Line Views

The FROM clause can itself contain a query-expression that takes an optional table alias. This kind of nested query-expression is called an in-line view. An in-line view is any query-expression that would be valid in a CREATE VIEW statement. PROC SQL can support many levels of nesting, but it is limited to 32 tables in any one query. The 32-table limit includes underlying tables that may contribute to views that are specified in the FROM clause.

An in-line view saves you a programming step. Rather than creating a view and referring to it in another query, you can specify the view in-line in the FROM clause.

Characteristics of in-line views include the following:

An in-line view is not assigned a permanent name, although it can take an alias.
An in-line view can be referred to only in the query in which it is defined. It cannot be referenced in another query.
You cannot use an ORDER BY clause in an in-line view.
The names of columns in an in-line view can be assigned in the object-item list of that view or with a parenthesized list of names following the alias. This syntax can be useful for renaming columns. See Querying an In-Line View for an example.

WHERE Clause

Subsets the output based on specified conditions.

Featured in: Joining Two Tables and Joining Three Tables

WHERE sql-expression

Argument

sql-expression: See sql-expression.

Details

When a condition is met (that is, the condition resolves to true), those rows are displayed in the result table; otherwise, no rows are displayed.
You cannot use summary functions that specify only one column. For example:
```
where max(measure1) > 50;
```
However, this WHERE clause will work:
```
where max(measure1,measure2) > 50;
```

Writing Efficient WHERE Clauses

Here are some guidelines for writing efficient WHERE clauses that enable PROC SQL to use indexes effectively:

Avoid using LIKE predicates that begin with % or _:

/* inefficient:*/ where country like '%INA'
/* efficient: */  where country like 'A%INA'

Avoid using arithmetic expressions in a predicate:

/* inefficient:*/ where salary>12*4000
/* efficient: */  where salary>48000

First put the expression that returns the fewest number of rows. In the following query, there are fewer rows where miles>3800 than there are where boarded>100.
```
where miles>3800 and boarded>100
```

GROUP BY Clause

Specifies how to group the data for summarizing.

Featured in: Creating a View from a Query's Result and Joining Two Tables and Calculating a New Value

GROUP BY group-by-item <,group-by-item>...

group-by-item is one of the following:

integer

column-name

sql-expression

Arguments

integer: equates to a column's position.
column-name: See column-name.
sql-expression: See sql-expression.

Details

You can specify more than one group-by-item to get more detailed reports. Both the grouping of multiple items and the BY statement of a PROC step are evaluated in similiar ways. If more than one group-by-item is specified, the first one determines the major grouping.
Integers can be substituted for column names (that is, SELECT object-items) in the GROUP BY clause. For example, if the group-by-item is 2, the results are grouped by the values in the second column of the SELECT clause list. Using integers can shorten your coding and enable you to group by the value of an unnamed expression in the SELECT list.
The data do not have to be sorted in the order of the group-by values because PROC SQL handles sorting automatically. You can use the ORDER BY clause to specify the order in which rows are displayed in the result table.
If you specify a GROUP BY clause in a query that does not contain a summary function, your clause is transformed into an ORDER BY clause and a message to that effect is written to the SAS log.
A group-by-item cannot be a summary function. For example, the following GROUP BY clause is not valid:
```
   group by sum(x)
```

HAVING Clause

Subsets grouped data based on specified conditions.

Featured in: Creating a View from a Query's Result and Joining Two Tables and Calculating a New Value

HAVING sql-expression

Argument

sql-expression: See sql-expression.

Subsetting Grouped Data

The HAVING clause is used with at least one summary function and an optional GROUP BY clause to summarize groups of data in a table. A HAVING clause is any valid SQL expression that is evaluated as either true or false for each group in a query. Or, if the query involves remerged data, the HAVING expression is evaluated for each row that participates in each group. The query must include one or more summary functions.

Typically, the GROUP BY clause is used with the HAVING expression and defines the group(s) to be evaluated. If you omit the GROUP BY clause, the summary function and the HAVING clause treat the table as one group.

The following PROC SQL step uses the PROCLIB.PAYROLL table (shown in Creating a Table from a Query's Result) and groups the rows by SEX to determine the oldest employee of each sex. In SAS, dates are stored as integers. The lower the birthdate as an integer, the greater the age. The expression birth=min(birth)is evaluated for each row in the table. When the minimum birthdate is found, the expression becomes true and the row is included in the output.

proc sql;
   title 'Oldest Employee of Each Gender';
   select *
      from proclib.payroll
      group by sex
      having birth=min(birth);

Note: This query involves remerged data because the values returned by a summary function are compared to values of a column that is not in the GROUP BY clause. See Remerging Data for more information about summary functions and remerging data. [cautend]

ORDER BY Clause

Specifies the order in which rows are displayed in a result table.

See also: query-expression

Featured in: Retrieving Values with the SOUNDS-LIKE Operator

ORDER BY order-by-item <,order-by-item>...;

order-by-item is one of the following:

integer <ASC|DESC>

column-name <ASC|DESC>

sql-expression <ASC|DESC>

Arguments

ASC: orders the data in ascending order. This is the default order.
column-name: See column-name.
DESC: orders the data in descending order.
integer: equates to a column's position.
sql-expression: See sql-expression.

Details

The ORDER BY clause sorts the result of a query expression according to the order specified in that query. When this clause is used, the default ordering sequence is ascending, from the lowest value to the highest. You can use the SORTSEQ= option to change the collating sequence for your output. See PROC SQL Statement.
If an ORDER BY clause is omitted, the SAS System's default collating sequence and your operating environment determine the order of a result table's rows. Therefore, if you want your result table to appear in a particular order, use the ORDER BY clause.
Using an ORDER BY clause has certain performance costs, as does any sorting procedure. If you are querying large tables, and the order of their results is not important, your queries will run faster without an ORDER BY clause.
If more than one order-by-item is specified (separated by commas), the first one determines the major sort order. For example, if the order-by-item is 2 (an integer), the results are ordered by the values of the second column. If a query-expression includes a set operator (for example, UNION), use integers to specify the order. Doing so avoids ambiguous references to columns in the table expressions.
In the ORDER BY clause, you can specify any column of a table or view that is specified in the FROM clause of a query-expression, regardless of whether that column has been included in the query's SELECT clause. For example, this query produces a report ordered by the descending values of the population change for each country from 1990 to 1995:
```
proc sql;
   select country
      from census
      order by pop95-pop90 desc;
```
```
NOTE: The query as specified involves 
      ordering by an item that 
      doesn't appear in its SELECT clause.
```
You can order the output by the values that are returned by a function, for example:
```
proc sql;
   select *
      from measure
      order by put(pol_a,fmt_a.);
```

Chapter Contents
Previous
Next
Top of Page

See Also:	column-definition
Featured in:	Creating a Table and Inserting Data into It and Creating a Table from a Query's Result

See also:	query-expression
Featured in:	Retrieving Values with the SOUNDS-LIKE Operator