The SQL Procedure

Concepts

Using SAS Data Set Options with PROC SQL

PROC SQL can apply most of the SAS data set options, such as KEEP= and DROP=, to tables or SAS/ACCESS views. 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. You can also use SAS data set options on tables or SAS/ACCESS views listed in the FROM clause of a query. 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);

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

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, the SAS System can automatically establish the appropriate connection to the DBMS.

See CONNECT Statement, DISCONNECT Statement, EXECUTE Statement, CONNECTION TO, and your SAS/ACCESS documentation.

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.

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).

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, PROC SQL passes the join to the DBMS. This increases performance by reducing data movement and translation. If the DBMS cannot do the join, 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 installed for your DBMS. For more information on the SAS/ACCESS LIBNAME statement, refer to your SAS/ACCESS documentation.

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.

SQLRC

contains the following status values that indicate the success of the SQL procedure statement:

0: 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 Institute. 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 Institute. 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 the LOOPS option .

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.

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 appears in the SAS log and gives you the macros' values.

PROC SQL Macro Variable Values

1 options ls=80; 2 proc sql noprint; 3 select * 4 from proclib.payroll; 5 6 %put sqlobs=**&sqlobs** 7 sqloops=**&sqloops** 8 sqlrc=**&sqlrc**; sqlobs=**1** sqloops=**11** sqlrc=**0**

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, 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.

Chapter Contents
Previous
Next
Top of Page