BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
Flip
Fluorite | Level 6
We store dta in Oricle all the time. It is an Oricle table and not a SAS dataset so you lose formatting etc.
Patrick
Opal | Level 21
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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.

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!

How to Concatenate Values

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.

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
  • 5 replies
  • 833 views
  • 0 likes
  • 4 in conversation