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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: