BookmarkSubscribeRSS Feed
mrdlau
Obsidian | Level 7

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;

 

 

5 REPLIES 5
kiranv_
Rhodochrosite | Level 12

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;


mrdlau
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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;

mrdlau
Obsidian | Level 7

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?

Tom
Super User Tom
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3001 views
  • 1 like
  • 3 in conversation