AdministratorFebruary 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;
For example, when we use Oracle database, we can use below code to pull out data by SQL queries:
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
where TXN_TYPE_ID=47 AND
YEAR_MONTH_NUM between 200909 and 201108 );
disconnect from oracle;
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.
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:
Log in to reply.