DATA Step, Macro, Functions and more

created volatile table, but can't still can't join to my query.

Reply
Contributor
Posts: 43

created volatile table, but can't still can't join to my query.

I'm using SAS EG and I have 3 steps in my project:

Step 1:  Query to generate data and insert into work.Table1.

Step 2:  Query to filter all distinct ID's from Table1 and put in a volatile table, TempLib.temp1.

step 3:  Query for Table2, but use TempLib.temp1 in a 'where' filter.

 

Step 1 works fine and I'm able to create my table.

Step 2 seems to work when I run it on it's own.

Step 3 breaks because it says it can't find the TempLib.temp1.

 

Why is this happening and how do I fix?

 

Here is roughly my code:

Step1

CONNECT TO TERADATA(MODE=TERADATA AUTHDOMAIN=xxx SERVER=xxx CONNECTION=GLOBAL);
CREATE TABLE work.table1 AS
SELECT * 
FROM CONNECTION TO TERADATA
(
SELECT * FROM Database
);
run;
quit;

 

Step 2:

proc sql;
CONNECT TO TERADATA(MODE=TERADATA AUTHDOMAIN=xxx SERVER=yyy CONNECTION=GLOBAL);
execute(create volatile table temp1
(var1 varchar(30))
on commit preserve rows) by teradata;

insert into templib.temp1
select distinct
t1.ID
from work.table1 t1;
quit;

proc sql;
CONNECT TO TERADATA(MODE=TERADATA AUTHDOMAIN=xxx SERVER=yyyI CONNECTION=GLOBAL);
select * from templib.temp1;



 Step 3 and this is where it breaks and it says my temp1 does not exist.

CONNECT TO TERADATA(MODE=TERADATA AUTHDOMAIN=xxx SERVER=yyyCONNECTION=GLOBAL);
CREATE TABLE work.table 2 AS
SELECT * FROM CONNECTION TO TERADATA
(
SELECT 
*
From database
where 
ID IN (select var1 from templib.temp1)
);
run;
quit;

 

 

PROC Star
Posts: 549

Re: created volatile table, but can't still can't join to my query.

[ Edited ]

this part does not look like right for sure. did u mean work.table2

 

 

CREATE TABLE work.table 2

 other issue is templib is libname is for using in SAS and in Teradata( that is when yiu use connect statement) you need not use any database name for volatile table in Teradata and I do not see the point why you are making a work table from teradata   and then inserting the data into volatile table, for me whole step 1 and 2 do not make any meaning.  below query will do all everything thing you are doing in  3 steps.  One more thing Volatile table are only for a session and they are not persistent from one Teradata connection to another 


Proc sql;
CONNECT TO TERADATA(MODE=TERADATA AUTHDOMAIN=xxx SERVER=yyy CONNECTION=GLOBAL); CREATE TABLE work.table2 AS SELECT * FROM CONNECTION TO TERADATA ( SELECT * From database.table where ID IN (select var1 from database.table) ); run; quit;


Contributor
Posts: 43

Re: created volatile table, but can't still can't join to my query.

thanks.

 

The reason I have 3 steps is because The query for step 1 and 3 were already created.  They are fairly large queries, and I really just needed step 2 to filter step 1 so I can join it with step 3. 

 

Would I just need to include step 2 part of step 3 in one connection?  I have a step 4 that also relies on the filter of step 1 after,  so do I just include step 2 as part of step 4?

 

I dont know how connections work, so I might need some assistance.

Super User
Super User
Posts: 8,289

Re: created volatile table, but can't still can't join to my query.

You forgot the first step. Create a LIBREF that can keep your connection active between PROC SQL steps.  Without that step SAS will close the session when the first PROC SQL step exits and your volatile table will be erases.

1) LIBNAME TDWORK TERADATA ..... connection=global ;

2) PROC SQL; CONNECT TO TERADATA (... connection=global) ; .... quit;

3) PROC SQL; CONNECT TO TERADATA (... connection=global); .... quit;

Contributor
Posts: 43

Re: created volatile table, but can't still can't join to my query.

I dont quite know how to use libref (my background is purely sql), so can you point me in the right direction as to the syntax?

Super User
Super User
Posts: 8,289

Re: created volatile table, but can't still can't join to my query.

If you are just using PROC SQL then why not just do all of the queries in one PROC SQL step?  Remove the extra QUIT, PROC SQL and CONNECT statements so that you are just using one PROC step.

 

The syntax for making a libref is very similar to the syntax for the CONNECT statement.  Mainly you just need to remove the parentheses that the CONNECT statement needs.

 

Ask a Question
Discussion stats
  • 5 replies
  • 295 views
  • 1 like
  • 3 in conversation