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 < 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= |
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.
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>>
;
< 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.
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).
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.
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 .
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.
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 .
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 >)>;
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).
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 >);
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.
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.
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.
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.
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 >>;
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.
[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. |
[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;
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 .
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 >> ;
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.
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.
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.
You can update a view s underlying data with some restrictions. See Updating PROC SQL and SAS/ACCESS Views on page 1135.
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' ;
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>;
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);
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.
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 >;
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.
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).
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 ;
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.
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.
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 ;
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.
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.
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 ;
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.
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.
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;
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;
[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.
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.
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.
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.
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 >>;
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 >
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 .
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.
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.
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 >
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.
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.
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**
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
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.
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.
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.
Subsets the output based on specified conditions.
Featured in: Example 4 on page 1145 and Example 9 on page 1159
WHERE sql-expression
sql-expression
See sql-expression on page 1113.
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;
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 >
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.
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)
Subsets grouped data based on specified conditions.
Featured in: Example 8 on page 1157 and Example 12 on page 1166
HAVING sql-expression
sql-expression
See sql-expression on page 1113.
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.
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 >>;
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.
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.
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>;
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.
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.
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.
Checks the accuracy of a query-expression s syntax and semantics without executing the expression.
VALIDATE query-expression;
query-expression
See query-expression on page 1107.
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.