topic Re: Proc SQL Execute Command SQL Server Temp Table Back to SAS in SAS Programming

Proc SQL Execute Command SQL Server Temp Table Back to SAS

andrewjmdata — Thu, 12 Jul 2018 10:52:17 GMT

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

Re: Proc SQL Execute Command SQL Server Temp Table Back to SAS

Oligolas — Thu, 12 Jul 2018 13:11:26 GMT

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#n0gz66qe8msnkyn1jgnhrr0l2k8y.htm

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

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

Re: Proc SQL Execute Command SQL Server Temp Table Back to SAS

andrewjmdata — Thu, 12 Jul 2018 13:14:43 GMT

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

Re: Proc SQL Execute Command SQL Server Temp Table Back to SAS

Oligolas — Fri, 13 Jul 2018 05:22:06 GMT

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

Re: Proc SQL Execute Command SQL Server Temp Table Back to SAS

LinusH — Fri, 13 Jul 2018 05:45:53 GMT

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...

Re: Proc SQL Execute Command SQL Server Temp Table Back to SAS

andrewjmdata — Wed, 18 Jul 2018 11:58:20 GMT

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