BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Anotherdream
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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.


View solution in original post

7 REPLIES 7
SASKiwi
PROC Star

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.


Anotherdream
Quartz | Level 8


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

alandool
Quartz | Level 8

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

Tom
Super User Tom
Super User

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;

Anotherdream
Quartz | Level 8

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!

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
alandool
Quartz | Level 8

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 14336 views
  • 0 likes
  • 5 in conversation