SQLEXEC() function in PROC DS2

[b1958]

Hi

I have the code shown below that puts an error in the log like this:

 delete from work._temp1 where a = 10
 -1
 ERROR: Error during execute of SQL statement: delete from work._temp1 where a = 10
 NOTE: Execution succeeded. No rows affected.

I can see it happens because I try to delete a record not found in the table. In the real case that I develop, this will certainly be the case from time to time. Is there any way I can avoid sas creating the ERROR:

If I do the same in PROC SQL, no ERROR appears in the log. 

data _temp1;
a=100;
run;

proc ds2 libs=work;
data _null_;
method run();
	declare varchar(500) sql;
	declare varchar (20) t;
	declare varchar(20) l;
	declare  varchar(5) n;
	declare int rc;
	t = '_temp1';
	l='work';
	n='10';
	sql = cat('delete from ',l, '.',t, ' where a = 10');
	put sql;	
	rc = sqlexec(sql);
	put rc ;
end;
enddata;
run;
quit;

[ChrisNZ · Accepted solution]

Same here.

I have no workaround (option SCOND=NONE seems ineffective), but would report it to tech support as a bug. 

[ChrisNZ]

There one workaround of course, but it can be very expensive in the absence of an index: run a few macros lines to test that a record exists before calling the delete statement. 

[b1958]

SAS support knew about the problem. There exists a feature request for getting this fixed.

SAS support recommended using the SQLSTMT package.