BookmarkSubscribeRSS Feed
Payal
Calcite | Level 5

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.

7 REPLIES 7
saspert
Pyrite | Level 9

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.

Payal
Calcite | Level 5

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;

Reeza
Super User

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

Payal
Calcite | Level 5

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.

Reeza
Super User

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...

saspert
Pyrite | Level 9

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?

Payal
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1949 views
  • 3 likes
  • 3 in conversation