Sort table in oracle through sas

Reply
Contributor
Posts: 27

Sort table in oracle through sas

Friends,

i have two tables in oracle environment which are associated by libname RnD. I want that after ETL job is completed these two tables gets sorted

by variables. Below are the code written:

PROC SORT DATA= RnD.LOAD_MASTER_TABLE;

BY LOADRUN_DATE;

RUN;

PROC SORT DATA= RnD.LOAD_TRANS_TABLE;

BY END_TIME JOB_NAME;

RUN;

But it gives error message:

ERROR: The ORACLE table LOAD_MASTER_TABLE has been opened for OUTPUT. This table already exists, or

       there is a name conflict with an existing object. This table will not be replaced. This engine

       does not support the REPLACE option.

ERROR: The ORACLE table LOAD_TRANS_TABLE has been opened for OUTPUT. This table already exists, or

       there is a name conflict with an existing object. This table will not be replaced. This engine

       does not support the REPLACE option.

Plz suggest how can i sort these oracle tables in SAS. Using explicit pass through , but i dont want to create a new table or increase job execution time.

Super Contributor
Posts: 644

Re: Sort table in oracle through sas

Posted in reply to rohitguptaecb

Oracle does not support sorting tables.  Tables can be indexed, and can then be accessed by SAS as if they were sorted on the index variables.

Richard

PROC Star
Posts: 1,167

Re: Sort table in oracle through sas

Posted in reply to rohitguptaecb

If you post your objective for sorting the table, we may be able to give some useful advice. I can't see a reason for just sorting a table, without wanting to do something with the results.

Tom

Contributor
Posts: 69

Re: Sort table in oracle through sas

Posted in reply to rohitguptaecb

You can't sort an existing Oracle table, or any RDBMS table I think.  You have to create a new table, drop the old table, and rename the new one to have the old name.

I'm not sure that proc sort will work--I've never used it against an RDBMS table.  If it doesn't then use PROC SQL create table with a group by statement.

Not sure why you want to sort the table.  Build an index if you're looking to speed up selecting where loadrun_date=<date>. 

PROC SORT DATA= RnD.LOAD_MASTER_TABLE out=RnD.sorted_load_master_table;

BY LOADRUN_DATE;

RUN;

proc datasets library=RnD;

     drop load_master_table;

     run;

     change sorted_load_master_table=load_master_table;

     run;

quit;

Ask a Question
Discussion stats
  • 3 replies
  • 566 views
  • 0 likes
  • 4 in conversation