Retrieving DBMS Data with a Pass-Through Query

This article will describe retrieving DBMS data using the SQL Procedure Pass-Through Facility. A pass-through facility reads and writes data between SAS Systems and DBMSs using the SAS/ACCESS view engine. For detailed information, see SQL Procedure’s Interaction with SAS/ACCESS Software.

Introduction to Pass-Through Queries

A pass-through query in SAS enables direct communication with a DBMS, allowing you to write and execute SQL code that runs natively on the DBMS server. This approach offers several advantages:

• Performance: Queries are executed on the DBMS server, taking advantage of its indexing and optimization features.
• Flexibility: You can use the full SQL syntax supported by your DBMS, including advanced features not available in SAS SQL.
• Efficiency: Reduces data transfer between the DBMS and SAS, as only the result set is transferred back to SAS.

Example Scenario

Suppose you need a brief listing of companies to whom you have sent invoices, the amount of the invoices, and the dates. This data is stored in an Oracle database. We will demonstrate how to use the SQL Procedure Pass-Through Facility to retrieve this data.

Step-by-Step Guide

1. Connect to the Oracle Database

First, you need to establish a connection to your Oracle database using the CONNECT statement in PROC SQL.

proc sql;
    connect to oracle as mydb (user=scott orapw=tiger path='myorapath');
    %put &sqlxmsg;
quit;

In this code:

• mydb is the alias for your connection.
• user and orapw specify your Oracle username and password.
• path specifies the Oracle database path.

The %put &sqlxmsg; statement helps capture and display any messages from the DBMS, useful for debugging.

Read more at – Top 7 Tips To Troubleshoot Your Proc SQL Code Like A Pro | 9TO5SAS

2. Retrieve Data with a Pass-Through Query

Next, you write the pass-through query to select the required data from the Oracle tables.

proc sql;
    connect to oracle as mydb (user=scott orapw=tiger path='myorapath');
    %put &sqlxmsg;

    title 'Brief Data for All Invoices';
    select invoicenum, name, billedon format=datetime9., amountinus format=dollar20.2
    from connection to mydb
    (
        select invoicenum, customers.name, billedon, amountinus
        from invoice, customers
        where invoice.billedto = customers.customer
        order by billedon, invoicenum
    );
    %put &sqlxmsg;

    disconnect from mydb;
quit;

Here’s what this code does:

• Connect to Oracle: Re-establishes the connection to Oracle.
• Title: Sets the title for the output.
• Select Clause: Specifies the columns to retrieve (invoicenum, name, billedon, and amountinus).
• From Clause: Uses connection to mydb to indicate that the subsequent SQL is executed on the Oracle DBMS.
• Inner Query: Contains the actual SQL code to run on Oracle. It joins the invoice and customers tables on the billedto and customer columns, respectively, and orders the results by billedon and invoicenum.

Explanation

CONNECT TO ORACLE

This statement initiates the connection to the Oracle database using the provided credentials and path.

FROM CONNECTION TO

This clause indicates that the following SQL query should be executed on the DBMS server rather than on SAS.

SQL Query

The inner query within parentheses is standard Oracle SQL. It performs the following actions:

• Joins the invoice and customers tables on the billedto and customer columns.
• Selects relevant columns (invoicenum, name, billedon, and amountinus).
• Orders the results by billedon and invoicenum.

Disconnecting from the DBMS

After the query execution, it’s good practice to disconnect from the database.

proc sql;
    disconnect from mydb;
quit;

This ensures that the connection is properly closed, freeing up resources.

Benefits of Using Pass-Through Queries

• Enhanced Performance: By leveraging the DBMS’s processing power, you can execute complex queries more quickly.
• Reduced Data Transfer: Only the final result set is transferred to SAS, reducing network load.
• Advanced SQL Features: Access to DBMS-specific SQL functions and optimizations.

Conclusion

Using the SQL Procedure Pass-Through Facility in SAS is a powerful method for retrieving and manipulating data stored in a DBMS. By executing queries directly on the DBMS, you can take full advantage of its performance and features. This approach not only improves efficiency but also simplifies the data retrieval process, especially when dealing with large datasets and complex queries.