SQL Pass-Through Facility for Relational Databases

[Justin]

When we use SAS programming to analyze data, we can connect to remote relational databases (Hadoop, Oracle, DB2, Teradata, SQL Server etc.) via the convenient SQL Pass Through facility to access and extract data. Below is the general syntax:

PROC SQL <option(s)>;
CONNECT TO dbms-name <AS alias> <(<database-connection-arguments> <connect-statement-arguments> )>;
Your custom query here;
DISCONNECT FROM dbms-name | alias;
QUIT;

For example, when we use Oracle database, we can use below code to pull out data by SQL queries:

proc sql;
connect to oracle(user=&uid  password=&und   path='     '   buffsize=5000); 

create table AAA as
  SELECT * FROM CONNECTION TO ORACLE
  ( select YEAR_MONTH_NUM      as year_month_num,
     ORIG_CIF_NUMBER           as custno,
     ACCOUNT_ID                as account_id,
     PRODUCT_ID                as product_id,
     RSM_PRODUCT_CODE          as product_code
   from MTHLY_CUST_ACCT_TXN   a  left join MTHLY_CUSTOMER  b 
    on a.YEAR_MONTH_NUM=b.YEAR_MONTH_NUM and
       a.Customer_ID=b.Customer_ID         and
       a.Account_ID=b.Account_ID
       where TXN_TYPE_ID=47  AND  
       YEAR_MONTH_NUM between 200909 and 201108   );

disconnect from oracle;
quit;

Please note: you must use native Oracle SQL code in the highlight part rather than SAS SQL code, because they have tiny differences. This part of code will NOT be executed in SAS, instead, they will be passed through to the Oracle database and be executed in Oracle. That’s why it is called SQL Pass Through facility.

Return Codes
As you use the PROC SQL statements that are available in the SQL pass-through facility, any error return codes and error messages are written to the SAS log. These codes and messages are available to you through these SAS macro variables:

SQLXRC: contains the DBMS return code that identifies the DBMS error.
SQLXMSG: contains descriptive information about the DBMS error that the DBMS generates.

The contents of the SQLXRC and SQLXMSG macro variables are printed in the SAS log using the %PUT macro. They are reset after each SQL pass-through facility statement has been executed.

See below link for more information about these return codes:

Automatic Macro Variables for Relational Databases

• This discussion was modified 3 years, 9 months ago by  Justin.
• This discussion was modified 3 years, 9 months ago by  Justin.