03-04-2018 03:52 AM - edited 03-04-2018 05:21 AM
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;
03-05-2018 03:14 PM
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.
Need further help from the community? Please ask a new question.