Syntax: SQL Procedure


Tip: Supports the Output Delivery System. See Output Delivery System on page 32 for details.

ODS Table Name : SQL_Results

Reminder: You can use any global statements. See Chapter 2, Fundamental Concepts for Using Base SAS Procedures, on page 15 for a list.

Reminder: You can use data set options any time a table name or view name is specified. See Using SAS Data Set Options with PROC SQL on page 1128 for details.

Note:

  • Regular type indicates the name of a component that is described in SQL Procedure Component Dictionary on page 1084.

  • view-name indicates a SAS data view of any type.

PROC SQL < option(s) >;

  • ALTER TABLE table-name

    • <ADD <CONSTRAINT> constraint-clause <, constraint-clause >>

    • <ADD column-definition<, column-definition>>

    • <DROP CONSTRAINT constraint-name <, constraint-name >>

    • <DROP column <, column >>

    • <DROP FOREIGN KEY constraint-name >

    • <DROP PRIMARY KEY >

    • <MODIFY column-definition<, column-definition>>

  • ;

  • CREATE < UNIQUE > INDEX index-name

    • ON table-name ( column <, column >);

  • CREATE TABLE table-name

    • ( column-specification <, column-specification constraint-specification >)

  • ;

  • CREATE TABLE table-name LIKE table-name2 ;

  • CREATE TABLE table-name AS query-expression

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

  • CREATE VIEW proc-sql-view AS query-expression

    • <ORDER BY order-by-item <, order-by-item >>

    • <USING libname-clause <, libname-clause >> ;

  • DELETE

    • FROM table-nameproc-sql-view sas/access-view < AS alias >

      • <WHERE sql-expression>;

  • DESCRIBE TABLE table-name <, table-name >;

  • DESCRIBE VIEW proc-sql-view <, proc-sql-view >;

  • DESCRIBE TABLE CONSTRAINTS table-name <, table-name >;

  • DROP INDEX index-name <, index-name >

    • FROM table-name ;

  • DROP TABLE table-name <, table-name >;

  • DROP VIEW view-name <, view-name >;

  • INSERT INTO table-namesas/access-viewproc-sql-view <( column <, column >)>

    • SET column =sql-expression

      • <, column =sql-expression>

    • <SET column =sql-expression

      • <, column =sql-expression>>;

  • INSERT INTO table-namesas/access-viewproc-sql-view <( column <, column >)>

    • VALUES ( value <, value >)

      • < VALUES ( value <, value >)>;

  • INSERT INTO table-name sas/access-viewproc-sql-view

    • <( column <, column >)> query-expression;

  • RESET < option(s) >;

  • 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 >>;

  • UPDATE table-namesas/access-viewproc-sql-view < AS alias >

    • SET column =sql-expression

      • <, column =sql-expression>

    • < SET column =sql-expression

      • <, column =sql-expression>>

    • < WHERE sql-expression>;

  • VALIDATE query-expression;

To connect to a DBMS and send it a DBMS-specific nonquery SQL statement, use this form:

  • PROC SQL ;

    • CONNECT TO dbms-name <AS alias >

      • <( connect-statement-argument-1=value <

      • connect-statement-argument-n=value >)>

      • <( database-connection-argument-1=value <

      • database-connection-argument-n=value >)>;

    • EXECUTE ( dbms-SQL-statement )

      • BY dbms-namealias ;

    • < DISCONNECT FROM dbms-namealias ;>

  • < QUIT ;>

To connect to a DBMS and query the DBMS data, use this form:

  • PROC SQL ;

    • CONNECT TO dbms-name <AS alias >

      • <( connect-statement-argument-1=value <

      • connect-statement-argument-n=value >)>

      • <( database-connection-argument-1=value <

      • database-connection-argument-n=value >)>;

    • SELECT column-list

      • FROM CONNECTION TO dbms-namealias

        • ( dbms-query )

        • optional PROC SQL clauses ;

    • < DISCONNECT FROM dbms-namealias ;>

  • < QUIT ;>

To do this

Use this statement

Modify, add, or drop columns

ALTER TABLE

Establish a connection with a DBMS

CONNECT TO

Create an index on a column

CREATE INDEX

Create a PROC SQL table

CREATE TABLE

Create a PROC SQL view

CREATE VIEW

Delete rows

DELETE

Display a definition of a table or view

DESCRIBE

Terminate the connection with a DBMS

DISCONNECT FROM

Delete tables, views, or indexes

DROP

Send a DBMS-specific nonquery SQL statement to a DBMS

EXECUTE

Add rows

INSERT

Reset options that affect the procedure environment without restarting the procedure

RESET

Select and execute rows

SELECT

Query a DBMS

CONNECTION TO

Modify values

UPDATE

Verify the accuracy of your query

VALIDATE

PROC SQL Statement

PROC SQL < option(s) >;

To do this

Use this option

Control output

 
 

Specify the buffer page size for the output

BUFFERSIZE=

 

Double-space the report

DOUBLENODOUBLE

 

Write a statement to the SAS log that expands the query

FEEDBACKNOFEEDBACK

 

Flow characters within a column

FLOWNOFLOW

 

Include a column of row numbers

NUMBERNONUMBER

 

Specify whether PROC SQL prints the query s result

PRINTNOPRINT

 

Specify whether PROC SQL should display sorting information

SORTMSGNOSORTMSG

 

Specify a collating sequence

SORTSEQ=

Control execution

 
 

Allow PROC SQL to use names other than SAS names

DQUOTE=

 

Specify whether PROC SQL should stop executing after an error

ERRORSTOPNOERRORSTOP

 

Specify whether PROC SQL should execute statements

EXEC NOEXEC

 

Restrict the number of input rows

INOBS=

 

Restrict the number of output rows

OUTOBS=

 

Restrict the number of loops

LOOPS=

 

Specify whether PROC SQL prompts you when a limit is reached with the INOBS=, OUTOBS=, or LOOPS= options

PROMPTNOPROMPT

 

Specify whether PROC SQL writes timing information for each statement to the SAS log

STIMERNOSTIMER

 

Override the SAS system option THREADSNOTHREADS

THREADSNOTHREADS

 

Specify how PROC SQL handles updates when there is an interruption

UNDO_POLICY=

Options

BUFFERSIZE= n n K n M n G

  • specifies the permanent buffer page size for the output in multiples of 1 (bytes), 1024 (kilobytes), 1,048,576 (megabytes), or 1,073,741,824 (gigabytes). For example, a value of 65536 specifies a page size of 65536 bytes, and a value of 64k specifies a page size of 65536 bytes.

  • BUFFERSIZE can also be specified in a RESET statement for use in particular queries.

  • Default: 0, which causes SAS to use the minimum optimal page size for the operating environment

DOUBLENODOUBLE

  • double-spaces the report.

  • Default: NODOUBLE

  • Featured in: Example 5 on page 1148

DQUOTE=ANSISAS

  • specifies whether PROC SQL treats values within double quotation marks (" ") as variables or strings. With DQUOTE=ANSI, PROC SQL treats a quoted value as a variable. This feature enables you to use the following as table names, column names, or aliases:

    • reserved words such as AS, JOIN, GROUP, and so on

    • DBMS names and other names that are not normally permissible in SAS.

  • The quoted value can contain any character.

  • With DQUOTE=SAS, values within double quotation marks are treated as strings.

  • Default: SAS

ERRORSTOPNOERRORSTOP

  • specifies whether PROC SQL stops executing if it encounters an error. In a batch or noninteractive session, ERRORSTOP instructs PROC SQL to stop executing the statements but to continue checking the syntax after it has encountered an error.

    NOERRORSTOP instructs PROC SQL to execute the statements and to continue checking the syntax after an error occurs.

  • Default: NOERRORSTOP in an interactive SAS session; ERRORSTOP in a batch or noninteractive session

  • Interaction: This option is useful only when the EXEC option is in effect.

  • Tip: ERRORSTOP has an effect only when SAS is running in the batch or noninteractive execution mode.

  • Tip: NOERRORSTOP is useful if you want a batch job to continue executing SQL procedure statements after an error is encountered.

EXECNOEXEC

  • specifies whether a statement should be executed after its syntax is checked for accuracy.

  • Default: EXEC

  • Tip: NOEXEC is useful if you want to check the syntax of your SQL statements without executing the statements.

  • See also: ERRORSTOP on page 1049

FEEDBACKNOFEEDBACK

  • specifies whether PROC SQL displays, in the SAS log, PROC SQL statements after view references are expanded or certain other transformations of the statement are made.

    This option has the following effects:

    • Any asterisk (for example, SELECT * ) is expanded into the list of qualified columns that it represents.

    • Any PROC SQL view is expanded into the underlying query.

    • Macro variables are resolved.

    • Parentheses are shown around all expressions to further indicate their order of evaluation.

    • Comments are removed.

  • Default: NOFEEDBACK

FLOW<= n < m >>NOFLOW

  • specifies that character columns longer than n are flowed to multiple lines. PROC SQL sets the column width at n and specifies that character columns longer than n are flowed to multiple lines. When you specify FLOW= n m , PROC SQL floats the width of the columns between these limits to achieve a balanced layout. Specifying FLOW without arguments is equivalent to specifying FLOW=12 200.

  • Default: NOFLOW

INOBS= n

  • restricts the number of rows (observations) that PROC SQL retrieves from any single source.

  • Tip: This option is useful for debugging queries on large tables.

LOOPS= n

  • restricts PROC SQL to n iterations through its inner loop. You use the number of iterations reported in the SQLOOPS macro variable (after each SQL statement is executed) to discover the number of loops. Set a limit to prevent queries from consuming excessive computer resources. For example, joining three large tables without meeting the join-matching conditions could create a huge internal table that would be inefficient to execute.

  • See also: Using Macro Variables Set by PROC SQL on page 1133

NODOUBLE

  • See DOUBLENODOUBLE on page 1049.

NOERRORSTOP

  • See ERRORSTOPNOERRORSTOP on page 1049.

NOEXEC

  • See EXECNOEXEC on page 1049.

NOFEEDBACK

  • See FEEDBACKNOFEEDBACK on page 1049.

NOFLOW

  • See FLOWNOFLOW on page 1050.

NONUMBER

  • See NUMBERNONUMBER on page 1051.

NOPRINT

  • See PRINTNOPRINT on page 1051.

NOPROMPT

  • See PROMPTNOPROMPT on page 1051.

NOSORTMSG

  • See SORTMSGNOSORTMSG on page 1051.

NOSTIMER

  • See STIMERNOSTIMER on page 1051.

NOTHREADS

  • See THREADSNOTHREADS.

NUMBERNONUMBER

  • specifies whether the SELECT statement includes a column called ROW, which is the row (or observation) number of the data as the rows are retrieved.

  • Default: NONUMBER

  • Featured in: Example 4 on page 1145

OUTOBS= n

  • restricts the number of rows (observations) in the output. For example, if you specify OUTOBS=10 and insert values into a table using a query-expression, then the SQL procedure inserts a maximum of 10 rows. Likewise, OUTOBS=10 limits the output to 10 rows.

PRINTNOPRINT

  • specifies whether the output from a SELECT statement is printed.

  • Default: PRINT

  • Tip: NOPRINT is useful when you are selecting values from a table into macro variables and do not want anything to be displayed.

  • Interaction: NOPRINT affects the value of the SQLOBS automatic macro variable. See Using Macro Variables Set by PROC SQL on page 1133 for details.

PROMPTNOPROMPT

  • modifies the effect of the INOBS=, OUTOBS=, and LOOPS= options. If you specify the PROMPT option and reach the limit specified by INOBS=, OUTOBS=, or LOOPS=, then PROC SQL prompts you to stop or continue. The prompting repeats if the same limit is reached again.

  • Default: NOPROMPT

SORTMSGNOSORTMSG

  • Certain operations, such as ORDER BY, may sort tables internally using PROC SORT. Specifying SORTMSG requests information from PROC SORT about the sort and displays the information in the log.

  • Default: NOSORTMSG

SORTSEQ= sort-table

  • specifies the collating sequence to use when a query contains an ORDER BY clause. Use this option only if you want a collating sequence other than your system s or installation s default collating sequence.

  • See also: SORTSEQ= option in SAS National Language Support (NLS): User s Guide .

STIMERNOSTIMER

  • specifies whether PROC SQL writes timing information to the SAS log for each statement, rather than as a cumulative value for the entire procedure. For this option to work, you must also specify the SAS system option STIMER. Some operating environments require that you specify this system option when you invoke SAS. If you use the system option alone, then you receive timing information for the entire SQL procedure, not on a statement-by-statement basis.

  • Default: NOSTIMER

THREADSNOTHREADS

  • overrides the SAS system option THREADSNOTHREADS for a particular invocation of PROC SQL. THREADSNOTHREADS can also be specified in a RESET statement for use in particular queries. When THREADS is specified, PROC SQL uses parallel processing in order to increase the performance of sorting operations that involve large amounts of data. For more information about parallel processing, see SAS Language Reference: Concepts .

  • Default: value of SAS system option THREADSNOTHREADS.

    Note: When THREADSNOTHREADS has been specified in a PROC SQL statement or a RESET statement, there is no way to reset the option to its default (that is, the value of the SAS system option THREADSNOTHREADS) for that invocation of PROC SQL.

UNDO_POLICY=NONEOPTIONALREQUIRED

  • specifies how PROC SQL handles updated data if errors occur while you are updating data. You can use UNDO_POLICY= to control whether your changes will be permanent:

  • NONE

    • keeps any updates or inserts.

  • OPTIONAL

    • reverses any updates or inserts that it can reverse reliably.

  • REQUIRED

    • reverses all inserts or updates that have been done to the point of the error. In some cases, the UNDO operation cannot be done reliably. For example, when a program uses a SAS/ACCESS view, it may not be able to reverse the effects of the INSERT and UPDATE statements without reversing the effects of other changes at the same time. In that case, PROC SQL issues an error message and does not execute the statement. Also, when a SAS data set is accessed through a SAS/SHARE server and is opened with the data set option CNTLLEV=RECORD, you cannot reliably reverse your changes.

      This option may enable other users to update newly inserted rows. If an error occurs during the insert, then PROC SQL can delete a record that another user updated. In that case, the statement is not executed, and an error message is issued.

  • Default: REQUIRED

Note: Options can be added, removed, or changed between PROC SQL statements with the RESET statement.

ALTER TABLE Statement

Adds columns to, drops columns from, and changes column attributes in an existing table. Adds, modifies, and drops integrity constraints from an existing table.

Restriction: You cannot use any type of view in an ALTER TABLE statement.

Restriction: You cannot use ALTER TABLE on a table that is accessed by an engine that does not support UPDATE processing.

Restriction: You must use at least one ADD, DROP, or MODIFY clause in the ALTER TABLE statement.

Featured in: Example 3 on page 1143

ALTER TABLE table-name

  • < ADD CONSTRAINT constraint-name constraint-clause <, constraint-name

    • constraint-clause >>

  • <ADD constraint-specification <, constraint-specification >>

  • <ADD column-definition<, column-definition>>

  • <DROP CONSTRAINT constraint-name <, constraint-name >>

  • <DROP column <, column >>

  • <DROP FOREIGN KEY constraint-name >

  • <DROP PRIMARY KEY >

  • <MODIFY column-definition<, column-definition>>

;

Arguments

< ADD CONSTRAINT constraint-name constraint-specification <, constraint-name constraint-specification >>

  • adds the integrity constraint that is specified in constraint-specification and assigns constraint-name to it.

< ADD constraint-specification <, constraint-specification >>

  • adds the integrity constraint that is specified in constraint-specification and assigns a default name to it. The default constraint name has the form that is shown in the following table:

    Default Name

    Constraint Type

    _NM xxxx _

    Not null

    _UN xxxx _

    Unique

    _CK xxxx _

    Check

    _PK xxxx _

    Primary key

    _FK xxxx _

    Foreign key

    In these default names, xxxx is a counter that begins at 0001.

< ADD column-definition<, column-definition>>

  • adds the column(s) that are specified in each column-definition.

column

  • names a column in table-name .

column-definition

  • See column-definition on page 1089.

constraint

  • is one of the following integrity constraints:

  • CHECK ( WHERE-clause )

    • specifies that all rows in table-name satisfy the WHERE-clause .

  • DISTINCT ( column <, column >)

    • specifies that the values of each column must be unique. This constraint is identical to UNIQUE.

  • FOREIGN KEY ( column <, column >)

      • REFERENCES table-name

    • <ON DELETE referential-action > <ON UPDATE referential-action > specifies a foreign key, that is, a set of columns whose values are linked to the values of the primary key variable in another table (the table-name that is specified for REFERENCES). The referential-actions are performed when the values of a primary key column that is referenced by the foreign key are updated or deleted.

    • Restriction: When defining overlapping primary key and foreign key constraints, which means that variables in a data file are part of both a primary key and a foreign key definition,

      • if you use the exact same variables, then the variables must be defined in a different order.

      • the foreign key s update and delete referential actions must both be RESTRICT.

  • NOT NULL ( column )

    • specifies that column does not contain a null or missing value, including special missing values.

  • PRIMARY KEY ( column <, column >)

    • specifies one or more primary key columns, that is, columns that do not contain missing values and whose values are unique.

    • Restriction: When you are defining overlapping primary key and foreign key constraints, which means that variables in a data file are part of both a primary key definition and a foreign key definition, if you use the exact same variables, then the variables must be defined in a different order.

  • UNIQUE ( column <, column >)

    • specifies that the values of each column must be unique. This constraint is identical to DISTINCT.

constraint-name

  • specifies a name for the constraint that is being specified. The name must be a valid SAS name.

    Note: The names PRIMARY, FOREIGN, MESSAGE, UNIQUE, DISTINCT, CHECK, and NOT cannot be used as values for constraint-name .

constraint-specification

  • consists of

    • constraint <MESSAGE= message-string <MSGTYPE= message-type >>

< DROP column <, column >>

  • deletes each column from the table.

< DROP CONSTRAINT constraint-name <, constraint-name >>

  • deletes the integrity constraint that is referenced by each constraint-name . To find the name of an integrity constraint, use the DESCRIBE TABLE CONSTRAINTS clause (see DESCRIBE Statement on page 1066).

< DROP FOREIGN KEY constraint-name >

  • Removes the foreign key constraint that is referenced by constraint-name .

    Note: The DROP FOREIGN KEY clause is a DB2 extension.

< DROP PRIMARY KEY >

  • Removes the primary key constraint from table-name .

    Note: The DROP PRIMARY KEY clause is a DB2 extension.

message-string

  • specifies the text of an error message that is written to the log when the integrity constraint is not met. The maximum length of message-string is 250 characters.

message-type

  • specifies how the error message is displayed in the SAS log when an integrity constraint is not met.

  • NEWLINE

    • the text that is specified for MESSAGE= is displayed as well as the default error message for that integrity constraint.

  • USER

    • only the text that is specified for MESSAGE= is displayed.

< MODIFY column-definition<, column-definition>>

  • changes one or more attributes of the column that is specified in each column-definition.

referential-action

  • specifies the type of action to be performed on all matching foreign key values.

  • CASCADE

    • allows primary key data values to be updated, and updates matching values in the foreign key to the same values. This referential action is currently supported for updates only.

  • RESTRICT

    • prevents the update or deletion of primary key data values if a matching value exists in the foreign key. This referential action is the default.

  • SET NULL

    • allows primary key data values to be updated, and sets all matching foreign key values to NULL.

table-name

    • in the ALTER TABLE statement, refers to the name of the table that is to be altered .

    • in the REFERENCES clause, refers to the name of table that contains the primary key that is referenced by the foreign key.

  • table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

WHERE-clause

  • specifies a SAS WHERE clause. Do not include the WHERE keyword in the WHERE clause.

Specifying Initial Values of New Columns

When the ALTER TABLE statement adds a column to the table, it initializes the column s values to missing in all rows of the table. Use the UPDATE statement to add values to the new column(s).

Changing Column Attributes

If a column is already in the table, then you can change the following column attributes by using the MODIFY clause: length, informat, format, and label. The values in a table are either truncated or padded with blanks (if character data) as necessary to meet the specified length attribute.

You cannot change a character column to numeric and vice versa. To change a column s data type, drop the column and then add it (and its data) again, or use the DATA step.

Note: You cannot change the length of a numeric column with the ALTER TABLE statement. Use the DATA step instead.

Renaming Columns

To change a column s name, you must use the SAS data set option RENAME=. You cannot change this attribute with the ALTER TABLE statement. RENAME= is described in the section on SAS data set options in SAS Language Reference: Dictionary .

Indexes on Altered Columns

When you alter the attributes of a column and an index has been defined for that column, the values in the altered column continue to have the index defined for them. If you drop a column with the ALTER TABLE statement, then all the indexes (simple and composite) in which the column participates are also dropped. See CREATE INDEX Statement on page 1057 for more information about creating and using indexes.

Integrity Constraints

Use ALTER TABLE to modify integrity constraints for existing tables. Use the CREATE TABLE statement to attach integrity constraints to new tables. For more information on integrity constraints, see the section on SAS files in SAS Language Reference: Concepts .

CONNECT Statement

Establishes a connection with a DBMS that is supported by SAS/ACCESS software.

Requirement: SAS/ACCESS software is required. For more information about this statement, refer to your SAS/ACCESS documentation.

See also: Connecting to a DBMS Using the SQL Procedure Pass-Through Facility on page 1129

CONNECT TO dbms-name <AS alias >

  • <( connect-statement-argument-1=value <
    connect-statement-argument-n=value >)>

  • <( database-connection-argument-1=value <
    database-connection-argument-n=value >)>;

Arguments

alias

  • specifies an alias that has 1 to 32 characters. The keyword AS must precede alias . Some DBMSs allow more than one connection. The optional AS clause enables you to name the connections so that you can refer to them later.

connect-statement-argument=value

  • specifies values for arguments that indicate whether you can make multiple connections, shared or unique connections, and so on, to the database. These arguments are optional, but if they are included, then they must be enclosed in parentheses. See SAS/ACCESS for Relational Databases: Reference for more information about these arguments.

database-connection-argument=value

  • specifies values for the DBMS-specific arguments that are needed by PROC SQL in order to connect to the DBMS. These arguments are optional for most databases, but if they are included, then they must be enclosed in parentheses. For more information, see the SAS/ACCESS documentation for your DBMS.

dbms-name

  • identifies the DBMS that you want to connect to (for example, ORACLE or DB2).

CREATE INDEX Statement

Creates indexes on columns in tables.

Restriction: You cannot use CREATE INDEX on a table that is accessed with an engine that does not support UPDATE processing.

CREATE < UNIQUE > INDEX index-name

  • ON table-name ( column <, column >);

Arguments

column

  • specifies a column in table-name .

index-name

  • names the index that you are creating. If you are creating an index on one column only, then index-name must be the same as column . If you are creating an index on more than one column, then index-name cannot be the same as any column in the table.

table-name

  • specifies a PROC SQL table.

Indexes in PROC SQL

An index stores both the values of a table s columns and a system of directions that enable access to rows in that table by index value. Defining an index on a column or set of columns enables SAS, under certain circumstances, to locate rows in a table more quickly and efficiently. Indexes enable PROC SQL to execute the following classes of queries more efficiently :

  • comparisons against a column that is indexed

  • an IN subquery where the column in the inner subquery is indexed

  • correlated subqueries, where the column being compared with the correlated reference is indexed

  • join-queries, where the join-expression is an equals comparison and all the columns in the join-expression are indexed in one of the tables being joined.

SAS maintains indexes for all changes to the table, whether the changes originate from PROC SQL or from some other source. Therefore, if you alter a column s definition or update its values, then the same index continues to be defined for it. However, if an indexed column in a table is dropped, then the index on it is also dropped.

You can create simple or composite indexes. A simple index is created on one column in a table. A simple index must have the same name as that column. A composite index is one index name that is defined for two or more columns. The columns can be specified in any order, and they can have different data types. A composite index name cannot match the name of any column in the table. If you drop a composite index, then the index is dropped for all the columns named in that composite index.

UNIQUE Keyword

The UNIQUE keyword causes SAS to reject any change to a table that would cause more than one row to have the same index value. Unique indexes guarantee that data in one column, or in a composite group of columns, remain unique for every row in a table. For this reason, a unique index cannot be defined for a column that includes NULL or missing values.

Managing Indexes

You can use the CONTENTS statement in the DATASETS procedure to display a table s index names and the columns for which they are defined. You can also use the DICTIONARY tables INDEXES, TABLES, and COLUMNS to list information about indexes. For more information, see Using the DICTIONARY Tables on page 1130.

See the section on SAS files in SAS Language Reference: Dictionary for a further description of when to use indexes and how they affect SAS statements that handle BY-group processing.

CREATE TABLE Statement

Creates PROC SQL tables.

Featured in: Example 1 on page 1139 and Example 2 on page 1141

 [1]  CREATE TABLE   table-name  (  column-specification  <,    column-specification   constraint-specification  >) ; [2]  CREATE TABLE   table-name   LIKE   table-name2  ; [3]  CREATE TABLE   table-name   AS  query-expression <  ORDER BY   order-by-item  <,    order-by-item  >>; 

Arguments

column-constraint

  • is one of the following:

  • CHECK ( WHERE-clause )

    • specifies that all rows in table-name satisfy the WHERE-clause .

  • DISTINCT

    • specifies that the values of the column must be unique. This constraint is identical to UNIQUE.

  • NOT NULL

    • specifies that the column does not contain a null or missing value, including special missing values.

  • PRIMARY KEY

    • specifies that the column is a primary key column, that is, a column that does not contain missing values and whose values are unique.

    • Restriction: When defining overlapping primary key and foreign key constraints, which means that variables in a data file are part of both a primary key and a foreign key definition, if you use the exact same variables, then the variables must be defined in a different order.

  • REFERENCES table-name

      • ON DELETE referential-action >< ON UPDATE referential-action >

    • specifies that the column is a foreign key, that is, a column whose values are linked to the values of the primary key variable in another table (the table-name that is specified for REFERENCES). The referential-actions are performed when the values of a primary key column that is referenced by the foreign key are updated or deleted.

    • Restriction: When you are defining overlapping primary key and foreign key constraints, which means that variables in a data file are part of both a primary key definition and a foreign key definition,

      • if you use the exact same variables, then the variables must be defined in a different order

      • the foreign key s update and delete referential actions must both be RESTRICT.

  • UNIQUE

    • specifies that the values of the column must be unique. This constraint is identical to DISTINCT.

  • Note: If you specify column-constraint , then SAS automatically assigns a name to the constraint. The constraint name has the form

    Default name

    Constraint type

    _CK xxxx _

    Check

    _FK xxxx _

    Foreign key

    _NM xxxx _

    Not Null

    _PK xxxx _

    Primary key

    _UN xxxx _

    Unique

  • where xxxx is a counter that begins at 0001.

column-definition

  • See column-definition on page 1089.

column-specification

  • consists of

    • column-definition < column-constraint >

constraint

  • is one of the following:

  • CHECK ( WHERE-clause )

    • specifies that all rows in table-name satisfy the WHERE-clause .

  • DISTINCT ( column <, column >)

    • specifies that the values of each column must be unique. This constraint is identical to UNIQUE.

  • FOREIGN KEY ( column <, column >)

      • REFERENCES table-name

      • ON DELETE referential-action > <ON UPDATE referential-action >

    • specifies a foreign key, that is, a set of columns whose values are linked to the values of the primary key variable in another table (the table-name that is specified for REFERENCES). The referential-actions are performed when the values of a primary key column that is referenced by the foreign key are updated or deleted.

    • Restriction: When you are defining overlapping primary key and foreign key constraints, which means that variables in a data file are part of both a primary key definition and a foreign key definition,

        • if you use the exact same variables, then the variables must be defined in a different order

        • the foreign key s update and delete referential actions must both be RESTRICT.

  • NOT NULL ( column )

    • specifies that column does not contain a null or missing value, including special missing values.

  • PRIMARY KEY ( column <, column >)

    • specifies one or more primary key columns, that is, columns that do not contain missing values and whose values are unique.

    • Restriction: When defining overlapping primary key and foreign ke constraints, which means that variables in a data file are part of both a primary key and a foreign key definition, if you use the exact same variables, then the variables must be defined in a different order.

  • UNIQUE ( column <, column >)

    • specifies that the values of each column must be unique. This constraint is identical to DISTINCT.

constraint-name

  • specifies a name for the constraint that is being specified. The name must be a valid SAS name.

    Note: The names PRIMARY, FOREIGN, MESSAGE, UNIQUE, DISTINCT, CHECK, and NOT cannot be used as values for constraint-name .

constraint-specification

  • consists of

    • CONSTRAINT constraint-name constraint <MESSAGE= message-string
      <MSGTYPE= message-type >>

message-string

  • specifies the text of an error message that is written to the log when the integrity constraint is not met. The maximum length of message-string is 250 characters.

message-type

  • specifies how the error message is displayed in the SAS log when an integrity constraint is not met.

  • NEWLINE

    • the text that is specified for MESSAGE= is displayed as well as the default error message for that integrity constraint.

  • USER

    • only the text that is specified for MESSAGE= is displayed.

ORDER BY order-by-item

  • sorts the rows in table-name by the values of each order-by-item . See ORDER BY Clause on page 1081.

query-expression

  • creates table-name from the results of a query. See query-expression on page 1107.

referential-action

  • specifies the type of action to be performed on all matching foreign key values.

  • CASCADE

    • allows primary key data values to be updated, and updates matching values in the foreign key to the same values. This referential action is currently supported for updates only.

  • RESTRICT

    • occurs only if there are matching foreign key values. This referential action is the default.

  • SET NULL

    • sets all matching foreign key values to NULL.

table-name

  • in the CREATE TABLE statement, refers to the name of the table that is to be created. You can use data set options by placing them in parentheses immediately after table-name . See Using SAS Data Set Options with PROC SQL on page 1128 for details.

  • in the REFERENCES clause, refers to the name of table that contains the primary key that is referenced by the foreign key.

table-name2

  • creates table-name with the same column names and column attributes as table-name2 , but with no rows.

WHERE-clause

  • specifies a SAS WHERE clause. Do not include the WHERE keyword in the WHERE clause.

Creating a Table without Rows

[1]  

The first form of the CREATE TABLE statement creates tables that automatically map SQL data types to those that are supported by SAS. Use this form when you want to create a new table with columns that are not present in existing tables. It is also useful if you are running SQL statements from an SQL application in another SQL-based database.

[2]  

The second form uses a LIKE clause to create a table that has the same column names and column attributes as another table. To drop any columns in the new table, you can specify the DROP= data set option in the CREATE TABLE statement. The specified columns are dropped when the table is created. Indexes are not copied to the new table.

Both of these forms create a table without rows. You can use an INSERT statement to add rows. Use an ALTER TABLE statement to modify column attributes or to add or drop columns.

Creating a Table from a Query Expression

[3]  

The third form of the CREATE TABLE statement stores the results of any query-expression in a table and does not display the output. It is a convenient way to create temporary tables that are subsets or supersets of other tables.

When you use this form, a table is physically created as the statement is executed. The newly created table does not reflect subsequent changes in the underlying tables (in the query-expression). If you want to continually access the most current data, then create a view from the query expression instead of a table. See CREATE VIEW Statement on page 1062.

CAUTION:

  • Recursive table references can cause data integrity problems. While it is possible to recursively reference the target table of a CREATE TABLE AS statement, doing so can cause data integrity problems and incorrect results. Constructions such as the following should be avoided:

     proc sql;     create table a as        select var1, var2        from a; 

Integrity Constraints

You can attach integrity constraints when you create a new table. To modify integrity constraints, use the ALTER TABLE statement. For more information on integrity constraints, see the section on SAS files in SAS Language Reference: Concepts .

CREATE VIEW Statement

Creates a PROC SQL view from a query-expression.

See also: What Are Views? on page 1044

Featured in: Example 8 on page 1157

CREATE VIEW proc-sql-view <( column-name-list )> AS query-expression

  • < ORDER BY order-by-item <, order-by-item >>

  • < USING libname-clause <, libname-clause >> ;

Arguments

column-name-list

  • is a comma-separated list of column names for the view, to be used in place of the column names or aliases that are specified in the SELECT clause. The names in this list are assigned to columns in the order in which they are specified in the SELECT clause. If the number of column names in this list does not equal the number of columns in the SELECT clause, then a warning is written to the SAS log.

query-expression

  • See query-expression on page 1107.

libname-clause

  • is one of the following:

    • LIBNAME libref < engine > SAS-data-library < option(s) >< engine-host-option(s) >

    • LIBNAME libref SAS/ACCESS-engine-name

      • < SAS/ACCESS-engine-connection-option(s) >

      • < SAS/ACCESS-engine-LIBNAME-option(s) >

  • See SAS Language Reference: Dictionary for information about the base SAS LIBNAME statement. See SAS/ACCESS for Relational Databases: Reference for information about the LIBNAME statement for relational databases.

order-by-item

  • See ORDER BY Clause on page 1081.

proc-sql-view

  • specifies the name for the PROC SQL view that you are creating. See What Are Views? on page 1044 for a definition of a PROC SQL view.

Sorting Data Retrieved by Views

PROC SQL enables you to specify the ORDER BY clause in the CREATE VIEW statement. When a view with an ORDER BY clause is accessed, and the ORDER BY clause directly affects the order of the results, its data is sorted and displayed as specified by the ORDER BY clause. However, if the ORDER BY clause does not directly affect the order of the results (for instance, if the view is specified as part of a join), then PROC SQL ignores the ORDER BY clause in order to enhance performance.

Note: If you specify the NUMBER option in the PROC SQL statement when you create your view, then the ROW column appears in the output. However, you cannot order by the ROW column in subsequent queries. See the description of NUMBERNONUMBER on page 1051.

Librefs and Stored Views

You can refer to a table name alone (without the libref) in the FROM clause of a CREATE VIEW statement if the table and view reside in the same SAS data library, as in this example:

 create view proclib.view1 as     select *        from invoice        where invqty>10; 

In this view, VIEW1 and INVOICE are stored permanently in the SAS data library referenced by PROCLIB. Specifying a libref for INVOICE is optional.

Updating Views

You can update a view s underlying data with some restrictions. See Updating PROC SQL and SAS/ACCESS Views on page 1135.

Embedded LIBNAME Statements

The USING clause enables you to store DBMS connection information in a view by embedding the SAS/ACCESS LIBNAME statement inside the view. When PROC SQL executes the view, the stored query assigns the libref and establishes the DBMS connection using the information in the LIBNAME statement. The scope of the libref is local to the view, and will not conflict with any identically named librefs in the SAS session. When the query finishes, the connection to the DBMS is terminated and the libref is deassigned.

The USING clause must be the last clause in the CREATE VIEW statement. Multiple LIBNAME statements can be specified, separated by commas. In the following example, a connection is made and the libref ACCREC is assigned to an ORACLE database.

 create view proclib.view1 as     select *        from accrec.invoices as invoices        using libname accrec oracle           user=  username  pass=  password  path='  dbms-path  '; 

For more information on the SAS/ACCESS LIBNAME statement, see the SAS/ACCESS documentation for your DBMS.

Note: Starting in SAS System 9, PROC SQL views, the Pass-Through Facility, and the SAS/ACCESS LIBNAME statement are the preferred ways to access relational DBMS data; SAS/ACCESS views are no longer recommended. You can convert existing SAS/ACCESS views to PROC SQL views by using the CV2VIEW procedure. See The CV2VIEW Procedure in SAS/ACCESS for Relational Databases: Reference for more information.

You can also embed a SAS LIBNAME statement in a view with the USING clause. This enables you to store SAS libref information in the view. Just as in the embedded SAS/ACCESS LIBNAME statement, the scope of the libref is local to the view, and it will not conflict with an identically named libref in the SAS session.

 create view work.tableview as     select * from proclib.invoices        using libname proclib  'sas-data-library'  ; 

DELETE Statement

Removes one or more rows from a table or view that is specified in the FROM clause.

Restriction: You cannot use DELETE FROM on a table that is accessed by an engine that does not support UPDATE processing.

Featured in: Example 5 on page 1148

DELETE

  • FROM table-namesas/access-viewproc-sql-view < AS alias >

    • < WHERE sql-expression>;

Arguments

alias

  • assigns an alias to table-name , sas/access-view , or proc-sql-view .

sas/access-view

  • specifies a SAS/ACCESS view that you are deleting rows from.

proc-sql-view

  • specifies a PROC SQL view that you are deleting rows from. proc-sql-view can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

sql-expression

  • See sql-expression on page 1113.

table-name

  • specifies the table that you are deleting rows from. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

CAUTION:

  • Recursive table references can cause data integrity problems. While it is possible to recursively reference the target table of a DELETE statement, doing so can cause data integrity problems and incorrect results. Constructions such as the following should be avoided:

     proc sql;     delete from a        where var1 > (select min(var2) from a); 

Deleting Rows through Views

You can delete one or more rows from a view s underlying table, with some restrictions. See Updating PROC SQL and SAS/ACCESS Views on page 1135.

CAUTION:

  • If you omit a WHERE clause, then the DELETE statement deletes all the rows from the specified table or the table that is described by a view.

DESCRIBE Statement

Displays a PROC SQL definition in the SAS log.

Restriction: PROC SQL views are the only type of view allowed in a DESCRIBE VIEW statement.

Featured in: Example 6 on page 1150

DESCRIBE TABLE table-name <, table-name >;

DESCRIBE VIEW proc-sql-view <, proc-sql-view >;

DESCRIBE TABLE CONSTRAINTS table-name <, table-name >;

Arguments

table-name

  • specifies a PROC SQL table. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

proc-sql-view

  • specifies a PROC SQL view. proc-sql-view can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

Details

  • The DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log for the table specified in the DESCRIBE TABLE statement, regardless of how the table was originally created (for example, with a DATA step). If applicable , SAS data set options are included with the table definition. If indexes are defined on columns in the table, then CREATE INDEX statements for those indexes are also written to the SAS log.

    When you are transferring a table to a DBMS that is supported by SAS/ACCESS software, it is helpful to know how it is defined. To find out more information about a table, use the FEEDBACK option or the CONTENTS statement in the DATASETS procedure.

  • The DESCRIBE VIEW statement writes a view definition to the SAS log. If you use a PROC SQL view in the DESCRIBE VIEW statement that is based on or derived from another view, then you might want to use the FEEDBACK option in the PROC SQL statement. This option displays in the SAS log how the underlying view is defined and expands any expressions that are used in this view definition. The CONTENTS statement in DATASETS procedure can also be used with a view to find out more information.

  • The DESCRIBE TABLE CONSTRAINTS statement lists the integrity constraints that are defined for the specified table(s).

DISCONNECT Statement

Ends the connection with a DBMS that is supported by a SAS/ACCESS interface.

Requirement: SAS/ACCESS software is required. For more information on this statement, refer to your SAS/ACCESS documentation.

See also: Connecting to a DBMS Using the SQL Procedure Pass-Through Facility on page 1129

DISCONNECT FROM dbms-namealias ;

Arguments

alias

  • specifies the alias that is defined in the CONNECT statement.

dbms-name

  • specifies the DBMS from which you want to end the connection (for example, DB2 or ORACLE). The name you specify should match the name that is specified in the CONNECT statement.

Details

  • An implicit COMMIT is performed before the DISCONNECT statement ends the DBMS connection. If a DISCONNECT statement is not submitted, then implicit DISCONNECT and COMMIT actions are performed and the connection to the DBMS is broken when PROC SQL terminates.

  • PROC SQL continues executing until you submit a QUIT statement, another SAS procedure, or a DATA step.

DROP Statement

Deletes tables, views, or indexes.

Restriction: You cannot use DROP TABLE or DROP INDEX on a table that is accessed by an engine that does not support UPDATE processing.

DROP TABLE table-name <, table-name >;

DROP VIEW view-name <, view-name >;

DROP INDEX index-name <, index-name >

  • FROM table-name ;

Arguments

index-name

  • specifies an index that exists on table-name .

table-name

  • specifies a PROC SQL table. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

view-name

  • specifies a SAS data view of any type: PROC SQL view, SAS/ACCESS view, or DATA step view. view-name can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

Details

  • If you drop a table that is referenced in a view definition and try to execute the view, then an error message is written to the SAS log that states that the table does not exist. Therefore, remove references in queries and views to any table(s) and view(s) that you drop.

  • If you drop a table with indexed columns, then all the indexes are automatically dropped. If you drop a composite index, then the index is dropped for all the columns that are named in that index.

  • You can use the DROP statement to drop a table or view in an external database that is accessed with the Pass-Through Facility or SAS/ACCESS LIBNAME statement, but not for an external database table or view that is described by a SAS/ACCESS view.

EXECUTE Statement

Sends a DBMS-specific SQL statement to a DBMS that is supported by a SAS/ACCESS interface.

Requirement: SAS/ACCESS software is required. For more information on this statement, refer to your SAS/ACCESS documentation.

See also: Connecting to a DBMS Using the SQL Procedure Pass-Through Facility on page 1129 and the SQL documentation for your DBMS.

EXECUTE ( dbms-SQL-statement )

  • BY dbms-namealias ;

Arguments

alias

  • specifies an optional alias that is defined in the CONNECT statement. Note that alias must be preceded by the keyword BY.

dbms-name

  • identifies the DBMS to which you want to direct the DBMS statement (for example, ORACLE or DB2).

dbms-SQL-statement

  • is any DBMS-specific SQL statement, except the SELECT statement, that can be executed by the DBMS-specific dynamic SQL.

Details

  • If your DBMS supports multiple connections, then you can use the alias that is defined in the CONNECT statement. This alias directs the EXECUTE statements to a specific DBMS connection.

  • Any return code or message that is generated by the DBMS is available in the macro variables SQLXRC and SQLXMSG after the statement completes.

INSERT Statement

Adds rows to a new or existing table or view.

Restriction: You cannot use INSERT INTO on a table that is accessed with an engine that does not support UPDATE processing.

Featured in: Example 1 on page 1139

 [1]  INSERT INTO   table-namesas/access-viewproc-sql-view  <(  column  <,    column  >) >  SET   column  =sql-expression     <,    column  =sql-expression> <  SET   column  =sql-expression     <,    column  =sql-expression>>; [2]  INSERT INTO   table-namesas/access-viewproc-sql-view  <(  column  <,    column  >)>  VALUES  (  value  <,    value  >)     <    VALUES  (  value  <,    value  >)>; [3]  INSERT INTO   table-name   sas/access-viewproc-sql-view  <(  column  <,    column  >)> query-expression; 

Arguments

column

  • specifies the column into which you are inserting rows.

proc-sql-view

  • specifies a PROC SQL view into which you are inserting rows. proc-sql-view can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

query-expression

  • See query-expression on page 1107.

sas/access-view

  • specifies a SAS/ACCESS view into which you are inserting rows.

sql-expression

  • See sql-expression on page 1113.

  • Restriction: You cannot use a logical operator (AND, OR, or NOT) in an expression in a SET clause.

table-name

  • specifies a PROC SQL table into which you are inserting rows. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

value

  • is a data value.

CAUTION:

  • Recursive table references can cause data integrity problems. While it is possible to recursively reference the target table of an INSERT statement, doing so can cause data integrity problems and incorrect results. Constructions such as the following should be avoided:

     proc sql;     insert into a        select var1, var2        from a        where var1 > 0; 

Methods for Inserting Values

[1]  

The first form of the INSERT statement uses the SET clause, which specifies or alters the values of a column. You can use more than one SET clause per INSERT statement, and each SET clause can set the values in more than one column. Multiple SET clauses are not separated by commas. If you specify an optional list of columns, then you can set a value only for a column that is specified in the list of columns to be inserted.

[2]  

The second form of the INSERT statement uses the VALUES clause. This clause can be used to insert lists of values into a table. You can either give a value for each column in the table or give values just for the columns specified in the list of column names. One row is inserted for each VALUES clause. Multiple VALUES clauses are not separated by commas. The order of the values in the VALUES clause matches the order of the column names in the INSERT column list or, if no list was specified, the order of the columns in the table.

[3]  

The third form of the INSERT statement inserts the results of a query-expression into a table. The order of the values in the query-expression matches the order of the column names in the INSERT column list or, if no list was specified, the order of the columns in the table.

Note: If the INSERT statement includes an optional list of column names, then only those columns are given values by the statement. Columns that are in the table but not listed are given missing values.

Inserting Rows through Views

You can insert one or more rows into a table through a view, with some restrictions. See Updating PROC SQL and SAS/ACCESS Views on page 1135.

Adding Values to an Indexed Column

If an index is defined on a column and you insert a new row into the table, then that value is added to the index. You can display information about indexes with

  • the CONTENTS statement in the DATASETS procedure. See CONTENTS Statement on page 319.

  • the DICTIONARY.INDEXES table. See Using the DICTIONARY Tables on page 1130 for more information.

For more information on creating and using indexes, see CREATE INDEX Statement on page 1057.

RESET Statement

Resets PROC SQL options without restarting the procedure.

Featured in: Example 5 on page 1148

RESET < option(s) >;

The RESET statement enables you to add, drop, or change the options in PROC SQL without restarting the procedure. See PROC SQL Statement on page 1048 for a description of the options.

SELECT Statement

Selects columns and rows of data from tables and views.

Restriction: The clauses in the SELECT statement must appear in the order shown.

See also: table-expression on page 1127, query-expression on page 1107

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.

See Also: column-definition on page 1089

Featured in: Example 1 on page 1139 and Example 2 on page 1141

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

Arguments

alias

  • assigns a temporary, alternate name to the column.

DISTINCT

  • eliminates duplicate rows.

  • Featured in: Example 13 on page 1168

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 on page 1087.

  • column-name << AS > alias >

      • <column-modifier < column-modifier>>

    • names a single column. See column-name on page 1091 and column-modifier on page 1090.

  • sql-expression < AS alias >

      • <column-modifier < column-modifier>>

    • derives a column from an sql-expression. See sql-expression on page 1113 and column-modifier on page 1090.

  • 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 data view that is specified in view-name .

  • view-alias .*

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

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, then use the LABEL= column-modifier, as described in column-modifier on page 1090. The keyword AS is not required with a column alias.

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.

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 also: Using Macro Variables Set by PROC SQL on page 1133

INTO macro-variable-specification

  • <, macro-variable-specification >

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-1 “ : macro-variable-n <NOTRIM>

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

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.

Details

  • Use the INTO clause only in the outer query of a SELECT statement and not in a subquery.

  • When storing a single value into a macro variable, PROC SQL preserves leading or trailing blanks. However, when storing values into a range of macro variables, or when using the SEPARATED BY option to store multiple values in one macro variable, PROC SQL trims leading or trailing blanks unless you use the NOTRIM option.

  • 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 that are produced by a query-expression. See Using Macro Variables Set by PROC SQL on page 1133 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  TWOSTORY      1940  TWOSTORY      1860 

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 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 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 noprint;     select distinct style        into :s1 separated by ','        from proclib.houses;  %put &s1; 

    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 
  • 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 one 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 the blanks to be trimmed , then add 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** 

FROM Clause

Specifies source tables or views.

Featured in: Example 1 on page 1139, Example 4 on page 1145, Example 9 on page 1159, and Example 10 on page 1162

FROM from-list

Arguments

alias

  • specifies a temporary, alternate name for a table, view, or in-line view that is specified in the FROM clause.

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.

from-list

  • is one of the following:

  • table-name << AS > alias >

    • names a single PROC SQL table. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

  • view-name << AS > alias >

    • names a single SAS data view. view-name can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

  • joined-table

    • specifies a join. See joined-table on page 1095.

  • (query-expression) < AS alias >

      • ( column <, column >)>

    • specifies an in-line view. See query-expression on page 1107.

  • CONNECTION TO

    • specifies a DBMS table. See CONNECTION TO on page 1092.

  • Note: With table-name and view-name , you can use data set options by placing them in parentheses immediately after table-name or view-name . See Using SAS Data Set Options with PROC SQL on page 1128 for details.

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. Column names in reflexive joins (joining a table with itself) must be prefixed with a table alias in order to distinguish which copy of the table the column comes from. 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 Example 10 on page 1162 for an example.

  • In order to visually separate an in-line view from the rest of the query, you can enclose the in-line view in any number of pairs of parentheses. Note that if you specify an alias for the in-line view, the alias specification must appear outside the outermost pair of parentheses for that in-line view.

WHERE Clause

Subsets the output based on specified conditions.

Featured in: Example 4 on page 1145 and Example 9 on page 1159

WHERE sql-expression

Argument

sql-expression

  • See sql-expression on page 1113.

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; 

GROUP BY Clause

Specifies how to group the data for summarizing.

Featured in: Example 8 on page 1157 and Example 12 on page 1166

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

Arguments

group-by-item

  • is one of the following:

  • integer

    • is a positive integer that equates to a column s position.

  • column-name

    • is the name of a column or a column alias. See column-name on page 1091.

  • sql-expression

    • See sql-expression on page 1113.

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 similar ways. If more than one group-by-item is specified, then 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, then 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. Note that if you use a floating-point value (for example, 2.3), then PROC SQL ignores the decimal portion.

  • The data does 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, then your clause is transformed into an ORDER BY clause and a message to that effect is written to the SAS log.

  • You can group the output by the values that are returned by an expression. For example, if X is a numeric variable, then the output of the following is grouped by the integer portion of values of X:

     select x, sum(y)  from table1  group by int(x); 

    Similarly, if Y is a character variable, then the output of the following is grouped by the second character of values of Y:

     select sum(x), y  from table1  group by substring(y from 2 for 1); 

    Note that an expression that contains only numeric literals (and functions of numeric literals) or only character literals (and functions of character literals) is ignored.

    An expression in a GROUP BY clause 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: Example 8 on page 1157 and Example 12 on page 1166

HAVING sql-expression

Argument

sql-expression

  • See sql-expression on page 1113.

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. Alternatively, if the query involves remerged data, then 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, then 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 Example 2 on page 1141) and groups the rows by Gender to determine the oldest employee of each gender. In SAS, dates are stored as integers. The lower the birth date as an integer, the greater the age. The expression birth=min(birth) is evaluated for each row in the table. When the minimum birth date 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 gender        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 on page 1124 for more information about summary functions and remerging data.

ORDER BY Clause

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

See also: query-expression on page 1107

Featured in: Example 11 on page 1164

ORDER BY order-by-item < ASC DESC ><, order-by-item < ASC DESC >>;

Arguments

order-by-item

  • is one of the following:

  • integer

    • equates to a column s position.

  • column-name

    • is the name of a column or a column alias. See column-name on page 1091.

  • sql-expression

    • See sql-expression on page 1113.

ASC

  • orders the data in ascending order. This is the default order; if neither ASC nor DESC is specified, the data is ordered in ascending order.

DESC

  • orders the data in descending order.

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 on page 1048.

  • If an ORDER BY clause is omitted, then a particular order to the output rows, such as the order in which the rows are encountered in the queried table, cannot be guaranteed . Without an ORDER BY clause, the order of the output rows is determined by the internal processing of PROC SQL, the default collating sequence of SAS, and your operating environment. Therefore, if you want your result table to appear in a particular order, then use the ORDER BY clause.

  • If more than one order-by-item is specified (separated by commas), then the first one determines the major sort order.

  • Integers can be substituted for column names (that is, SELECT object-items) in the ORDER BY clause. For example, if the order-by-item is 2 (an integer), then the results are ordered by the values of the second column. If a query-expression includes a set operator (for example, UNION), then use integers to specify the order. Doing so avoids ambiguous references to columns in the table expressions. Note that if you use a floating-point value (for example, 2.3) instead of an integer, then PROC SQL ignores the decimal portion.

  • 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 an expression. For example, if X is a numeric variable, then the output of the following is ordered by the integer portion of values of X:

     select x, y  from table1  order by int(x); 

    Similarly, if Y is a character variable, then the output of the following is ordered by the second character of values of Y:

     select x, y  from table1  order by substring(y from 2 for 1); 

    Note that an expression that contains only numeric literals (and functions of numeric literals) or only character literals (and functions of character literals) is ignored.

UPDATE Statement

Modifies a column s values in existing rows of a table or view.

Restriction: You cannot use UPDATE on a table that is accessed by an engine that does not support UPDATE processing.

Featured in: Example 3 on page 1143

UPDATE table-namesas/access-viewproc-sql-view < AS alias >

  • SET column =sql-expression

    • <, column =sql-expression>

  • SET column =sql-expression

    • <, column =sql-expression>>

  • WHERE sql-expression>;

Arguments

alias

  • assigns an alias to table-name , sas/access-view , or proc-sql-view .

column

  • specifies a column in table-name , sas/access-view , or proc-sql-view .

sas/access-view

  • specifies a SAS/ACCESS view.

sql-expression

  • See sql-expression on page 1113.

  • Restriction: You cannot use a logical operator (AND, OR, or NOT) in an expression in a SET clause.

table-name

  • specifies a PROC SQL table. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

proc-sql-view

  • specifies a PROC SQL view. proc-sql-view can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

Updating Tables through Views

You can update one or more rows of a table through a view, with some restrictions. See Updating PROC SQL and SAS/ACCESS Views on page 1135.

Details

  • Any column that is not modified retains its original values, except in certain queries using the CASE expression. See CASE expression on page 1087 for a description of CASE expressions.

  • To add, drop, or modify a column s definition or attributes, use the ALTER TABLE statement, described in ALTER TABLE Statement on page 1052.

  • In the SET clause, a column reference on the left side of the equal sign can also appear as part of the expression on the right side of the equal sign. For example, you could use this expression to give employees a $1,000 holiday bonus:

     set salary=salary + 1000 
  • If you omit the WHERE clause, then all the rows are updated. When you use a WHERE clause, only the rows that meet the WHERE condition are updated.

  • When you update a column and an index has been defined for that column, the values in the updated column continue to have the index defined for them.

VALIDATE Statement

Checks the accuracy of a query-expression s syntax and semantics without executing the expression.

VALIDATE query-expression;

Argument

query-expression

  • See query-expression on page 1107.

Details

  • The VALIDATE statement writes a message in the SAS log that states that the query is valid. If there are errors, then VALIDATE writes error messages to the SAS log.

  • The VALIDATE statement can also be included in applications that use the macro facility. When used in such an application, VALIDATE returns a value that indicates the query-expression s validity. The value is returned through the macro variable SQLRC (a short form for SQL return code). For example, if a SELECT statement is valid, then the macro variable SQLRC returns a value of 0. See Using Macro Variables Set by PROC SQL on page 1133 for more information.




Base SAS 9.1.3 Procedures Guide (Vol. 2)
Base SAS 9.1 Procedures Guide, Volumes 1, 2, 3 and 4
ISBN: 1590472047
EAN: 2147483647
Year: 2004
Pages: 142

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net