topic How to create volatile tables from SAS in SAS Programming

How to create volatile tables from SAS

Ronein — Wed, 03 Jan 2024 13:11:59 GMT

Hello

I am running query code in SAS that work on Tera tables.

The query has 2 parts:

1-In part 1 I create table called t_dates and this table is a sas data set.

proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table t_dates  as 
select * from connection to teradata
(
SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
FROM   V797_PROCESS_DATE
);
disconnect from teradata;
quit ;

2-In part 2 the query merge SAS data set with Tera table and then I get an error because Tera doesnt know the SAS data set that was created (t_dates)

proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table want  as 
select * from connection to teradata
(
SELECT  a.*
FROM VBM5732_AGR_DELAYED_PAYMENTS  AS a 
Where AS52_TAR_DHIA BETWEEN(SEL START_DATE FROM t_dates)  AND (SEL END_DATE FROM t_dates)
);
disconnect from teradata;
quit ;

I got an error because Tera doesnt know the sas data set that was created,

Then I have tried to create Tera volatile table but this method is not working

proc sql ;
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
execute (
create volatile table t_dates as (

SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
FROM   V797_PROCESS_DATE

    ) with data on commit preserve rows 
  ) by teradata;
quit;

My Question- How to create volatile tables from SAS ????

Re: How to create volatile tables from SAS

Tom — Wed, 03 Jan 2024 13:57:09 GMT

Don't make TWO different connections to the database, that will lose your volatile tables from the first table when the first connection ends.

Make a LIBREF and leave it open for the whole process. You can use the dbmstemp=yes to allow you to access volatile tables. (You will need the check what schema to use. I seem to remember it uses your Teradata userid as the schema name for the volatile tables.)

libref td teradata .... dmstemp=yes ;

Then you can use that in PROC SQL if you want. So if you do something like this to make volatile table xxx

proc sql;
connect using td ;
execute by td 
(create volatile table xxx ..... )
;
quit;

Then you can later reference from SAS using TD.XXX as the dataset name.

Or you could use PROC APPEND to copy a dataset into TD.

proc append base=td.class data=sashelp.class;
run;

And then reference it from pass through code running in Teradata.

proc sql;
connect using td;
create table results as select * from connection to td
(select * from schema.table where name in 
 (select name from xxx)
);
quit;

Re: How to create volatile tables from SAS

Patrick — Thu, 04 Jan 2024 04:13:04 GMT

On top of what @Tom wrote make sure that the connection is global.

CONNECTION= LIBNAME Statement Option

I also suggest you read the following two docu entries.

Temporary Table Support for Teradata

Temporary Table Support for SAS/ACCESS

Re: How to create volatile tables from SAS

Ronein — Thu, 04 Jan 2024 06:36:15 GMT

Thanks,

Bases on my example can you show the all code?

/*****Creating SAS DATA SET FROM Tera table**********/
/*****Creating SAS DATA SET FROM Tera table**********/
/*****Creating SAS DATA SET FROM Tera table**********/
proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table t_dates_Sas_Data_SET  as 
select * from connection to teradata
(
SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
FROM   V797_PROCESS_DATE
);
disconnect from teradata;
quit ;


/*****Creating Teradata Volatile Table from FROM Tera table**********/
/*****Creating Teradata Volatile Table from FROM Tera table**********/
/*****Creating Teradata Volatile Table from FROM Tera table**********/
proc sql;
 connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth connection=global);
 execute(
 create volatile table t_dates_Tera_volatile as (
SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
FROM   V797_PROCESS_DATE
 )
 with data primary index (START_DATE)
 on commit preserve rows
 ) by teradata;
quit;

As I see I didn't succeed creating Tera Volatile table because I run this code and got error

 
proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table Check_Tera_volatile  as 
select * from connection to teradata
(*
from t_dates_Tera_volatile
);
disconnect from teradata;

quit ;

Re: How to create volatile tables from SAS

Ronein — Thu, 04 Jan 2024 06:55:43 GMT

TeraData libname is already defined .

I have checked and When I run this code then it iw working 100%

proc sql outobs=5;
create table Check_Libname_TeraData as
select *
from teradata.V797_PROCESS_DATE
;
quit;

However, this code to create Tera Violate table is not working


proc sql;
connect using teradata ;
execute by teradata 
( create volatile table t_dates_Tera_volatile as 
(SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
FROM   V797_PROCESS_DATE
) 
)
;
quit;

Re: How to create volatile tables from SAS

SASKiwi — Thu, 04 Jan 2024 09:40:34 GMT

If your program isn't working then you should always check your SAS log for errors, warnings and notes. Please post your complete SAS log, including your LIBNAME statement - does it include the DBMSTEMP = YES option as @Tom explained?

Re: How to create volatile tables from SAS

Patrick — Fri, 05 Jan 2024 01:02:10 GMT

@Ronein Looks like you haven't read the docu under the links I've shared.

Please also always share the SAS log if there are errors.

Isn't it obvious to you why below code can't work. What does the SAS log tell you?