-1

I have a scenario where I have several sql server data source tables. I have read only access to these sources. I cannot create permanent tables in Sql Server environment. I can however create temporary tables.

I thought of creating global temporary table out of scenario 1 result set and reference that in scenario 2(again create second global temp table in scenario 2 ) and 3rd global temp table out of third sql code. I have included generic sql below

Finally create a SAS data set out of each of these global temp tables.(We want to ensure all joins, data transformation needs to happen in sql server and not perform it in SAS)

Scenario1-

select * from table1 join table2

on table1.id=table2.id

where table1.product='Apple'

Scenario-2

Above result is then used in another query as

select * from table3 m

left join above _result_table t

on m.id=t.id

and the above result is again referenced further.

I tried researching online to find similar issue implementation and I could not find it.

I have tried below code, but this creates a SAS data set , I want to instead create a global temporary table so that another query such as below can reference it. How do I accomplish that? 

proc sql ;

connect to odbc(dsn=abc user=123 pw=****** connection=shared);

create table xyz as select * from

connectoin to ODBC

(

select * from table1 join table2

on table1.id=table2.id

where table1.product='Apple'

);DISCONNECT FROM ODBC;

QUIT;

Your help is greatly appreciated.

Thanks,

Alexey Sigida
  • 2,040
  • 9
  • 26
curious_mind
  • 29
  • 3

1 Answers1

0

Upon further research, I think I got part of the solution,-still working on creating sas data set out of temporary sql server table.

I created a temp table structure lets say ##abc,then i proceeded with following steps

PROC SQL;
CONNECT TO ODBC AS T1(DSN=SRC INSERTBUFF=32767 USER=UID PW="PD." CONNECTION=SHARED);
EXECUTE(
CREATE TABLE ##abc
(id int,
name varchar(50)
)by T1;

EXECUTE(INSERT ##abc
SELECT * FROM SqlServerTable
)by T1;
SELECT * FROM CONNECTION TO T1(SELECT * FROM ##abc);
QUIT;

I got the

curious_mind
  • 29
  • 3
  • If you want `##abc` to live past the end of that PROC SQL step then make sure you have defined an libref using the exact some settings as the CONNECT statement. From the SQL Server docs once the session that created the global temporary table ends the table will be removed. By defining the libref using a shared connection this will make the SQL server see the session as active as long as the libref is defined. – Tom Oct 02 '19 at 19:24