Creating compressed tables in Oracle database from SAS

[EtoUspeh]

Hi, everyone!

Oracle databases have an option to create compressed tables:

    create table new_table compress as

    select *

    from old_table

I can create uncompressed Oracle tables from SAS:

    libname lib_orcl oracle user=orcl_user path=orcl_srv password=orcl_password schema=orcl_user;

    proc sql;

        create table lib_orcl.new_table as

        select *

        from old_SAS_table;

    quit;

Is there a way to make compressed Oracle tables from within SAS?

option compress=yes; doesn't seem to work

[ybolduc]

Have you tried using an explicit pass-through? Usually, this is the best way to use Database specific functionnalities.

See this URL: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113595.htm

Thanks,

[EtoUspeh]

The issue with pass-through is that as far as I know does not allow to use data on SAS server.

I need to take some data from SAS server and put it into Oracle...

[ybolduc]

You could create the table with an explicit pass-through and the use a simple proc sql insert statement to populate it.

[BrunoMueller · Accepted solution]

Have not tested this, but on the LIBNAME statement you have the option DBCREATE_TABLE_OPTS=. with this option you can specify options to be used when creating a table in Oracle.

The SASTRACE option below will show the SQL passed to the DBMS.

Code sample could look like this:

libname sugus oracle path=xe  DBCREATE_TABLE_OPTS="compress";

options
  sastrace=",,,d"
  sastraceloc=saslog
  nostsuffix
;

proc delete data=sugus.comp_test;
run;

data sugus.comp_test;
  set sashelp.cars;
run;

[EtoUspeh]

Thanks, Bruno! This is exactly what I need.

The "create and insert" solution would probably work too but it's just inconvenient.