I was in the midst of replying to the OP by @Quentin when the solution(s) were posted.
However, since I put some work into this, here goes, apologies if some of this is redundant...
First of all, have a look at https://www.red-gate.com/simple-talk/sql/t-sql-programming/temporary-tables-in-sql-server/. It's a bit long, but goes into details about temp tables and table variables.
Second, do this in SQL Server Management Studio:
1) In query window #1:
SELECT name, database_id INTO tempdb.dbo.table1 FROM master.sys.databases
SELECT name, database_id INTO #table2 FROM master.sys.databases
SELECT name, database_id INTO ##table3 FROM master.sys.databases
SELECT * FROM tempdb.INFORMATION_SCHEMA.Tables WHERE table_name LIKE '%table%'
SELECT * FROM tempdb.dbo.table1
SELECT * FROM #table2
SELECT * FROM ##table3
2) In a second query window:
SELECT * FROM tempdb.INFORMATION_SCHEMA.Tables WHERE table_name LIKE '%table%'
SELECT * FROM tempdb.dbo.table1
SELECT * FROM ##table3
SELECT * FROM #table2
In the 2nd query window, you can see that table1, #table2, and ##table3 exist, but you can only access table1 and ##table3; #table2 can only be accessed by the process ID that created it. (You can submit SELECT @@spid to get the process ID, but it's also displayed next to your userid in SSMS).
Now, close the first query window, and resubmit the code in the second query window. You'll see that #table2 and ##table3 have disappeared. So, the Temporary Tables persist only for the life of the process ID that created them, and global temporary tables can be accessed by other process ID's, but again *they only exist during the life of the process ID that created them*.
With that in mind, submit the below code in SAS EG (modify as required).
* set desired SAS options ;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options msglevel=I;
options fullstimer;
options mprint mrecall;
options compress=binary;
options ls=max;
options nocenter;
options lognumberformat=1;
libname TMP odbc NOPROMPT="Driver={SQL Server Native Client 10.0};Server=XXXXXXXX;Database=TempDB;Trusted_Connection=yes;" bulkload=yes schema=dbo ;
* #1A ;
proc sql;
connect using tmp;
execute by tmp (
SELECT name, database_id INTO tempdb.dbo.table1 FROM master.sys.databases;
);
select * from connection to tmp (
SELECT * FROM tempdb.dbo.table1;
);
execute by tmp (
DROP TABLE tempdb.dbo.table1;
);
quit;
* #1B ;
proc sql;
connect using tmp;
execute by tmp (
SELECT name, database_id INTO tempdb.dbo.table1 FROM master.sys.databases;
);
quit;
proc sql;
connect using tmp;
select * from connection to tmp (
SELECT * FROM tempdb.dbo.table1;
);
execute by tmp (
DROP TABLE tempdb.dbo.table1;
);
quit;
* #2A ;
proc sql;
connect using tmp;
execute by tmp (
SELECT name, database_id INTO #table2 FROM master.sys.databases;
);
select * from connection to tmp (
SELECT * FROM #table2;
);
execute by tmp (
DROP TABLE #table2;
);
quit;
* #2B ;
proc sql;
connect using tmp;
execute by tmp (
SELECT name, database_id INTO #table2 FROM master.sys.databases;
);
quit;
proc sql;
connect using tmp;
select * from connection to tmp (
SELECT * FROM #table2;
);
execute by tmp (
DROP TABLE #table2;
);
quit;
* #3A ;
proc sql;
connect using tmp;
execute by tmp (
SELECT name, database_id INTO ##table3 FROM master.sys.databases;
);
select * from connection to tmp (
SELECT * FROM ##table3;
);
execute by tmp (
DROP TABLE ##table3;
);
quit;
* #3B ;
proc sql;
connect using tmp;
execute by tmp (
SELECT name, database_id INTO ##table3 FROM master.sys.databases;
);
quit;
proc sql;
connect using tmp;
select * from connection to tmp (
SELECT * FROM ##table3;
);
execute by tmp (
DROP TABLE ##table3;
);
quit;
* #4 ;
proc sql;
connect using tmp;
create table tmp.'#class2'n as
select * from sashelp.class;
select * from connection to tmp (
SELECT * FROM #class2;
);
execute by tmp (
DROP TABLE #class2;
);
quit;
* #5 ;
proc sql;
connect using tmp;
create table tmp.'##class3'n as
select * from sashelp.class;
select * from connection to tmp (
SELECT * FROM ##class3;
);
execute by tmp (
DROP TABLE ##class3;
);
quit;
My conclusion is that the connections from SAS to SQL Server are transient, with each connection resulting in a new process ID. This is then problematic for the reasons above. Furthermore, global temporary tables can have security issues if the data is sensitive.
After pulling out my hair a few times trying to make temporary tables "work", my usual approach has been to create a "tmp" schema in my database, writing "temporary" tables to the tmp schema, and dropping the tables once I'm done with them.
Hope this helps...
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.
