ERROR 457-185: The REPLACE statement is not valid on a data set that is opened for OUTPUT.

[tansel]

Hi everyone,

We have an input excel that we read some data from multiple sources by vba and then importing some of it to the sas lib. We are aiming to import the new day data everyday and cumulate the data date by date in one table. 

for ex: we have a currency fx rates data that contains 21 th of october at market.IMP_EXCH datatable. And trying to add 22 th of october to it by the code below:

%let data='/sasdata/input_22102019.xlsx';

PROC IMPORT OUT= EXCH
DATAFILE= &data
DBMS=xlsx REPLACE;
SHEET="fxrates"; /* fx rates data is in the fxrates sheet of the excel */
GETNAMES=YES;
run;

data market.IMP_EXCH;
set EXCH
market.IMP_EXCH;
REPLACE;
run;

but we got an error message like this:

ERROR 457-185: The REPLACE statement is not valid on a data set that is opened for OUTPUT. 

the reason for adding a replace statement to code is preventing duplication in table. does any one have any idea as a solution?

[ballardw]

---

@tansel wrote:

Hi everyone,

We have an input excel that we read some data from multiple sources by vba and then importing some of it to the sas lib. We are aiming to import the new day data everyday and cumulate the data date by date in one table. 

for ex: we have a currency fx rates data that contains 21 th of october at market.IMP_EXCH datatable. And trying to add 22 th of october to it by the code below:

%let data='/sasdata/input_22102019.xlsx';

PROC IMPORT OUT= EXCH
DATAFILE= &data
DBMS=xlsx REPLACE;
SHEET="fxrates"; /* fx rates data is in the fxrates sheet of the excel */
GETNAMES=YES;
run;

data market.IMP_EXCH;
set EXCH
market.IMP_EXCH;
REPLACE;
run;

but we got an error message like this:

ERROR 457-185: The REPLACE statement is not valid on a data set that is opened for OUTPUT.

does any one have any idea as a solution?

---

Simple: Remove the Replace statement in the data step.

BTW you should be extremely cautious about using the :

Data someset;

    set someset

    ;

approach. If you have any data manipulation involved and make a logic error you have replaced the data set and may not be able to recover very easily.

I would suggest Proc Append instead of that data step but Proc Import with XLSX are pretty poor about generating identical variable properties.

[tansel]

thanks for reply. i also try it and its working. but my concern is duplicating the data in table. (for example: inserting same day data for 2 or 3 times). have you got any suggestion for preventing it?

[Reeza]

Look into modify/update instead then. You need some way to identify that records may be duplicate, how would that be defined?

[tansel]

first colomn of the current data is  as_of_date column. it can defined if "the first column of the next day's data" and "dates that are currently stored at the first column of current table" is matched. the picture below is a sample data from Sas database. Just think that I m trying to add the new day fx rates to this table everyday. (ex: a table with start 24/10/2019 for today). i dont want to duplicate it if there is already some data which has a 24/10/2019 date in the DATE column. this is the reason why i write a replace statement.

many thanks for your helpSample Data From Database table

[Reeza · Accepted solution]

That's not what REPLACE does, please read the documentation on what it actually does. It replaces the whole file, it doesn't pertain to specific records. UPDATE will apply to specific records.

[ballardw]

One way to avoid duplications might be:

In this code var1 var2 var3 are a generic list of sufficient variables to identify unique records.

proc sort data=market.Imp_exch;
  by var1 var2 var3;
run;

proc sort data=exch;
   by var1 var2 var3;
run;


data market.imp_exch;
   update market.imp_exch
         exch
   ;
   by var1 var2 var3;
run;

The market.imp_exch should only need to be sorted once unless you do something else to possibly change the order of variables.