Not able to remove duplicate records from oracle table

Reply
Occasional Contributor
Posts: 19

Not able to remove duplicate records from oracle table

Hi All,

I am trying to remove the duplicate records from oracle table using proc sort and nodupkey as below :-

proc sort data=libname.XXX nodupkey;

by YYY ZZZ;

run;

When I tried to execute this code I am getting the below error.

ERROR: The ORACLE table XXX 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

Please can anybody tell me how to resolve this at the earliest.

Regards,

Siva

Super User
Posts: 7,809

Re: Not able to remove duplicate records from oracle table

Posted in reply to sivaram_veerabagu

Use pass-through SQL to do it in the Oracle DBMS. Or pull the data over to SAS, do the proc sort there, and then replace the table in the DBMS.

Background: when SAS sorts a SAS dataset, it creates a new dataset file with .sas7bdat.lck extension parallel to the original dataset and, when finished, removes the old .sas7bdat and renames .sas7bdat.lck to .sas7bdat. This operation is clearly not possible in the DBMS.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 19

Re: Not able to remove duplicate records from oracle table

Posted in reply to KurtBremser

Thanks KurtBremser

Super User
Super User
Posts: 7,970

Re: Not able to remove duplicate records from oracle table

Posted in reply to sivaram_veerabagu

Can I ask why you want to remove records from a database using SAS?  Would you not be better off doing such db admin on the database?

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