SAS to Oracle ODBC - passing a SAS table INTO the database

Question

Can anyone please advise the syntax for passing a table FROM a SAS library INTO an oracle database?

example code below (although obviously the connection to the WORK library cannot be referenced in this way)

PROC SQL noprint;
connect to ODBC as X (dsn='ALIAS' uid='USER1' pwd='passwd' quote_char='');
exec (CREATE TABLE Test AS
    SELECT * from WORK.MY_SAS_TABLE
    )by X;
disconnect from X;
quit;

A similar question was asked here but seems to relate to a SQLSERVER connection rather than oracle..

Answer 1

I'm a bit rusty, but what if you set up your database as a libref?

Something like:

libname X odbc dsn='ALIAS' uid='USER1' pwd='passwd' quote_char='';
data X.test;
    set work.my_sas_table;
run;

Answer 2

Set up a libref to point to your Oracle database, either using the ODBC libname engine or the Oracle libname engine (which will be faster if you have the right licence and software installed):

libname X oracle username='USER1' password='passwd' path=ORCL;

If an empty table with the right columns already exists in Oracle, you can use:

proc sql noprint;
  insert into X.test select * from work.my_sas_table;
quit;

If the table doesn't exist, you can use a data step:

data X.test;
  set work.my_sas_table;
run;