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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.