Update/Delete Statements in Proc SQL

[Vemula]

Hi All,

Please let me know if Update and Delete statements works in Proc SQL in the temporary tables (#temp_studies) while connecting to database backend (Rave). I am trying to update and delete few rows in temporary tables created, but it is not working. Below is the syntax which i have used for delete statement. 

Proc Sql;

%connect_to_rave(datasrc=&datasrc.);

execute ( create table #temp (Subject nvarchar(4000), Subjectid int, Sex Nvarchar(100));

insert into #temp('Saketh', 101, ' ')

delete from #temp where sex eq ' '

) by sqlsvr;

create table temp as select * from connection to sqlsvr(select * from #temp);

execute ( drop #temp) by sqlsvr;

quit; 

[noling]

Hi, looks like you have some syntax errors. Are you using "#" in your actual code?

Here's an example changing a work table:

data want;
	set sashelp.cars;
	if _N_ =11 then stop;
	keep make EngineSize;
run;

proc sql;
	insert into want values('New Make', 99);
	delete from want where make ='Land Rover';
quit;

[Vemula]

Hi Noling,

Thanks for your response and the example. 

Yes, '#' is used for creating temporary tables while connecting to database. If the syntax error is there, then that will be generated in the log of the program as an error. But, it didn't. The code is working now after inserting 'execute (' and ') by sqlsvr' for each update and delete statements. 

Regards,

Yashoda Vemula

[Tom · Accepted solution]

Why are you trying to execute more than one statement at a time? Does SQLSERVER and/or SAS/Access allow that?

And if it did you appear to missing a semi-colon between your last two statements.

Try doing the statements one at a time.

proc sql;
%connect_to_rave(datasrc=&datasrc.);
execute ( 
create table #temp (Subject nvarchar(4000), Subjectid int, Sex Nvarchar(100))
) by sqlsvr;
execute ( 
insert into #temp('Saketh', 101, ' ')
) by sqlsvr;
execute ( 
delete from #temp where sex eq ' '
) by sqlsvr;
create table temp as select * from connection to sqlsvr (select * from #temp);
execute ( drop #temp) by sqlsvr;
quit; 

[Vemula]

Hi Tom,

Thanks for your response and the example. 

Yes, it will accept multiple statements at a time. It is not generating any error even though the semi-colon is not provided. It tried with each statement accompanied by 'execute (' and ' ) by sqlsvr)' followed by a semi-colon. It worked. 

Thanks much for your inputs. It really helped me to debug the issue. 

Regards,

Yashoda Vemula