Hello all. Does anyone happen to know if it's possible to create a temporary table within a sql server pass through. Aka, is there a way to make the following code work?
proc sql;
connect to odbc as myodbc (dsn=odbc_MYODBC_yay);
create table finalresults as
select * from connection to myodbc
(select *
into #temporaryTable
from mastertable
where mycolumn='0001'
select * from #temporarytable);
disconnect from myodbc;
quit;
Obviously this is a simplified version of the code I would like to write, however if possible it would results in much easier code to maintain, transfer, and a general efficency gain.
I should pre-face this with the note that I know a workaround for this. It's simple to create a stored procedure to do everything you need, and then pull from the table created within the stored procedure, and then drop the table (or a few other work arounds I've used before).
SO I don't need a solution to the problem per-say, I just need to know if this exact solution is someway possible!
Thanks!
Thanks so much!
Brandon
Yes, no problem. Check out the EXECUTE statement:
proc sql;
connect to odbc as myodbc (dsn=odbc_MYODBC_yay);
execute(create table #temporaryTable as
select from mastertable
where mycolumn='0001') by myodbc;
quit;
You can run any SQL Server-specific statements this way, even stored procedures.
Yes, no problem. Check out the EXECUTE statement:
proc sql;
connect to odbc as myodbc (dsn=odbc_MYODBC_yay);
execute(create table #temporaryTable as
select from mastertable
where mycolumn='0001') by myodbc;
quit;
You can run any SQL Server-specific statements this way, even stored procedures.
So I have a few issues with doing things this way. The first is, if I define a temporary table with two ## (a global temporary table) I cannot then go into sql server and open this temporary table (or create a second sql server pass through functionality to pull data from the intermediate temporary table.
I actually need to create 3-4 temporary tables, and then pull the final results into a SAS dataset, where that result is simply the select from the last temporary table created.
Brandon
Hi Brandon,
Did you ever receive a reply or figure out how to do this? I have a need to do something similar.
"I actually need to create 3-4 temporary tables, and then pull the final results into a SAS dataset, where that result is simply the select from the last temporary table created."
Alan
I am not sure about using ODBC, but with other SAS/Access products you can keep the temporary tables by keeping your connection to the server open using a LIBNAME.
LIBNAME xxx odbc ..... ;
proc sql ;
connect to odbc .... ;
execute ( ... ) by odbc ;
quit;
I did alandool. It appears that if you make independent execute queries as SASKiwi does above this can be done. Just make x different queries, 1 for each temp table plus 1 additional to pull it into the sas system!
Why would you try to outsmart a SQL optimizer either at the SAS site or at the DBMS site?
SQL is using temporary tables when it decides they are needed.
Thanks, Anotherdream and Tom! I was able to do what was needed by enclosing sql statements as mentioned:
exec( ...... ) by sqlsvr;
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.