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.
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 |
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
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 |