SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Loading SAS data into Oracle

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 136
Accepted Solution

Loading SAS data into Oracle

 

Hi,

 

       I have a SAS table of 10,000 records with 7 columns to be loaded into Oracle. One of my variable is "Row_Num" values as 1-10,000 is sorted and loaded into Oracle. When I loaded this table into Oracle it is not as same sort order as SAS Table. Is there a way I can send the data to Oracle in the same way how it was there in SAS table.

 

I used PROC APPEND with DBCOMMIT=0 to load SAS data to an empty Oracle table. I belive It is doing a parallel batch insert randomly. 

When I tried to use bulkload=yes, I get the following error:

ERROR: BCP=/BULKLOAD= option not supported by this datasource.

 

Environment:

Connected to Oracle using ODBC, SAS 9.4 M2( Linux), SAS EG 7.12 running through CITRIX (Windows server 2012)

 

Thanks,

Suryakiran

Thanks,
Suryakiran

Accepted Solutions
Solution
‎07-11-2017 11:21 AM
PROC Star
Posts: 1,167

Re: Loading SAS data into Oracle

Posted in reply to SuryaKiran

It's a base concept of relational databases that you can't assume the records will be in any sequence. As stated, you need to use an "ordered by" clause to retrieve them in a sequence. Even if they were loaded in a sequence, again it's a base concept that in the absence of an "ordered by" clause, you can't expect the records to be returned in any particular sequence.

 

Tom

View solution in original post


All Replies
Super User
Posts: 5,430

Re: Loading SAS data into Oracle

Posted in reply to SuryaKiran
Perhaps this more of an Oracle question rather than a SAS one.
I think that RDBMS usually doesn preserve row order in the physical storage. That shouldn't be a problem in your case since you already have a row_num column which could do ORDER BY on (or be sorted implicit on by using BY from any SAS step).
Data never sleeps
Frequent Contributor
Posts: 83

Re: Loading SAS data into Oracle

Posted in reply to SuryaKiran

I don't believe Oracle will retain the ordering of a table when loading or storing data, unless maybe you use an Index-Organized table.  Even with an IOT (which I actually don't know a whole lot about) I think the index is generally used for faster access than actually storing the data in order on disk.  Normally you'd just sort the data to whatever order you want when you're accessing it.

Respected Advisor
Posts: 4,173

Re: Loading SAS data into Oracle

[ Edited ]
Posted in reply to SuryaKiran

@SuryaKiran

Oracle doesn't work this way and you always need an Order By to control the order of queries.

 

What triggered your question? Why is the sort order important to you?

Solution
‎07-11-2017 11:21 AM
PROC Star
Posts: 1,167

Re: Loading SAS data into Oracle

Posted in reply to SuryaKiran

It's a base concept of relational databases that you can't assume the records will be in any sequence. As stated, you need to use an "ordered by" clause to retrieve them in a sequence. Even if they were loaded in a sequence, again it's a base concept that in the absence of an "ordered by" clause, you can't expect the records to be returned in any particular sequence.

 

Tom

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 216 views
  • 0 likes
  • 5 in conversation