SAS

SAS Data Sets

SELECT Statements

A SELECT statement can consist of the following basic clauses.

• SELECT

• INTO

• FROM

• JOIN

• WHERE

• GROUP BY

• HAVING

• UNION

• ORDER BY

• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT { [ TOP <numeric_literal> | DISTINCT ] { * | { <expression> [ [ AS ] <column_reference> ] | { <table_name> | <correlation_name> } .* } [ , ... ] } [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ] { FROM <table_reference> [ [ AS ] <identifier> ] } [ , ... ] [ [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] ] [ ... ] [ WHERE <search_condition> ] [ GROUP BY <column_reference> [ , ... ] [ HAVING <search_condition> ] [ UNION [ ALL ] <select_statement> ] [ ORDER BY <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ] [ LIMIT <expression> [ { OFFSET | , } <expression> ] ] } | SCOPE_IDENTITY() <expression> ::= | <column_reference> | @ <parameter> | ? | COUNT( * | { [ DISTINCT ] <expression> } ) | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) | NULLIF ( <expression> , <expression> ) | COALESCE ( <expression> , ... ) | CASE <expression> WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ] [ ELSE { <expression> | NULL } ] END | <literal> | <sql_function> <search_condition> ::= { <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ] } [ { AND | OR } ... ]

Examples

1. Return all columns:

   SELECT * FROM Account

2. Rename a column:

   SELECT [Name] AS MY_Name FROM Account

3. Cast a column's data as a different data type:

   SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Account

4. Search data:

   SELECT * FROM Account WHERE Industry = 'Floppy Disks'

5. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

   SELECT * FROM Account WHERE Industry = @param