Base SAS 9.1 Procedures Guide, Volumes 1, 2, 3 and 4

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:

PROC SQL < option(s) >;

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

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

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

DOUBLENODOUBLE

DQUOTE=ANSISAS

ERRORSTOPNOERRORSTOP

EXECNOEXEC

FEEDBACKNOFEEDBACK

FLOW<= n < m >>NOFLOW

INOBS= n

LOOPS= n

NODOUBLE

NOERRORSTOP

NOEXEC

NOFEEDBACK

NOFLOW

NONUMBER

NOPRINT

NOPROMPT

NOSORTMSG

NOSTIMER

NOTHREADS

NUMBERNONUMBER

OUTOBS= n

PRINTNOPRINT

PROMPTNOPROMPT

SORTMSGNOSORTMSG

SORTSEQ= sort-table

STIMERNOSTIMER

THREADSNOTHREADS

UNDO_POLICY=NONEOPTIONALREQUIRED

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

;

Arguments

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

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

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

column

column-definition

constraint

constraint-name

constraint-specification

< DROP column <, column >>

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

< DROP FOREIGN KEY constraint-name >

< DROP PRIMARY KEY >

message-string

message-type

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

referential-action

table-name

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 >

Arguments

alias

connect-statement-argument=value

database-connection-argument=value

dbms-name

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

Arguments

column

index-name

table-name

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 :

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

column-definition

column-specification

constraint

constraint-name

constraint-specification

message-string

message-type

ORDER BY order-by-item

query-expression

referential-action

table-name

table-name2

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:

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

Arguments

column-name-list

query-expression

libname-clause

order-by-item

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

Arguments

alias

sas/access-view

proc-sql-view

sql-expression

table-name

CAUTION:

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:

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

proc-sql-view

Details

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

dbms-name

Details

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 >

Arguments

index-name

table-name

view-name

Details

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 )

Arguments

alias

dbms-name

dbms-SQL-statement

Details

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

proc-sql-view

query-expression

sas/access-view

sql-expression

table-name

value

CAUTION:

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

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 >

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

DISTINCT

object-item

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

Arguments

macro-variable

macro-variable-specification

NOTRIM

SEPARATED BY character

Details

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:

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

column

from-list

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:

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

Details

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

Details

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

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

ASC

DESC

Details

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 >

Arguments

alias

column

sas/access-view

sql-expression

table-name

proc-sql-view

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

VALIDATE Statement

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

VALIDATE query-expression;

Argument

query-expression

Details

Related

Категории