Create global temporary table from result of sql code with multi joins in SAS

[SoloDolo]

Hi SAS Experts,

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,

Solo

[Patrick]

You need an EXECUTE() block like shown here .

[SoloDolo]

Hi Patrick,

Thanks for the link. I could not create temporay table as 

PROC SQL;

CONNECT TO ODBC AS A(DSN=SRC USER=ABC PW="***" CONNECTION=GLOBAL);

EXECUTE(

CREATE TABLE ##T1 AS

SELECT * FROM ABC);

QUIT;

But was able to create it as

PROC SQL;

CONNECT TO ODBC AS A(DSN=SRC USER=ABC PW="***" CONNECTION=GLOBAL);

EXECUTE(

create table ##T1

(

COL1 VARCHAR(30),

COL2 VARCHAR(30)

))BY A;

and populate the temp table as below

PROC SQL;

CONNECT TO ODBC AS A(DSN=SRC USER=ABC PW="***" CONNECTION=GLOBAL);

EXECUTE(

INSERT ##T1

SELECT * FROM ABC);

QUIT;

How do I create a SAS data set from the ##T1 temp data source?

I also want to be able to reference this temp table to create another temp table that uses above table in join

[Tom · Accepted solution]

Just select from it.

PROC SQL;
CONNECT TO ODBC AS A(DSN=SRC USER=ABC PW="***"  CONNECTION=GLOBAL);
EXECUTE (
  create table ##T1
  (COL1 VARCHAR(30)
  ,COL2 VARCHAR(30)
  )
) BY A;
EXECUTE (
  INSERT ##T1
  SELECT * FROM ABC
) BY A;
create table MYSASTABLE as 
select * from connection to A
(
  select * from ##T1
)
;
QUIT;

[SoloDolo]

Thank You, this worked.