Create multiple -volatile tanles in tera and then sas data set

[Ronein]

Hello

I am trying to create multiple volatile tables in tera -volatile1,volatile1,volatile3

And then I want to create a sas data set from volatile3.

STEP1 (Create  volatile tables in tera -volatile1,volatile1,volatile3 is running successfully with no error)

STEP2 (Create sas date set from tera volatile3 ) is not working 

ERROR: File TDWORK.v3.DATA does not exist.

Please note that in step1 I didnt run  disconnect from teradata statement

/**STEP1**/
proc sql; 
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth connection=global); 
/* Create volatile table1 */
execute( 
create volatile table v1 as
(
SELECT TOP 100000
            Branch_Cust_Nbr AS customer_ID,
            Branch_Cust_IP,
            first_Branch_Cust_IP AS numerator,
            Team_IP
        FROM VBM374_USED_BRANCH_CUSTOMER
)  
with data primary index (Branch_Cust_IP) 
on commit preserve rows  ) by teradata; 
/* Create volatile table2 */
execute( 
create volatile table v2 as
(
SELECT TOP 100000
            Team_IP,
            Branch_Nbr
        FROM VBM376_INTERNAL_ORGANIZATION
)  
with data primary index (TEAM_IP) 
on commit preserve rows  ) by teradata; 
 /* Create volatile table3 by merging table1 and table2 */
  execute (
CREATE VOLATILE TABLE v3 AS
(SELECT
            a.customer_ID,
            a.Branch_Cust_IP,
            a.numerator,
            b.Team_IP,
            b.Branch_Nbr
        FROM v1 as a
        LEFT JOIN v2 as b
        ON a.Team_IP =b.Team_IP)

with data primary index (Branch_Cust_IP) 
on commit preserve rows  ) by teradata;
/*    disconnect from teradata;*/
quit;

/**STEP2**/
libname tdwork teradata 
AUTHDOMAIN=TeraDataAuth 
mode=teradata  
server=dwprod
schema=DWP_vall
connection=global 
dbmstemp=yes;

proc sql;
create table want as
select *
from tdwork.v3
;
quit;

[LinusH]

The connection is dropped at the end of the PROC SQL step.
Try to issue the LIBNAME statement before the PROC SQL's.
Another work-around is to extract the data to SAS in the same/last PROC SQL.

[Ronein]

Can you please show code ?

[Tom]

---

@Ronein wrote:
Can you please show code ?

---

You have all the code you need, just change the order.

First make the libref (make sure it is pointing to the right place to find your volatile tables). The teradata system I used 10-15 years ago that meant setting the SCHEMA= option to my Teradata username.

Spoiler

libname tdwork teradata 
AUTHDOMAIN=TeraDataAuth 
mode=teradata  
server=dwprod
schema=DWP_vall
connection=global 
dbmstemp=yes
;

Second make the volatile tables.

Spoiler

/**STEP1**/
proc sql; 
connect using tdwork as teradata ;
/* Create volatile table1 */
execute by teradata( 
create volatile table v1 as
(
SELECT TOP 100000
            Branch_Cust_Nbr AS customer_ID,
            Branch_Cust_IP,
            first_Branch_Cust_IP AS numerator,
            Team_IP
        FROM VBM374_USED_BRANCH_CUSTOMER
)  
with data primary index (Branch_Cust_IP) 
on commit preserve rows  ); 
/* Create volatile table2 */
execute by teradata ( 
create volatile table v2 as
(
SELECT TOP 100000
            Team_IP,
            Branch_Nbr
        FROM VBM376_INTERNAL_ORGANIZATION
)  
with data primary index (TEAM_IP) 
on commit preserve rows  ); 
 /* Create volatile table3 by merging table1 and table2 */
execute  by teradata (
CREATE VOLATILE TABLE v3 AS
(SELECT
            a.customer_ID,
            a.Branch_Cust_IP,
            a.numerator,
            b.Team_IP,
            b.Branch_Nbr
        FROM v1 as a
        LEFT JOIN v2 as b
        ON a.Team_IP =b.Team_IP)

with data primary index (Branch_Cust_IP) 
on commit preserve rows  ) ;
quit;

Then copy the dataset.  You could use a simple DATA step.

Spoiler

data want;
  set tdwork.v3;
run;

Or you could use an SQL query (if you like having to type too much code).

Spoiler

proc sql;
create table want as
select *
from tdwork.v3
;
quit;