You can access the Oracle tables with SAS/ACCESS software using either the Oracle LIBNAME engine or the Pass-Through Facility with the SQL procedure.

A LIBNAME engine provides a seamless and transparent way to surface data. It requires minimal knowledge of the database and Structured Query Language (SQL). Once you define the LIBNAME, you can use Oracle tables same as SAS data sets.

You need to substitute your own Oracle connection information.

Once you assign the LIBNAME, you can view your Oracle tables in your library in the SAS Explorer window or, if you prefer, you can submit a DATASETS procedure that is similar to the following to display the tables in the SAS log.

In the following example, the KEEP data set option causes the SAS/ACCESS engine to select only the SALARY and DEPT columns when it reads the ORALIB.EMPLOYEES table:

The PROC SQL Pass-Through Facility requires that you have knowledge of the SQL that is passed to the database via PROC SQL.

In the above example, the second SELECT statement(in the parentheses) is passed to Oracle 'as is'. Temporary SAS data set ‘job204’ is created from an Oracle table Employees containing jobcode 204.

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	SAS/ACCESS Interface to Oracle	Microsoft® Windows® for 64-Bit Itanium-based Systems	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows XP 64-bit Edition	9.21_M2		9.2 TS2M3
		Microsoft® Windows® for x64	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Datacenter Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Enterprise Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 Standard Edition	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2003 for x64	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2008	9.21_M2		9.2 TS2M3
		Microsoft Windows Server 2008 for x64	9.21_M2		9.2 TS2M3
		Microsoft Windows XP Professional	9.21_M2		9.2 TS2M3
		Windows 7 Enterprise 32 bit	9.21_M2		9.2 TS2M3
		Windows 7 Enterprise x64	9.21_M2		9.2 TS2M3
		Windows 7 Home Premium 32 bit	9.21_M2		9.2 TS2M3
		Windows 7 Home Premium x64	9.21_M2		9.2 TS2M3
		Windows 7 Professional 32 bit	9.21_M2		9.2 TS2M3
		Windows 7 Professional x64	9.21_M2		9.2 TS2M3
		Windows 7 Ultimate 32 bit	9.21_M2		9.2 TS2M3
		Windows 7 Ultimate x64	9.21_M2		9.2 TS2M3
		Windows Vista	9.21_M2		9.2 TS2M3
		Windows Vista for x64	9.21_M2		9.2 TS2M3
		64-bit Enabled AIX	9.21_M2		9.2 TS2M3
		64-bit Enabled HP-UX	9.21_M2		9.2 TS2M3
		64-bit Enabled Solaris	9.21_M2		9.2 TS2M3
		HP-UX IPF	9.21_M2		9.2 TS2M3
		Linux	9.21_M2		9.2 TS2M3
		Linux for x64	9.21_M2		9.2 TS2M3
		OpenVMS on HP Integrity	9.21_M2		9.2 TS2M3
		Solaris for x64	9.21_M2		9.2 TS2M3

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

This is a sample of LIBNAME and PROC SQL Pass-Through code to connect to Oracle database using SAS/ACCESS Interface to Oracle. The code cannot run sucessfully stand-alone since you need to provide your Oracle connection information and database table name.

---

libname oralib oracle user=scott password=tiger path=alien;

Data work.acc024;
set oralib.employees(keep=salary dept);
where dept='ACC024';
run;

proc sql;
connect to oracle(user=scott password=tiger path=alien);
create table work.job204 as
select * from connection to Oracle
(select * from Employees where jobcode=204 );
quit;

47   libname oralib oracle user=scott password=XXXXX path=alien;
NOTE: Libref ORALIB was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: alien
48
49   Data work.acc024;
50   set oralib.employees(keep=salary dept);
51   where dept='ACC024';
52   run;

NOTE: There were 3 observations read from the data set ORALIB.EMPLOYEES.
      WHERE dept='ACC024';
NOTE: The data set WORK.ACC024 has 3 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


53
54   proc sql;
55   connect to oracle(user=scott password=XXXXX path=alien);
56   create table work.job204 as
57   select * from connection to Oracle
58   (select * from Employees where jobcode=204 );
NOTE: Table WORK.JOB204 created, with 7 rows and 11 columns.

59   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.03 seconds

Sample of LIBNAME statement and SQL Pass-Through code to connect to Oracle database using SAS/ACCESS® Interface to Oracle

Type:	Usage Note
Priority:
Topic:	Data Management ==> Data Sources ==> External Databases ==> Oracle

Date Modified:	2010-12-22 13:00:20
Date Created:	2010-11-16 22:37:22