Proc SQL Execute Command SQL Server Temp Table Back to SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Proc SQL Execute Command SQL Server Temp Table Back to SAS

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


Accepted Solutions
Solution
11 hours ago
Super User
Posts: 5,878

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

Posted in reply to andrewjmdata
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...
Data never sleeps

View solution in original post


All Replies
Regular Contributor
Posts: 164

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

Posted in reply to andrewjmdata

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;
________________________

- Cheers -

Occasional Contributor
Posts: 14

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

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

Regular Contributor
Posts: 164

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

Posted in reply to andrewjmdata

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

________________________

- Cheers -

Solution
11 hours ago
Super User
Posts: 5,878

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

Posted in reply to andrewjmdata
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...
Data never sleeps
Occasional Contributor
Posts: 14

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 80 views
  • 0 likes
  • 3 in conversation