BookmarkSubscribeRSS Feed
vallsas
Pyrite | Level 9

hi,

 

what is the code to create a table in oracle schema directly . as space problem in our local machine. i think this best idea when we have too big data . 

what kind of techiniques to be remember while dong this kind of table creation.

 

10 REPLIES 10
LinusH
Tourmaline | Level 20
You can create it with SAS syntax directly.
But if you have concerns about data size, you probably want to involve your Oracle DBA's so they can recommend/physically design the table structure.
Data never sleeps
Reeza
Super User

Depends on your computers and set up. So you should talk to your IT team on your options. 

 

Ideally you can write to the server but if you all do that, you may have other issues. And Big Data should be discussed with them because it will impact server performance for everyone. And make sure your definition of Big Data matches there's...for example, 2 million rows is not big data for SAS, locally or on a server. 

 

Patrick
Opal | Level 21

If the sole reason is a lack of disk space then may be consider to get additional disk space. 

kanivan51
Obsidian | Level 7

Hi, vallsas!

 

First, you need to assign the library:

 

libname _LIB_NAME oracle

user=your_login

password="your_pass"

path='allias_of_connection' /*or TNS of connection*/

schema=where_will_be_your_table_on_server

preserve_tab_names=YES;

 

And then you can create table in any convenient way.

For example:

 

 

data _LIB_NAME.your_table;

x=1;

run;

Reeza
Super User

@kanivan51 wrote:

Hi, vallsas!

 

First, you need to assign the library:

 

libname _LIB_NAME oracle

user=your_login

password="your_pass"

path='allias_of_connection' /*or TNS of connection*/

schema=where_will_be_your_table_on_server

preserve_tab_names=YES;

 

And then you can create table in any convenient way.

For example:

 

 

data _LIB_NAME.your_table;

x=1;

run;


Often you won't have permissions to do this by default, at least in a good setup 🙂

Your IT may need to set you up, this is why we've been saying talk to your IT dept.

kanivan51
Obsidian | Level 7

If vallsas has access to the database on the server, and knows how to connect, and has the privileges to create tables there, then my advice should help.
Otherwise, of course, vallsas must first contact IT.

Patrick
Opal | Level 21

@kanivan51

The question is if you should use an Oracle database as data dump.

For more permanent and "serious" table structures I normally prefer to use pass-through SQL and create the tables "properly" eventually also adding structural definitions which SAS won't generate (like partitioning).

kanivan51
Obsidian | Level 7
It seems that vallvas must to clarify his needs. There are many options for solving the problem. The question is what is optimal for him
vallsas
Pyrite | Level 9

the requirement is very simple, as we have options to create through LIBNAME statement. as it is very stright and simple.

 

where as , as per my experience if we have 20 gb table to create in EG its not possible directly as it will take too much time or some times system getting hang.

 

so if we submit the  orcale code in EG , so that the code will create direclty in oracle instead of sas work and then create a table in oacle.

 

thanks.

.

 

Patrick
Opal | Level 21

@vallsas

To create 20GB of SAS data takes on my several years old laptop with a not so fast local disk less than a minute. The bottleneck is disk I/O.

 

If you load your SAS data into Oracle then the bottleneck is highly likely network throughput. You will then also have to write all your SAS code using this data in Oracle in a way that it fully executes in-database as else you're going to have a lot of additional network traffic and that's certainly going to be slower than any disk I/O.

 

You haven't really told us how your processes look like but from the few bits and pieces I feel you probably are trying to solve an issue with the wrong remedy.

 

My be investigate first why writing SAS data to your WORK area takes that much time. Eventually use the SAS iotest utility to measure disk I/O.

http://support.sas.com/kb/51/659.html

http://support.sas.com/kb/51/660.html

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1629 views
  • 3 likes
  • 5 in conversation