07-21-2011 02:03 PM
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.
07-21-2011 03:40 PM
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,...
DISCONNECT FROM myconn;
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.
07-21-2011 03:48 PM
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:
I am hoping for something that will let me do the following (albeit work.test is not recognized in Oracle):
connect to odbc(user=xxx password=xxx! dsn=Database INSERTBUFF=3000);
create table Schema.test as
select id, amount, date1
) by odbc;
07-21-2011 04:37 PM
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
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?
07-21-2011 04:54 PM
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.
07-21-2011 05:24 PM
Ok...could you post the code you used?
You can also reference this paper:
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...
07-22-2011 02:30 PM
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?
07-22-2011 02:44 PM
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;
create table mylib.trans_test as
select * from work.trans
Roughly 13.5 million records were written in 30 minutes. Much better than what I was getting previously. Thank you for all your help.