SELECT Clause

Lists the columns that will appear in the output.

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

Syntax

Required Arguments

Details

Asterisk (*) Notation

Column Aliases

Syntax

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

Required Arguments

alias

assigns a temporary, alternate name to the column.

DISTINCT

eliminates duplicate rows. The DISTINCT argument is identical to UNIQUE.

Note	DISTINCT works on the internal or stored value, not necessarily on the value as it is displayed. Numeric precision can cause multiple rows to be returned with values that appear to be the same.
Tips	A row is considered a duplicate when all of its values are the same as the values of another row. The DISTINCT argument applies to all columns in the SELECT list. One row is displayed for each existing combination of values.
Tips	If available, PROC SQL uses index files when processing SELECT DISTINCT statements.
Example	Producing All the Possible Combinations of the Values in a Column

object-item

is one of the following:

*

represents all columns in the tables or views that are listed in the FROM clause.

case-expression <AS alias>

derives a column from a CASE expression. See CASE Expression.

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

names a single column. See column-name and column-modifier.

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

derives a column from an sql-expression. See sql-expression and column-modifier.

table-name.*

specifies all columns in the PROC SQL table that is specified in table-name.

table-alias.*

specifies all columns in the PROC SQL table that has the alias that is specified in table-alias.

view-name.*

specifies all columns in the SAS view that is specified in view-name.

view-alias.*

specifies all columns in the SAS view that has the alias that is specified in view-alias.

UNIQUE

eliminates duplicate rows. The UNIQUE argument is identical to DISTINCT.

Note: Although the UNIQUE argument is identical to DISTINCT, it is not an ANSI standard.

Details

Asterisk (*) Notation

The asterisk (*) represents all columns of the table or tables 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.

Note: A warning will occur if you create an output table using the SELECT * syntax when columns with the same name exist in the multiple tables that are listed on the FROM clause. You can avoid the warning by using one of the following actions:

Individually list the desired columns in the SELECT statement at the same time as you omit the duplicate column names.
Use the RENAME= and DROP= data set options. In this example, the ID column is renamed tmpid.
```
proc sql;
   create table all(drop=tmpid) as
      select * from
         one, two(rename=(id=tmpid))
            where one.id=two.tmpid;
quit;
```
If table aliases are used, place the RENAME= data set option after the table name and before the table alias. You can omit the DROP= data set option if you want to keep the renamed column in the final output table.

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, then use the LABEL= column-modifier, as described in column-modifier. The keyword AS is required with a column alias to distinguish the column alias from 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, then the alias becomes the permanent name of the column for each execution of the view.

Note: For the maximum portability of SQL code that is used outside of the SAS SQL procedure, avoid writing code that refers to column aliases in a WHERE clause, GROUP BY clause, or HAVING clause. For more information, see Column Alias Exceptions.