Skip to Main Content
  • Questions
  • How can SAS datasets be loaded into Oracle tables

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Chandra.

Asked: February 01, 2007 - 2:23 pm UTC

Last updated: December 02, 2009 - 6:58 am UTC

Version: 9.0.4

Viewed 10K+ times! This question is

You Asked

I have a requirement to load SAS datasets into Oracle table. This process needed to be repeatable for different SAS datasets to different oracle table.
The one way I though of was getting SAS datasets in MSAccess and then get ito Oracle. Is that the only way or their is better/efficient way?
A quick response will be appreciated.

Thanks,

and Tom said...

well, you can have SAS dump data to "csv" (comma separated values) files, then they can easily be loaded using SQLLDR or external tables.

if you have odbc access to SAS, there is generic connectivity as well:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why not use SAS?

Mette, February 02, 2007 - 7:26 am UTC

Hi there

We just use SAS to put the data into Oracle. You must have the SAS Access for Oracle module.

We use:
- proc export
- data steps
- proq sql

and every other procedure using normal SAS datasets can read/write Oracle tables.

Proc sql;
insert into oracle.tabxx select name, address, email from work.sasds;
quit;

You can also do normal DDL, stored procs etc.

Ypu may choose to use the SAS SQL implementation (access to all SAS functions) or use "native" oracle having access to all Oracle functions.

It works very nice, performs OK, uses paralelism where possible.

regards
Mette
Tom Kyte
February 02, 2007 - 11:01 am UTC

thank you very much ! appreciate that

SAS Read

A reader, December 01, 2009 - 8:34 pm UTC

Hi Tom,

We have a situation where SAS queries read Oracle DW table (10gR1) through ODBC mostly based upon creation date column(with timestamp). However, the queries use SAS native functions to get the data in YYYYMM format. I just want to validate my approach..
When any front end tool uses native function to query Oracle tables, all data (without native function) is first made available to the tool, and then the native function is applied by the tool. If that is the case, then I am suggesting that we should create a column in the table like - month key, and ETL can populate that column as YYYYMM (based upon creation date), and users can apply predicate WHERE month key = YYYYMM. I can also partition the table on month key, thus improving the performance...

Thanks

Tom Kyte
December 01, 2009 - 10:28 pm UTC

what is a 'native function'?


... I can also partition the table on month key, thus improving the performance... ...

first, you can partition a table on a date or timestamp column by month.
second, your "thus" does not follow from "partition" - it might improve, degrade or not affect performance at all.

SAS Function

A reader, December 02, 2009 - 1:16 am UTC

Hi Tom,

Thanks.
SAS function is datepart.
Regards
Tom Kyte
December 02, 2009 - 6:58 am UTC

why do you care if SAS spends a couple of seconds converting the date into YYYYMM?

You have a date in the database, you have done it right.
Let SAS convert it to whatever it wants.

if you are worried about them full scanning an entire table to find a single month and would like them to use partition elimination or an index, then tell the SAS people to query
where date_column >= to_date( :x,'YYYYMM' )
  and date_column < add_months(to_date( :x, 'YYYYMM' ), 1 )


it'll have the same "effect" as you adding a redundant column that you must maintain so they can code

where fake_date = :x


only it'll be much more efficient and straightforward, something you can do this afternoon if you want.

Thanks

A reader, December 02, 2009 - 2:28 pm UTC


SAS Datasets from Oracle

Nisha, July 11, 2012 - 6:49 am UTC