using oracle to store sas datasets

[deleted_user]

Is it possible to use an oracle database to store SAS datasets?

Our fileserver is full but we have an oracle server that is underused. I'm looking for a way to use oracle to store some large SAS datasets and keep them available and usable by users. One thought is to either convert the dataset to an oracle table or sture the dataset as a blob in oracle.

A couple questions I have is how accessable would the sas dataset be and how it could be accessed

[Flip]

We store dta in Oricle all the time. It is an Oricle table and not a SAS dataset so you lose formatting etc.

[Patrick]

Hi Bill

You need SAS\Access to Oracle installed and licenced and the Oracle client installed - but once this is done it's really easy (I believe SAS\Access to ODBC could also work).

The libname statement (or metadat definition) for an Oracle table looks a bit different - but it's not too hard to get it done.

Once the library is defined you can access Oracle tables as if they were SAS tables.
SAS will translate your SAS statements to Oracle SQL statements as far as possible, send them to the DB, have it executed there, retrieve the result dataset and process it with the remaining statements.

You might need an Oracle DBA to define appropriate schematas and grants in the DB and - if the tables are large - a few more Oracle DBA tasks are beneficial like table partitioning.

It's not a must - but I would recommend it - that a SAS user/programmer gets some fundamental Oracle knowledge. It helps to understand the differences between SAS files and an Oracle table and to take full advantage of having the data stored in Oracle.

Having the data in a DB adds sometimes a few minor challenges but gives you also a lot of benefits like much better access control than with SAS\Share.
Challenges like one user reading a table while the other one wants to updata a row in this table just go away - or are at least solveable with reasonable effort.... and so on.

Just an opinion.

HTH
Patrick

[deleted_user]

We use Oracle all the time but only as a data source. I need to talk to the people that manage the database server and see if they see any issues first

[sbb]

From my perspective, with the stated objective "..a way to use oracle to store some large SAS datasets.." and doing so using an Oracle database architecture, are you treating the symptom here or the actual problem? And what happens when someone who is a SAS user needs to get access to the historical (SAS database) information which is housed on a different machine/server and in a totally foreign format?

Honestly, I sure hope that you plan to conduct some proof-of-concept and handle a few test scenarios with detailed recovery documentation along the way.

Scott Barry
SBBWorks, Inc.

[deleted_user]

first I have to do an analysis to see if it is even doable. There may be some procedural reason for us to not use the server for that purpose.