topic Re: Creating temp table in sql server pass through? in SAS Programming

Creating temp table in sql server pass through?

Anotherdream — Wed, 29 May 2013 22:15:41 GMT

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

Re: Creating temp table in sql server pass through?

SASKiwi — Thu, 30 May 2013 01:11:12 GMT

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.

Re: Creating temp table in sql server pass through?

Anotherdream — Thu, 30 May 2013 13:57:05 GMT

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

Re: Creating temp table in sql server pass through?

alandool — Fri, 03 Jan 2014 14:26:50 GMT

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

Re: Creating temp table in sql server pass through?

Anotherdream — Fri, 03 Jan 2014 15:32:07 GMT

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!

Re: Creating temp table in sql server pass through?

Tom — Sat, 04 Jan 2014 17:03:26 GMT

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;

Re: Creating temp table in sql server pass through?

jakarman — Sun, 05 Jan 2014 09:52:57 GMT

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.

Re: Creating temp table in sql server pass through?

alandool — Sun, 05 Jan 2014 18:00:28 GMT

Thanks, Anotherdream and Tom! I was able to do what was needed by enclosing sql statements as mentioned:

exec( ...... ) by sqlsvr;