BookmarkSubscribeRSS Feed
ScottBass
Rhodochrosite | Level 12

Hi,

 

SAS 9.3 on Windows

 

The SAS documentation http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#p1h2yn2en7gkb8n1mb... states:

 

When you want to use temporary tables that persist across SAS procedures and DATA steps with ODBC, you must use the CONNECTION=SHARED LIBNAME option. When you do this, the temporary table is available for processing until the libref is closed.

 

This is my libname statement:

 

LIBREF:  TMP
CONNECT: NOPROMPT="Driver={SQL Server Native Client 
10.0};Server=XXXXX,#####;Database=MyDatabase;Trusted_Connection=yes;"
OPTIONS: schema=tmp connection=shared

 

i.e. libname &libref odbc &connect &options;

 

This is my test code:

 

proc sql;
   connect using tmp;
   execute(
select *
into #temp
from master.sys.tables;
   ) by tmp;

   select * from connection to tmp (
      select * from tmp.#temp
   );
quit;

proc sql;
   connect using tmp;
   select * from connection to tmp (
      select * from tmp.#temp
   );
quit;

data work.foo;
   set tmp.'#temp'n;
run;

The first step works, the second and third ones give these errors:

 

ERROR: CLI describe error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 
       '#temp'. : [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be 
       prepared.

ERROR: File TMP.'#temp'n.DATA does not exist.

What bit am I not understanding from "...the temporary table is available for processing until the libref is closed."

 

Thanks...

 

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
5 REPLIES 5
RahulG
Barite | Level 11

Try creating with double hash i.e. global temporary table 

 

Change #temp to ##temp.

 

##temp would be available till conncetion is active.

LinusH
Tourmaline | Level 20

Have you seen this note:

http://support.sas.com/kb/48/566.html

Apart from @RahulG suggestion, check the ODBC driver version.

Data never sleeps
ScottBass
Rhodochrosite | Level 12

Ok, I think I can answer my own question.

 

I found this link, which was helpful: http://www.sascommunity.org/planet/blog/category/temp-table/

 

So I then created this test code (don't worry about the macro - it simply allocates the SQL Server libref via ODBC):

 

 

%libname_sqlsvr(libref=TMP,database=MyDatabase,options=schema=dbo connection=shared bcp=yes)
 
data tmp.'##class'n;
   set sashelp.class;
run;
 
proc sql;
   connect using tmp;
   execute(
      select *
      into ##systables
      from tempdb.sys.tables
   ) by tmp;
 
   select *
   from connection to tmp(
      select * from ##systables
   );
quit;
 
data class;
   set tmp.'##class'n;
run;
 
data systables;
   set tmp.'##systables'n;
run;
 
proc sql ;
   connect using tmp;
   create table systables as
   select *
   from connection to tmp (
      select * from ##systables
   );
   disconnect from tmp;
quit;
 
proc sql ;
   connect using tmp;
   create table class as
   select *
   from connection to tmp (
      select * from ##class
   );
   disconnect from tmp;
quit;

 

 

Things worked as expected/desired for #class, but not for #systables.  Even though the first SQL query against ##systables showed that ##systables exists (for a brief moment in time), I could never access it in a subsequent step.

 

I also did a search and replace of ## with #, then re-ran the process.  What was interesting is that, even though I never could see #class in tempdb via SSMS (because it is a different connection), I could access it from my SAS program (because the libref is keeping the connection open).  This is useful, as my temporary files could contain data that, while not super-sensitive, I'd prefer others cannot see via SSMS.  However, in this scenario, the final proc sql step failed; I can access #class via the libref, but not via explicit pass-through.

 

I tested with both connection=shared and connection=global with the same results.

 

Then the light bulb lit:  explicit pass-through isn't going through the SAS library, so any temporary tables created via explict pass-through won't be available after the connection (proc sql step) ends.  This also means that any library options, such as connection=shared, don't apply.  

 

I had SSMS open, viewing tempdb.Temporary Tables, refreshing after each SAS step.  I could see ##class, but never could see either ##systables, #systables, or #class.

 

Let me know if this analysis is flawed.  Sorry for the false alarm.  I hope this may help someone who finds this via search someday.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Patrick
Opal | Level 21

Hi Scott,

 

I guess your analysis is principally right. Question is now: Could the libname and the pass-through connection use the same connection so that you'll always get access to the temporary data.

 

Based on the example in the following link that should at least be possible for Oracle under SAS9.4

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

 

Cheers,

Patrick

 

ScottBass
Rhodochrosite | Level 12

Thanks @Patrickfor your reply.

 

I couldn't work out how to share/persist a connection on SQL Server when using explicit pass-through, which would be a requirement for my application.  I learned a bit more about SQL Server temporary tables and their support in SAS this thread, but I'm not quite there yet.

 

Sample code:

 

proc sql;
   connect using tmp;
   execute (
      select * into ##systables from tempdb.sys.tables;
   ) by tmp;

   select * from connection to tmp (select * from ##systables);
quit;

proc sql;
   connect using tmp;
   select * from connection to tmp (select * from ##systables);
quit;

If anyone knows how to get the 2nd query to work, please let me know.  AFAIK, I need a way to persist the first connection, and have the 2nd query re-use that same connection.

 

Bonus points if you can get this to work with #systables instead of ##systables.

 

Oh yeah...SAS 9.3 on Windows Server.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

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
  • 5 replies
  • 4362 views
  • 0 likes
  • 4 in conversation