The code you supplied doesn't make any SAS tables at all - the ##test table is an SQL Server temp table. You could add CREATE TABLE WORK.TEST AS just before the final SELECT statement to create a SAS work library table before disconnecting from SQL server to copy the data from SQL Server into the SAS work library. Something like this should do it:
proc sql;
connect to odbc as oledb (datasrc="mssql-orion" user=orion password=Metadata0 ) ;
execute(
IF object_id('tempdb..##test') is not null
BEGIN
DROP TABLE ##test
END
create table orion.##test
(id int, name varchar(12)))by oledb;
execute(insert into orion.##test(ID,Name) values(1,'Mark')) by oledb ;
execute(insert into orion.##test(ID,Name) values(2,'Joe')) by oledb ;
create table WORK.TEST as
select * from connection to oledb (select * from orion.##test);
disconnect from oledb ;
quit;
In testing, I have found that once you disconnect from MSSQL Server, temp tables are deleted by MS SQL Server. For example:
proc sql;
connect to odbc as oledb (datasrc="mssql-orion" user=orion password=Metadata0 ) ;
execute(
IF object_id('tempdb..##test') is not null
BEGIN
DROP TABLE ##test
END
create table orion.##test
(id int, name varchar(12)))by oledb;
execute(insert into orion.##test(ID,Name) values(1,'Mark')) by oledb ;
execute(insert into orion.##test(ID,Name) values(2,'Joe')) by oledb ;
select * from connection to oledb (select * from orion.##test);
disconnect from oledb ;
quit;
This produces the results:
id | name |
---|---|
1 | Mark |
2 | Joe |
However, if you check in Microsoft SQL Server Management Studio (MSSMS) the table ##test no longer exists.
I tried this submitting just this in interactive mode:
proc sql;
connect to odbc as oledb (datasrc="mssql-orion" user=orion password=Metadata0 ) ;
execute(
IF object_id('tempdb..##test') is not null
BEGIN
DROP TABLE ##test
END
create table orion.##test
(id int, name varchar(12)))by oledb;
execute(insert into orion.##test(ID,Name) values(1,'Mark')) by oledb ;
execute(insert into orion.##test(ID,Name) values(2,'Joe')) by oledb ;
And as long as PROC SQL remained running, I could see the temp table ##test in MSSMS. As soon as I executed the DISCONNECT statement in SAS, table ##test disappeared from MSSMS, too.
I tried this directly in MSSMS (without using SAS). The table remained available in MSSMS until I shut down the program. Upon logging back in, table ##test was no longer available.
It then appears to me that this is normal behavior for MS SQL Server temp tables. Consider completing all the required work with the temp table before disconnecting from MS SQL Server, or using a normal table to do your work and explicitly then deleting it when finished.
The code you posted updates a temporary table in SQL Server but you are now saying it is a SAS WORK table? Where do you want the end result? A table in SAS or in SQL Server?
@Ja5ya wrote:
This doesn't work out for me as my dataset 'tt' is a temporary dataset in SAS. I'm trying to update it using joins with tables from SQL server.
The code you posted is a pure pass-through to SQL Server. It does NOTHING with SAS datasets.
As the term implies, temporary tables exist only for the duration of a connection.
The code you supplied doesn't make any SAS tables at all - the ##test table is an SQL Server temp table. You could add CREATE TABLE WORK.TEST AS just before the final SELECT statement to create a SAS work library table before disconnecting from SQL server to copy the data from SQL Server into the SAS work library. Something like this should do it:
proc sql;
connect to odbc as oledb (datasrc="mssql-orion" user=orion password=Metadata0 ) ;
execute(
IF object_id('tempdb..##test') is not null
BEGIN
DROP TABLE ##test
END
create table orion.##test
(id int, name varchar(12)))by oledb;
execute(insert into orion.##test(ID,Name) values(1,'Mark')) by oledb ;
execute(insert into orion.##test(ID,Name) values(2,'Joe')) by oledb ;
create table WORK.TEST as
select * from connection to oledb (select * from orion.##test);
disconnect from oledb ;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.