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;
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;
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;
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
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
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;
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
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.