What's New in the SAS 9.4 SQL Procedure
- Overview
- Additional SAS SQL System Option and PROC SQL Statement Options
- Support Added for Linguistic Collation
- Support Added for Extended Attributes in DICTIONARY Tables
- Replaced BUFFERSIZE PROC SQL Statement Option
- Support Added for the SQLXOPENERRS Macro Variable
- Support Added for the SQL_PUSHTCINTOVIEW_FROM_OUTSIDE System Option
- Support Added for Implicit Pass Through
- Documentation Enhancements
Overview
Here are the new features and enhancements:
- additional SAS SQL system option and PROC SQL statement options
- support added for linguistic collation
- support added for extended attributes in DICTIONARY tables
- replaced BUFFERSIZE PROC SQL statement option
- support added for the SQLXOPENERRS macro variable
- support added for the SQL_PUSHTCINTOVIEW_FROM_OUTSIDE system option
- support added for implicit pass through
- documentation enhancements
Additional SAS SQL System Option and PROC SQL Statement Options
The following SQL options have been added to enable PROC SQL to terminate an SQL query if an implicit pass-through request fails:
- SQLIPONEATTEMPT system option
- In SAS 9.4M3, DICTDIAG | NODICTDIAG PROC SQL statement options
For more information, see SQLIPONEATTEMPT System Option and PROC SQL Statement.
Support Added for Linguistic Collation
Support has been added for linguistic collation with the SORTSEQ statement option. For more information, see SORTSEQ=sort-table | LINGUISTIC.
Support Added for Extended Attributes in DICTIONARY Tables
Support has been added to copy extended attribute information in DICTIONARY tables. The XATTRS DICTIONARY table and Vxattr Sashelp view have been added. For more information, see Accessing SAS Information By Using DICTIONARY Tables.
Replaced BUFFERSIZE PROC SQL Statement Option
The BUFFERSIZE option for the PROC SQL statement has been replaced with the UBUFSIZE option. The BUFFERSIZE option that was used prior to SAS 9.4 works the same as the UBUFSIZE option and is still supported. As of SAS 9.4, UBUFSIZE is the preferred option. For more information, see PROC SQL Statement.
Support Added for the SQLXOPENERRS Macro Variable
SAS 9.4M3 adds the SQLXOPENERRS macro variable, which contains the number of DICTIONARY tables that failed to open. For more information, see SQLXOPENERRS.
Support Added for the SQL_PUSHTCINTOVIEW_FROM_OUTSIDE System Option
SAS Viya 3.5 adds support for the SQL_PUSHTCINTOVIEW_FROM_OUTSIDE system option, which applies a WHERE clause that contains a truncated comparison operator to a view. The operator then returns only the names in the view that begin with the complete specified value of the operator. For more information, see SQL_PUSHTCINTOVIEW_FROM_OUTSIDE System Option.
Support Added for Implicit Pass Through
In SAS Viya 3.5, PROC SQL detects user-defined functions in queries and automatically passes those queries to the database. For more information, see Syntax: SQL Procedure.
Documentation Enhancements
Information about using data set options in the INSERT statement was added in a March 2025 documentation update. See the following new topics: