About SQL Procedure Interactions

Overview of SQL Procedure Interactions with SAS/ACCESS

The SQL procedure implements Structured Query Language (SQL) for SAS software. For details about PROC SQL , see the SAS SQL Procedure User’s Guide. Here is how you can use SAS/ACCESS software for relational databases for PROC SQL interactions.

• You can assign a libref to a DBMS using the SAS/ACCESS LIBNAME statement and reference the new libref in a PROC SQL statement to query, update, or delete DBMS data. (See LIBNAME Statement for Relational Databases.)
• You can embed LIBNAME information in a PROC SQL view and then automatically connect to the DBMS every time the PROC SQL view is processed. (See SQL Views with Embedded LIBNAME Statements.)
• You can send DBMS-specific SQL statements directly to a DBMS using an extension to PROC SQL called the SQL pass-through facility. (See Syntax for the SQL pass-through facility for Relational Databases.)

SQL Pass-Through Facility

The SQL pass-through facility uses SAS/ACCESS to connect to a DBMS and to send statements directly to the DBMS for execution. As an alternative to the SAS/ACCESS LIBNAME statement, this facility lets you use the SQL syntax of your DBMS. It supports any SQL that is not ANSI-standard that your DBMS supports.

Not all SAS/ACCESS interfaces support this feature, however. To determine whether it is available in your environment, see SAS/ACCESS features by host.

Here are the tasks that you can complete by using the SQL pass-through facility.

• Establish and terminate connections with a DBMS using its CONNECT and DISCONNECT statements.
• Send dynamic, non-query, DBMS-specific SQL statements to a DBMS using its EXECUTE statement.
• Retrieve data directly from a DBMS using its CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement.

You can use SQL pass-through facility statements in a PROC SQL query, or you can store them in an SQL view. When you create an SQL view, any arguments that you specify in the CONNECT statement are stored with the view. Therefore, when you use the view in a SAS program, SAS can establish the appropriate connection to the DBMS.

For DBMS-specific details about the SQL pass-through facility, see the reference section for your SAS/ACCESS interface.