topic Writing to Oracle database using ODBC connection in SAS Programming

Writing to Oracle database using ODBC connection

Payal — Thu, 21 Jul 2011 18:03:42 GMT

I have SAS 9.3 on a PC, it is connecting to the database using ODBC connection. I can read tables easily enough using the pass through facility but when I try to write using a data step, it is taking a long time and then terminating the connection. None of the searches on web led me to an example that shows how to write to ODBC using the pass through facility. If anyone has any examples I can use, I would appreciate it.

Thanks.

Writing to Oracle database using ODBC connection

saspert — Thu, 21 Jul 2011 19:40:54 GMT

Hi Payal,

I would think this to be the syntax you can use while updating the tables in Oracle -

PROC SQL; *replace lowercase font with your tables/connection info

CONNECT TO myconn (USER =oracleuserid PASSWORD=oraclepw PATH=oraclepath);

CREATE TABLE TEST AS AS SELECT * FROM myconn

(

UPDATE schema_name.table_name alias

SET alias.column1=value, alias.column2=value2,...

WHERE alias.some_column=some_value

);

DISCONNECT FROM myconn;

QUIT;

However, it has been some time since I have tried an UPDATE (since you are asking about writing to the database). So, please always check with your local admin. Also, I am not sure if the syntax is different for 9.1.3 vs 9.1.

Thanks,
saspert.

Re: Writing to Oracle database using ODBC connection

Payal — Thu, 21 Jul 2011 19:48:29 GMT

Thank you for answering.

I don't think this solution will work for me. The problem is that my data is in a SAS dataset (i.e. work.datasetname) and it has millions of rows so I don't think I can just use update for one row at a time. I used the following syntax but that's where I got the timing error:

Data SCHEMA.test;

Set work.test;

Run;

I am hoping for something that will let me do the following (albeit work.test is not recognized in Oracle):

proc sql;

connect to odbc(user=xxx password=xxx! dsn=Database INSERTBUFF=3000);

execute (

create table Schema.test as

select id, amount, date1

from work.test

) by odbc;

quit;

Re: Writing to Oracle database using ODBC connection

Reeza — Thu, 21 Jul 2011 20:37:53 GMT

I thought when you used pass-through you needed to use SQL code, not SAS?

Ie use 'into' rather than 'create table as'

select id, amount, date1

into schema.test

from work.test

You also don't need to do it through the pass through facility.

See this paper, its for Teradata but maybe something similar for Oracle?

http://support.sas.com/resources/papers/proceedings11/105-2011.pdf

Re: Writing to Oracle database using ODBC connection

Payal — Thu, 21 Jul 2011 20:54:58 GMT

Create table as is part of SQL code, so I tried to use that. Per your suggestion, I tried the into, but it gave me the following error:

ERROR 79-322: Expecting a :.

ERROR 76-322: Syntax error, statement will be ignored

When I researched more, it seemed like into: is creating a macro variable rather than writing to a table. Still, no resolution for me. The reason I am using the pass through facility is because the database resides at a third party vendor location and I have to use ODBC to connect to it anyway.

Thank you for the suggestions though. The paper you linked was very interesting.

Re: Writing to Oracle database using ODBC connection

Reeza — Thu, 21 Jul 2011 21:24:20 GMT

Ok...could you post the code you used?

You can also reference this paper:

www2.sas.com/proceedings/sugi29/106-29.pdf

specifically on how to load tables into Oracle, its old, but perhaps some of those methods would work for you.

I usually use a libname statement with an odbc connection and it works fine...

Re: Writing to Oracle database using ODBC connection

saspert — Fri, 22 Jul 2011 18:30:06 GMT

From my past experience I have seen it is quite cumbersome to load Oracle using Libname statment because there is usually less control over a dataset having 1 million rows. And on the other hand using Proc SQL Pass thu will allow only 1 record to be updated at a time.

Any other users who have faced similiar issues?

Re: Writing to Oracle database using ODBC connection

Payal — Fri, 22 Jul 2011 18:44:24 GMT

Reeza - I was able to use the paper you linked and it worked very well. Here's my code:

libname mylib odbc user=xxx password=xxx dsn=DSNNAME insertbuff=32767;

proc sql;

create table mylib.trans_test as

select * from work.trans

quit;

Roughly 13.5 million records were written in 30 minutes. Much better than what I was getting previously. Thank you for all your help.