|To do this
||Use this option
||Double-space the report
||Write a statement to the SAS log that expands the query
||Flow characters within a column
||Include a column of row numbers
||Specify whether PROC SQL prints the query's result
||Specify whether PROC SQL should display sorting information
||Specify a collating sequence
||Allow PROC SQL to use names other than SAS names
||Specify whether PROC SQL should stop executing after an error
||Specify whether PROC SQL should execute statements
||Restrict the number of input rows
||Restrict the number of output rows
||Restrict the number of loops
||Specify whether PROC SQL prompts you when a limit is reached with
the INOBS=, OUTOBS=, or LOOPS= options
||Specify whether PROC SQL writes timing information to the SAS log
||Specify how PROC SQL handles updates when there is an interruption
- double-spaces the report.
- specifies whether PROC SQL treats values within double-quotes
as variables or strings. With DQUOTE=ANSI, PROC SQL treats a quoted value
as a variable. This enables you to use the following as table names, column
names, or aliases:
The quoted value can contain any character.
- reserved words such as AS, JOIN, GROUP, and so on.
- DBMS names and other names not normally permissible in
With DQUOTE=SAS, values within quotes are treated as strings.
- 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.
||NOERRORSTOP in an interactive SAS session;
ERRORSTOP in a batch or noninteractive session
||This option is useful only when
the EXEC option is in effect.
||ERRORSTOP has an effect only when SAS is
running in the batch or noninteractive execution mode.
||NOERRORSTOP is useful if you want a batch
job to continue executing SQL procedure statements after an error is encountered.
- EXEC | NOEXEC
- specifies whether a statement should be executed after its
syntax is checked for accuracy.
||NOEXEC is useful if you want to check the
syntax of your SQL statements without executing the statements.
- specifies whether PROC SQL displays a statement after it
expands view references or makes certain transformations on the statement.
This option expands any use of an asterisk (for example,
SELECT *) into
the list of qualified columns that it represents. Any PROC SQL view is expanded
into the underlying query, and parentheses are shown around all expressions
to further indicate their order of evaluation.
- 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.
FLOW is equivalent to FLOW=12 200.
- restricts the number of rows (observations) that PROC SQL
retrieves from any single source.
||This option is useful for debugging queries
on large tables.
- 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 DOUBLE|NODOUBLE .
- See ERRORSTOP|NOERRORSTOP .
- See EXEC|NOEXEC .
- See FEEDBACK|NOFEEDBACK .
- See FLOW|NOFLOW .
- See NUMBER|NONUMBER .
- See PRINT|NOPRINT .
- See PROMPT|NOPROMPT .
- See SORTMSG|NOSORTMSG .
- See STIMER|NOSTIMER .
- specifies whether the SELECT statement includes a column
called ROW, which is the row (or observation) number of the data as they are
- 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, the SQL procedure inserts a maximum of 10 rows. Likewise,
OUTOBS=10 limits the output to 10 rows.
- specifies whether the output from a SELECT statement is
||NOPRINT is useful when you are selecting
values from a table into macro variables and do not want anything to be displayed.
- 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=, PROC SQL prompts you to stop or continue. The prompting
repeats if the same limit is reached again.
- 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.
- 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.
||SORTSEQ= option in SAS Language Reference: Dictionary.
- 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,
you receive timing information for the entire SQL procedure, not on a statement-by-statement
- 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:
- keeps any updates or inserts.
- reverses any updates or inserts that it can reverse reliably.
- undoes 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, PROC SQL can delete a record that another
user updated. In that case, the statement is not executed, and an error message
Note: Options can be added, removed, or
changed between PROC SQL statements with the RESET statement.
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.