Home Forums Main Forums SAS Forum SQL Pass-Through Facility for Relational Databases

  • SQL Pass-Through Facility for Relational Databases

     Justin updated 7 months, 2 weeks ago 1 Member · 1 Post
  • Justin

    Administrator
    February 6, 2021 at 5:59 pm

    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 7 months, 2 weeks ago by  Justin.
    • This discussion was modified 7 months, 2 weeks ago by  Justin.

Log in to reply.

Original Post
0 of 0 posts June 2018
Now