topic Re: Create multiple -volatile tanles in tera and then sas data set in SAS Programming

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

Ronein — Thu, 06 Feb 2025 05:13:40 GMT

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;

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

LinusH — Thu, 06 Feb 2025 07:55:30 GMT

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.

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

Ronein — Thu, 06 Feb 2025 14:49:53 GMT

Can you please show code ?

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

Tom — Thu, 06 Feb 2025 15:20:40 GMT

@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.

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

Second make the volatile tables.

/**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.

data want;
  set tdwork.v3;
run;

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

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