Proc SQL Execute Command SQL Server Temp Table Back to SAS

[andrewjmdata]

I have written code in proc sql using the execute command to run sql code. This all works well and I can create perm/temp files in the SQL db.

For our production version we will only be able to create temp tables (#tablename/##tablename). What I will need to do is bring this temp file into actual sas to create a report and then output to client (Excel/PowerBI). I am conscious that the temp table will only exist for the execute(….) by sqlserver session. Do you know how I can do this?

Thanks

Andrew

[Oligolas]

Hi,

we use Oracle but...

there is a "SAS Access Interface to Microsoft SQL Server" chapter in the SAS documentation:

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n0gz66qe8msnkyn1jg...

libname mydblib sqlsvr
   noprompt="uid=testuser;
   pwd=testpass;
   dsn=sqlservr;"
   stringdates=yes;

proc print data=mydblib.customers;
   where state='CA';
run;

[andrewjmdata]

Thanks.

This might be specific to SQLServer,  but does your method mean temporary tables can be pulled into SAS as well?

In SQLServer they are denoted by #tablename and only exist for the session of the user.

Cheers

Andrew

[Oligolas]

Hi, I don't know you'll have to give it a try

[LinusH · Accepted solution]

I'm pretty sure that those tables exist as long as the connection lives (that's until a DISCONNECT FROM or a PROC SQL QUIT).
You could verify this by fetching the data using SELECT * FROM CONNECTION TO...

[andrewjmdata]

Thanks, I was nicely surprised to see it did work. Many thanks!