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;
Same here.
I have no workaround (option SCOND=NONE seems ineffective), but would report it to tech support as a bug.
Same here.
I have no workaround (option SCOND=NONE seems ineffective), but would report it to tech support as a bug.
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.
SAS support knew about the problem. There exists a feature request for getting this fixed.
SAS support recommended using the SQLSTMT package.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.