- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.