DATA Step, Macro, Functions and more

Creating compressed tables in Oracle database from SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Creating compressed tables in Oracle database from SAS

[ Edited ]

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


Accepted Solutions
Solution
2 weeks ago
SAS Super FREQ
Posts: 817

Re: Creating compressed tables in Oracle database from SAS

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;

View solution in original post


All Replies
Contributor
Posts: 25

Re: Creating compressed tables in Oracle database from SAS

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,

Occasional Contributor
Posts: 5

Re: Creating compressed tables in Oracle database from SAS

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

Contributor
Posts: 25

Re: Creating compressed tables in Oracle database from SAS

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

Solution
2 weeks ago
SAS Super FREQ
Posts: 817

Re: Creating compressed tables in Oracle database from SAS

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;
Occasional Contributor
Posts: 5

Re: Creating compressed tables in Oracle database from SAS

Posted in reply to Bruno_SAS

Thanks, Bruno! This is exactly what I need.

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 100 views
  • 0 likes
  • 3 in conversation