07-29-2015 06:27 AM
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;
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.
07-29-2015 06:52 AM
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.