Concepts: SQL Procedure | Base SAS 9.1 Procedures Guide, Volumes 1, 2, 3 and 4

Using SAS Data Set Options with PROC SQL

In PROC SQL, you can apply most of the SAS data set options, such as KEEP= and DROP=, to tables or SAS/ACCESS views any time that you specify a table or SAS/ACCESS view. In the SQL procedure, SAS data set options that are separated by spaces are enclosed in parentheses, and they follow immediately after the table or SAS/ACCESS view name . In the following PROC SQL step, RENAME= renames LNAME to LASTNAME for the STAFF1 table. OBS= restricts the number of rows written to STAFF1 to 15:

 proc sql;     create table            staff1(rename=(lname=lastname)) as        select *           from staff(obs=15);

SAS data set options can be combined with SQL statement arguments:

 proc sql;     create table test        (a character, b numeric, pw=cat);     create index staffidx on        staff1 (lastname, alter=dog);

You cannot use SAS data set options with DICTIONARY tables because DICTIONARY tables are read-only objects.

The only SAS data set options that you can use with PROC SQL views are those that assign and provide SAS passwords: READ=, WRITE=, ALTER=, and PW=.

See SAS Language Reference: Dictionary for a description of SAS data set options.

Connecting to a DBMS Using the SQL Procedure Pass-Through Facility

What Is the Pass-Through Facility?

The SQL Procedure Pass-Through Facility enables you to send DBMS-specific SQL statements directly to a DBMS for execution. The Pass-Through Facility uses a SAS/ ACCESS interface engine to connect to the DBMS. Therefore, you must have SAS/ ACCESS software installed for your DBMS.

You submit SQL statements that are DBMS-specific. For example, you pass Transact-SQL statements to a SYBASE database. The Pass-Through Facility s basic syntax is the same for all the DBMSs. Only the statements that are used to connect to the DBMS and the SQL statements are DBMS-specific.

With the Pass-Through Facility, you can perform the following tasks :

establish a connection with the DBMS using a CONNECT statement and terminate the connection with the DISCONNECT statement.
send nonquery DBMS-specific SQL statements to the DBMS using the EXECUTE statement.
retrieve data from the DBMS to be used in a PROC SQL query with the CONNECTION TO component in a SELECT statement s FROM clause.

You can use the Pass-Through Facility statements in a query, or you can store them in a PROC SQL view. When a view is stored, any options that are specified in the corresponding CONNECT statement are also stored. Thus, when the PROC SQL view is used in a SAS program, SAS can automatically establish the appropriate connection to the DBMS.

See CONNECT Statement on page 1056, DISCONNECT Statement on page 1067, EXECUTE Statement on page 1068, CONNECTION TO on page 1092, and The Pass-Through Facility for Relational Databases in SAS/ACCESS for Relational Databases: Reference .

Note: SAS procedures that do multipass processing cannot operate on PROC SQL views that store Pass-Through Facility statements, because the Pass-Through Facility does not allow reopening of a table after the first record has been retrieved. To work around this limitation, create a SAS data set from the view and use the SAS data set as the input data set.

Return Codes

As you use PROC SQL statements that are available in the Pass-Through Facility, any errors are written to the SAS log. The return codes and messages that are generated by the Pass-Through Facility are available to you through the SQLXRC and SQLXMSG macro variables. Both macro variables are described in Using Macro Variables Set by PROC SQL on page 1133.

Connecting to a DBMS Using the LIBNAME Statement

For many DBMSs, you can directly access DBMS data by assigning a libref to the DBMS using the SAS/ACCESS LIBNAME statement. Once you have associated a libref with the DBMS, you can specify a DBMS table in a two-level SAS name and work with the table like any SAS data set. You can also embed the LIBNAME statement in a PROC SQL view (see CREATE VIEW Statement on page 1062).

PROC SQL will take advantage of the capabilities of a DBMS by passing it certain operations whenever possible. For example, before implementing a join, PROC SQL checks to see if the DBMS can do the join. If it can, then PROC SQL passes the join to the DBMS. This enhances performance by reducing data movement and translation. If the DBMS cannot do the join, then PROC SQL processes the join. Using the SAS/ACCESS LIBNAME statement can often provide you with the performance benefits of the SQL Procedure Pass-Through Facility without having to write DBMS-specific code.

To use the SAS/ACCESS LIBNAME statement, you must have SAS/ACCESS software installed for your DBMS. For more information about the SAS/ACCESS LIBNAME statement, refer to the SAS/ACCESS documentation for your DBMS.

Using the DICTIONARY Tables

What Are DICTIONARY Tables?

DICTIONARY tables are special, read-only SAS data views that contain information about your SAS session. For example, the DICTIONARY.COLUMNS table contains information, such as name, type, length, and format, about all columns in all tables that are known to the current SAS session. DICTIONARY tables are accessed by using the libref DICTIONARY in the FROM clause in a SELECT statement in PROC SQL. Additionally, there are PROC SQL views, stored in the SASHELP library and known as SASHELP views , that reference the DICTIONARY tables and that can be used in other SAS procedures and in the DATA step.

Note: You cannot use data set options with DICTIONARY tables.

For an example that demonstrates the use of a DICTIONARY table, see Example 6 on page 1150.

The following table describes the DICTIONARY tables that are available and shows the associated SASHELP view(s) for each table.

Table 45.2: DICTIONARY Tables and Associated SASHELP Views
DICTIONARY table	SASHELP view	Description
CATALOGS	VCATALG	Contains information about known SAS catalogs.
CHECK_CONSTRAINTS	VCHKCON	Contains information about known check constraints.
COLUMNS	VCOLUMN	Contains information about columns in all known tables.
CONSTRAINT_COLUMN_USAGE	VCNCOLU	Contains information about columns that are referred to by integrity constraints.
CONSTRAINT_TABLE_USAGE	VCNTABU	Contains information about tables that have integrity constraints defined on them.
DICTIONARIES	VDCTNRY	Contains information about all DICTIONARY tables.
ENGINES	VENGINE	Contains information about SAS engines.
EXTFILES	VEXTFL	Contains information about known external files.
FORMATS	VFORMAT	Contains information about currently accessible formats and informats.
GOPTIONS	VGOPT VALLOPT	Contains information about currently defined graphics options (SAS/GRAPH software). SASHELP.VALLOPT includes SAS system options as well as graphics options.
INDEXES	VINDEX	Contains information about known indexes.
LIBNAMES	VLIBNAM	Contains information about currently defined SAS data libraries.
MACROS	VMACRO	Contains information about currently defined macros.
MEMBERS	VMEMBER VSACCES VSCATLG VSLIB VSTABLE VSTABVW VSVIEW	Contains information about all objects that are in currently defined SAS data libraries. SASHELP.VMEMBER contains information for all member types; the other SASHELP views are specific to particular member types (such as tables or views).
OPTIONS	VOPTION VALLOPT	Contains information on SAS system options. SASHELP.VALLOPT includes graphics options as well as SAS system options.
REFERENTIAL_CONSTRAINTS	VREFCON	Contains information about referential constraints.
STYLES	VSTYLE	Contains information about known ODS styles.
TABLE_CONSTRAINTS	VTABCON	Contains information about integrity constraints in all known tables.
TABLES	VTABLE	Contains information about known tables.
TITLES	VTITLE	Contains information about currently defined titles and footnotes.
VIEWS	VVIEW	Contains information about known data views.

Retrieving Information about DICTIONARY Tables and SASHELP Views

To see how each DICTIONARY table is defined, submit a DESCRIBE TABLE statement. After you know how a table is defined, you can use its column names in a subsetting WHERE clause in order to retrieve more specific information. For example:

 proc sql;     describe table dictionary.indexes;

The results are written to the SAS log:

 6    proc sql;  7       describe table dictionary.indexes;  NOTE: SQL table DICTIONARY.INDEXES was created like:  create table DICTIONARY.INDEXES    (     libname char(8) label='Library Name',     memname char(32) label='Member Name',     memtype char(8) label='Member Type',     name char(32) label='Column Name',     idxusage char(9) label='Column Index Type',     indxname char(32) label='Index Name',     indxpos num label='Position of Column in Concatenated Key',     nomiss char(3) label='Nomiss Option',     unique char(3) label='Unique Option'     );

Use the DESCRIBE VIEW statement in PROC SQL to find out how a SASHELP view is defined. Here s an example:

 proc sql;     describe view sashelp.vstabvw;

The results are written to the SAS log:

 6    proc sql;  7       describe view sashelp.vstabvw;  NOTE: SQL view SASHELP.VSTABVW is defined as:          select libname, memname, memtype            from DICTIONARY.MEMBERS           where (memtype='VIEW') or (memtype='DATA')        order by libname asc, memname asc;

Using DICTIONARY Tables

DICTIONARY tables are commonly used to monitor and manage SAS sessions because the data is more easily manipulated than the output from, for example, PROC DATASETS. You can query DICTIONARY tables the same way that you query any other table, including subsetting with a WHERE clause, ordering the results, and creating PROC SQL views. Note that many character values in the DICTIONARY tables are stored as all-uppercase characters ; you should design your queries accordingly .

Because DICTIONARY tables are read-only objects, you cannot insert rows or columns, alter column attributes, or add integrity constraints to them.

Note: For DICTIONARY.TABLES and SASHELP.VTABLE, if a table is read-protected with a password, then the only information that is listed for that table is the library name, member name, member type, and type of password protection; all other information is set to missing.

DICTIONARY Tables and Performance

When querying a DICTIONARY table, SAS launches a discovery process that gathers information that is pertinent to that table. Depending on the DICTIONARY table that is being queried, this discovery process can search libraries, open tables, and execute views. Unlike other SAS procedures and the DATA step, PROC SQL can mitigate this process by optimizing the query before the discovery process is launched. Therefore, although it is possible to access DICTIONARY table information with SAS procedures or the DATA step by using the SASHELP views, it is often more efficient to use PROC SQL instead.

For example, the following programs both produce the same result, but the PROC SQL step runs much faster because the WHERE clause is processed prior to opening the tables that are referenced by the SASHELP.VCOLUMN view:

 data mytable;     set sashelp.vcolumn;     where libname='WORK' and memname='SALES';  run;  proc sql;     create table mytable as        select * from sashelp.vcolumn        where libname='WORK' and memname='SALES';  quit;

Note: SAS does not maintain DICTIONARY table information between queries. Each query of a DICTIONARY table launches a new discovery process.

If you are querying the same DICTIONARY table several times in a row, then you can get even faster performance by creating a temporary SAS data set (with the DATA step SET statement or PROC SQL CREATE TABLE AS statement) with the information that you want and running your query against that data set.

Using Macro Variables Set by PROC SQL

PROC SQL sets up macro variables with certain values after it executes each statement. These macro variables can be tested inside a macro to determine whether to continue executing the PROC SQL step. SAS/AF software users can also test them in a program after an SQL SUBMIT block of code, using the SYMGET function.

After each PROC SQL statement has executed, the following macro variables are updated with these values:

SQLOBS

contains the number of rows executed by an SQL procedure statement. For example, it contains the number of rows formatted and displayed in SAS output by a SELECT statement or the number of rows deleted by a DELETE statement.
When the NOPRINT option is specified, the value of the SQLOBS macro variable depends on whether an output table, single macro variable, macro variable range, or macro variable list is created:
- If no output table, macro variable list, or macro variable range is created, then SQLOBS contains the value 1.
- If an output table is created, then SQLOBS contains the number of rows in the output table.
- If a single macro variable is created, then SQLOBS contains the value 1.
- If a macro variable list or macro variable range is created, then SQLOBS contains the number of rows that are processed to create the macro variable list or range.

SQLRC

contains the following status values that indicate the success of the SQL procedure statement:
- - PROC SQL statement completed successfully with no errors.
- 4
  - PROC SQL statement encountered a situation for which it issued a warning. The statement continued to execute.
- 8
  - PROC SQL statement encountered an error. The statement stopped execution at this point.
  - 12
  - PROC SQL statement encountered an internal error, indicating a bug in PROC SQL that should be reported to SAS Technical Support. These errors can occur only during compile time.
- 16
  - PROC SQL statement encountered a user error. This error code is used, for example, when a subquery (that can only return a single value) evaluates to more than one row. These errors can only be detected during run time.
- 24
  - PROC SQL statement encountered a system error. This error is used, for example, if the system cannot write to a PROC SQL table because the disk is full. These errors can occur only during run time.
- 28
  - PROC SQL statement encountered an internal error, indicating a bug in PROC SQL that should be reported to SAS Technical Support. These errors can occur only during run time.

SQLOOPS

contains the number of iterations that the inner loop of PROC SQL executes. The number of iterations increases proportionally with the complexity of the query. See also the description of LOOPS = on page 1050.

SQLXRC

contains the DBMS-specific return code that is returned by the Pass-Through Facility.

SQLXMSG

contains descriptive information and the DBMS-specific return code for the error that is returned by the Pass-Through Facility.
Note: Because the value of the SQLXMSG macro variable can contain special characters (such as &, %, /, *, and ;), use the %SUPERQ macro function when printing the value:
```
 %put %superq(sqlxmsg); 
```
See SAS Macro Language: Reference for information about the %SUPERQ function.

This example retrieves the data but does not display them in SAS output because of the NOPRINT option in the PROC SQL statement. The %PUT macro statement displays the macro variables values.

 proc sql noprint;     select *        from proclib.payroll;  %put sqlobs=**&sqlobs**       sqloops=**&sqloops**       sqlrc=**&sqlrc**;

The message in Output 45.3 appears in the SAS log and gives you the macros values.

Output 45.3: PROC SQL Macro Variable Values

 40   options ls=80;  41  42   proc sql noprint;  43      select *  44         from proclib.payroll;  45  46   %put sqlobs=**&sqlobs**  47        sqloops=**&sqloops**  48        sqlrc=**&sqlrc**;  sqlobs=**1**        sqloops=**11**     sqlrc=**0**

Macro variables that are generated by PROC SQL follow the scoping rules for %LET. For more information about macro variable scoping, see SAS Macro Language: Reference .

Updating PROC SQL and SAS/ACCESS Views

You can update PROC SQL and SAS/ACCESS views using the INSERT, DELETE, and UPDATE statements, under the following conditions.

If the view accesses a DBMS table, then you must have been granted the appropriate authorization by the external database management system (for example, DB2). You must have installed the SAS/ACCESS software for your DBMS. See the SAS/ACCESS interface guide for your DBMS for more information on SAS/ACCESS views.
You can update only a single table through a view. The table cannot be joined to another table or linked to another table with a set-operator. The view cannot contain a subquery.
You can update a column in a view using the column s alias, but you cannot update a derived column, that is, a column produced by an expression. In the following example, you can update the column SS, but not WeeklySalary.
```
 create view EmployeeSalaries as     select Employee, SSNumber as SS,            Salary/52 as WeeklySalary            from employees; 
```
You cannot update a view containing an ORDER BY.

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.