Create SAS data set from Tera VOLATILE table

[Ronein]

Hello

Let's say that I want to upload volatile Tera table into sas data set.

What is the way to do it?

Please note that the libname to tera is 

libname tr  teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL dbmstemp=yes ;

/************Run in SAS-Create a single Violate table in Tera by using a single execute block************/
/************Run in SAS-Create a single Violate table in Tera by using a single execute block************/
/************Run in SAS-Create a single Violate table in Tera by using a single execute block************/
proc sql;
connect using TeraData;  
execute(

      CREATE VOLATILE TABLE  VT5727_DATES_M  AS (
      SELECT Max(PROCESS_DATE )   AS END_DATE  
      ,(CAST('01/10/2023' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE 
      ,END_DATE/ 100 + 190000 AS MONTH_REPORTED        
      FROM   V797_PROCESS_DATE  
      )WITH DATA ON COMMIT PRESERVE ROWS


    ) by teradata;
  ;
quit;

/*I have tried with this code but it is not working*/
Data want;
set tr.VT5727_DATES_M;
Run;
/*25         GOPTIONS ACCESSIBLE;*/
/*26         Data VT5727_DATES_M;*/
/*           ___________________*/
/*           180*/
/**/
/*ERROR 180-322: Statement is not valid or it is used out of proper order.*/
/**/
/*27         set tr.VT5727_DATES_M;*/
/*           _____________________*/
/*           180*/
/**/
/*ERROR 180-322: Statement is not valid or it is used out of proper order.*/

[AhmedAl_Attar]

Shouldn't you use
connect using tr;

if your libname reference is TR, then you should use TR for your connection

[AhmedAl_Attar]

Check this paper https://www.lexjansen.com/pharmasug-cn/2017/AD/PharmaSUG-China-2017-AD07.pdf for examples

[LinusH]

As said in another thread, I'm don't think the volatile table is available outside the session created by the CONNECT statement.

And that session ends with either DISCONNECT FROM, or PROC SQL QUIT statment.

[Patrick]

Don't use (and you don't need) libname option dbmstemp=yes because you're creating the volatile table directly with Teradata code in a execute block.

Try if below code works.

libname tr teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL dbmstemp=yes ;
proc sql;
connect using tr;  
execute(

      CREATE VOLATILE TABLE  VT5727_DATES_M  AS (
      SELECT Max(PROCESS_DATE )   AS END_DATE  
      ,(CAST('01/10/2023' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE 
      ,END_DATE/ 100 + 190000 AS MONTH_REPORTED        
      FROM   V797_PROCESS_DATE  
      )WITH DATA ON COMMIT PRESERVE ROWS


    ) by tr;
  ;
quit;

/*I have tried with this code but it is not working*/
Data want;
  set tr.VT5727_DATES_M;
Run;

Run above test in a fresh new SAS session. If this doesn't work then try:

libname tr teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL dbmstemp=yes ;
proc sql;
  connect using tr;  
  execute(

        CREATE VOLATILE TABLE  VT5727_DATES_M  AS (
        SELECT Max(PROCESS_DATE )   AS END_DATE  
        ,(CAST('01/10/2023' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE 
        ,END_DATE/ 100 + 190000 AS MONTH_REPORTED        
        FROM   V797_PROCESS_DATE  
        )WITH DATA ON COMMIT PRESERVE ROWS


      ) by tr;
    ;

  create table want as
  select *
  from connection to tr 
  (
    select * from VT5727_DATES_M
  );

quit;

I can't test this code so if there is a syntax error then please spend first a little bit of time trying to fix it before you just come back and state it's throwing an error.

Also try to "cleanly" share code you've run and related log. What you've shared in your question doesn't "match" and is likely caused by some "leftover" from an earlier run in the same session. 

When getting an error ALWAYS re-run using a fresh SAS session to ensure it's not some "leftover".

And last but not least:

Look into Example: Create and Join a Permanent Table and a Temporary Table