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
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;
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,
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...
You could create the table with an explicit pass-through and the use a simple proc sql insert statement to populate it.
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;
Thanks, Bruno! This is exactly what I need.
The "create and insert" solution would probably work too but it's just inconvenient.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.