Desktop productivity for business analysts and programmers

sas code oracle table creation

Reply
Contributor
Posts: 63

sas code oracle table creation

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.

 

Super User
Posts: 5,388

Re: sas code oracle table creation

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
Super User
Posts: 19,170

Re: sas code oracle table creation

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. 

 

Respected Advisor
Posts: 4,137

Re: sas code oracle table creation

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

Occasional Contributor
Posts: 13

Re: sas code oracle table creation

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;

Super User
Posts: 19,170

Re: sas code oracle table creation


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 Smiley Happy

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

Occasional Contributor
Posts: 13

Re: sas code oracle table creation

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.

Respected Advisor
Posts: 4,137

Re: sas code oracle table creation

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

Occasional Contributor
Posts: 13

Re: sas code oracle table creation

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
Contributor
Posts: 63

Re: sas code oracle table creation

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.

.

 

Respected Advisor
Posts: 4,137

Re: sas code oracle table creation

@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

 

 

 

Ask a Question
Discussion stats
  • 10 replies
  • 255 views
  • 3 likes
  • 5 in conversation