- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ????
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?