topic Re: SQLEXEC() function in PROC DS2 in SAS Programming

SQLEXEC() function in PROC DS2

b1958 — Sun, 04 Mar 2018 10:21:31 GMT

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;

Re: SQLEXEC() function in PROC DS2

ChrisNZ — Mon, 05 Mar 2018 06:10:51 GMT

Same here.

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

Re: SQLEXEC() function in PROC DS2

ChrisNZ — Mon, 05 Mar 2018 20:14:20 GMT

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.

Re: SQLEXEC() function in PROC DS2

b1958 — Tue, 10 Apr 2018 12:45:23 GMT

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

SAS support recommended using the SQLSTMT package.