03-15-2018 12:00 AM
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:
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;
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;
03-15-2018 12:24 AM - edited 03-15-2018 12:41 AM
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
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;
03-15-2018 11:13 PM
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.
03-15-2018 12:41 AM
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;
03-15-2018 11:15 PM
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?
03-16-2018 12:04 AM
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.