Work With Oracle: A Quick Sheet for SAS Programmers

(Note: All the followings are tested on Windows XP environment.)

0. Install Oracle Database 10g Express Edition

Fast (and free) to download, easy to deploy and simple to admin–for learning and testing purpose, Oracle Database 10g Express Edition (Oracle Database XE, a mini version of Oracle Database 10g Release 2) are strongly recommended:

0.1 Download it at its homepage(206MB);

0.2 Install it following default settings;

0.3 Unlock accounts for HR and SCOTT. In a windows DOS prompt, using the following scripts:

sqlplus sys/YourSysPassword as sysdba

alter user HR account unlock

alter user HR identified by YourHRPassword

alter user SCOTT account unlock

alter user SCOTT identified by TIGER

Or you can accomplish these tasks within the Oracle web application:

http://localhost:8081/apex

1. Connect Oracle using SAS libname engine

libname SCOTT oracle user=”SCOTT” password=”TIGER” path=’xe’ ;

Note: xe is the default path for Oracle Database XE.

2. Connect Oracle using SQL Procedure Pass-Through Facility

proc sql ;
connect to oracle as orcl
(user=”SCOTT” password=”TIGER” path=’xe’);

_select *       

from connection to orcl_

(

SELECT …

) ;

_disconnect from orcl;</p> 

quit;

Note: 1) In this approach, Oracle, instead of SAS, processes the SQL statement (i.e., you use the more powerful and flexible Oracle SQL syntax instead of SAS Proc SQL procedure for querying. For more, see SAS Doc).

      __ 2) Your Oracle SQL codes should be placed in the <span style="color:#ff0000;">RED</span> blocks, and end without a semicolon(;):

_select *

from emp

3) Only basic Oracle SQL statements (select, create table, …)can pass through this facility.

3. Load SAS datasets to Oracle database

proc copy in=sashelp
out=scott;
select class;
run;

4. Misc: Some differences between Oracle SQL and SAS Proc SQL

4.1 Table aliases

Oracle: from employees a;

SAS: from employees a; or

from employees as a;

4.2 Column aliases

Oracle: don’t use single quotation marks(‘’).

_select job_id as job, job_id job, job_id as “job” , jobid as ‘job’

SAS:

_select job_id as job, job_id “job” , job_id ‘job’, jobid job

4.3 Literal Character Strings

Oracle: Date and character literal values must be enclosed within single quotation marks(‘’).

SAS: Use both single and double quotation marks.

4.4 Order the null value

Oracle: Null values are displayed last for ascending sequences and first for descending sequences.

SAS: Null values are displayed first for ascending sequences and last for descending sequences.

4.5 Nesting Group Functions

_select max(avg(salary))
from employees
group by departmentid

SAS log:

ERROR: Summary functions nested in this way are not supported.

Optional approach for SAS:

_select max(avg) as max
from(
select avg(salary) as avg
from employees
group by departmentid )

4.6 row number(_n_)

Oracle:

select rownum
from employees

SAS:

select monotonic()
from employees

Note: monotonic() is an undocumented function of SAS, seehttp://www2.sas.com/proceedings/sugi29/040-29.pdf