Can I read/write to an Oracle database from a SAS program using SAS/ACCESS to ODBC?

[Mahis]

Hello everyone,

I'm trying to understand if it's possible to directly access and manipulate data in an Oracle database from a SAS program using the SAS/ACCESS Interface to ODBC.

Is it possible to create, insert, update, or delete data in Oracle tables through SAS/ACCESS Interface to ODBC.

I found this method for SAS/ACCESS Interface to Oracle, I want to know if I can do something like this with SAS/ACCESS to ODBC.

[Patrick]

Short answer is yes. There is just some functionality not supported that the native driver/access engine would.
https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/odbc-driver.html#GUID-CD847FD4-7... 

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n1ht6mv0wybbzkn1x4np6nnlwzu6.htm 

[Mahis]

Using SAS/ACCESS to ODBC I can select data from the DB and create tables within SAS libraries, I need to write the table directly into the Oracle database. If you have experience with ODBC and SAS, could you please help me specify the path of the libref within the database?

[Patrick · Accepted solution]

If you have already a working libname for reading data in Oracle with SAS then you've got all you need for SAS to communicate with Oracle. All the rest depends on what grants your user got in Oracle. 

For just writing data from SAS to Oracle you could use Proc Append or SAS SQL. 

If you want to write data from one Oracle table to another Oracle table and similar things then the role of SAS is just about sending the SQL to Oracle. This is also possible using explicit pass-through SQL. Here some template code:

libname myora ODBC<connection parameters>;

proc sql;
  connect using myora;
  execute(
    <and here any Oracle syntax that SAS just will send to Oracle for execution >
    ) by myora;
quit;

...and don't forget that a libref can't be longer than 8 characters meaning below wouldn't be  valid.

Here 3 syntax options all creating the same SAS table from an Oracle source table

libname myora ODBC <connection parameters>;

proc sql;
  connect using myora;
  create table work.SAS_Table as 
  select * from connection to myora
  (
    <and here Oracl SQL syntax that can use Oracle only functionality like partition clauses >
  );
quit;

proc sql;
  create table work.SAS_table as
  select * 
  from myora.<ora table name as seen by SAS>;
quit;

data work.SAS_Table;
  set myora.<ora table name as seen by SAS>;
run;

[Mahis]

Thank you so much for your help, After adding the libname statement to my code as shown below:

libname mytest odbc datasrc=&ds;

proc sql;
connect to odbc (dsn=&ds user=&us password=&pw);
create table mytest.sqlo as
select *
from connection to odbc
(
SELECT *
FROM &det..&table
WHERE ROWNUM <= 5
);
disconnect from odbc;
quit;

The table is now created directly in the database, whereas before it was created in the work library.

Thanks again.

[Tom]

---

@Mahis wrote:

Thank you so much for your help, After adding the libname statement to my code as shown below:

libname mytest odbc datasrc=&ds;

proc sql;
connect to odbc (dsn=&ds user=&us password=&pw);
create table mytest.sqlo as
select *
from connection to odbc
(
SELECT *
FROM &det..&table
WHERE ROWNUM <= 5
);
disconnect from odbc;
quit;

The table is now created directly in the database, whereas before it was created in the work library.

Thanks again.

---

That is silly.  You pulled the data from ORACLE by using FROM CONNECTION TO.  Then you wrote it back to ORACLE by writing to a libref that is pointing from SAS to ORACLE.  So the data took a round trip from the Oracle server to the SAS server and back again.

If you just use passthru SQL code you could have Oracle make the table and the data would never have to leave the Oracle server.

Also you don't need to make a second connection . Just use CONNECT USING MYTEST in the PROC SQL code and you can reuse the existing connection you already have to ORACLE.  The ORACLE DBA will probably be happier if you only made one connection.

(Note SAS does sell a SAS/ACCESS to ORACLE product which should provide better integration than use SAS/ACCESS to ODBC.)

[Mahis]

Thank you for your feedback. I wasn't aware of this connection method until I saw it in Patrick's response. I was just testing if we could write to the Oracle database directly instead of the SAS compute server.

I would like to ask you, does using aggregation between multiple Oracle databases in SAS Guide impact the performance of the compute server, or will all the processing be done in the Oracle server? We want to add connections to different Oracle databases to work with them in SAS Guide, but we don't want it to affect the performance of the computer server that is being used for another project.

[Tom]

You will need to ask your Oracle DBA about what types of queries will cause them issues.

In general I suspect that they would prefer that you aggregate the data in Oracle where you can and so end up transferring less data across to SAS.

If you have to use different in different physical database in Oracle you might also want to check with your DBA.  Multiple schemas (groups of related tables, like a LIBREF in SAS) in the same Oracle database should be able to be queried together.  Again it probably works better to use pass thru to send Oracle code you created instead of letting SAS guess how to generate the Oracle code.  That can cause SAS to attempt to drag the whole dataset ("table" in Oracle/SQL speak) to SAS because it does not know how to translate some SAS specific code you used.

If you are using data across different Oracle database there are features in Oracle for making cross connections between databases.  But they are complex to setup and usually for ad hoc reporting it is just simpler to make your own separate connections.

If they are really worried about your queries impacting their operations the DBA should know how to give you the right access.  Normally they would give you access to readonly views that make it impossible for you to mess up the data.  They might even make a nightly snapshot of the data into a separate database for reporting so that there is no impact at all on the database that is used for daily activity.

[Mahis]

We are worried about the impact of these aggregations on the SAS compute server. If we will use SQL passthru, all the processing will be done on the Oracle DB?

[SASKiwi]

Passthru means pass the query through to the database you are connecting to where all processing will be done. If all of your source data is in Oracle and you want to write the results back to Oracle, then doing as much processing in Oracle as you can is a sensible approach.

[Mahis]

@Tom @SASKiwi  Thank you, that's what we needed to know.