SQLEXEC() function in PROC DS2

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

SQLEXEC() function in PROC DS2

[ Edited ]

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;

 


Accepted Solutions
Solution
‎03-05-2018 02:22 AM
PROC Star
Posts: 2,128

Re: SQLEXEC() function in PROC DS2

Same here.

 

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

View solution in original post


All Replies
Solution
‎03-05-2018 02:22 AM
PROC Star
Posts: 2,128

Re: SQLEXEC() function in PROC DS2

Same here.

 

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

PROC Star
Posts: 2,128

Re: SQLEXEC() function in PROC DS2

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. 

Occasional Contributor
Posts: 17

Re: SQLEXEC() function in PROC DS2

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

 

SAS support recommended using the SQLSTMT package. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 168 views
  • 0 likes
  • 2 in conversation