topic Re: Creating compressed tables in Oracle database from SAS in SAS Programming

Creating compressed tables in Oracle database from SAS

EtoUspeh — Thu, 28 Jun 2018 12:18:05 GMT

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

Re: Creating compressed tables in Oracle database from SAS

ybolduc — Thu, 28 Jun 2018 15:38:11 GMT

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,

Re: Creating compressed tables in Oracle database from SAS

EtoUspeh — Thu, 28 Jun 2018 15:54:09 GMT

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

Re: Creating compressed tables in Oracle database from SAS

ybolduc — Thu, 28 Jun 2018 16:39:22 GMT

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

Re: Creating compressed tables in Oracle database from SAS

BrunoMueller — Fri, 29 Jun 2018 15:19:32 GMT

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;

Re: Creating compressed tables in Oracle database from SAS

EtoUspeh — Mon, 02 Jul 2018 10:50:44 GMT

Thanks, Bruno! This is exactly what I need.

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