CODEWITHSUNDEEP
sasteradatasas-macro
JoeX
JoeX

Reputation: 37

SAS Enterprise Guide Drop Volatile Tables If Any Exist

I'm attempting to create a SAS macro that can drop any/all volatile tables in a current Teradata session.

Currently the only way to have it run successfully is to create a dummy volatile table at the beginning of the macro that allows the creation of a table containing all table names found using the HELP VOLATILE TABLE option in Teradata.

The current issue I'm running into is if I remove the dummy table creation (XXXX) and I don't have any volatile tables in my session then HELP VOLATILE TABLE doesn't return anything and leads to the inability to create a table.

Warning/Errors when no volatile tables are present.

WARNING: The variable 'Table Dictionary Name'n in the DROP, KEEP, or RENAME list has never been referenced.

ERROR: PROC SQL requires any created table to have at least 1 column.

Is there a way around having the need to create a dummy table (like shown below)?


%macro dropvts(DropALL=N,USE_COMMIT_WORK=N)/parmbuff;
    %put syspbuff contains: &syspbuff;

    RESET NOPRINT;

    EXECUTE(
        CREATE VOLATILE TABLE XXXX, LOG (
        XXXX CHAR(4)
        )
        PRIMARY INDEX ( XXXX )
        ON COMMIT PRESERVE ROWS) BY TERADATA;
    %IF %UPCASE(&USE_COMMIT_WORK)=Y %THEN %DO;
    EXECUTE(COMMIT WORK) BY TERADATA;
    %END;
    
    CREATE TABLE VTs (RENAME=('Table Dictionary Name'n=TableName) KEEP='Table Dictionary Name'n)  AS  
        SELECT * FROM CONNECTION TO TERADATA (HELP VOLATILE TABLE);    /*** CHG PROJ ID 409 PLF ***/

    %IF %UPCASE(&DropALL)=N %THEN %DO;
        %local parm;
        %do parm=1 %to %sysfunc(countw(%sysfunc(compress(&syspbuff))));

            %let exists=0;
            SELECT 1 INTO :exists FROM VTs
            WHERE UPCASE(TableName)="%UPCASE(%SCAN(&syspbuff,&parm))";

            %IF &exists=1 %THEN %DO;
                EXECUTE(DROP TABLE %SCAN(%sysfunc(compress(&syspbuff)),&parm)) BY TERADATA;
                %IF %UPCASE(&USE_COMMIT_WORK)=Y %THEN %DO;
                EXECUTE(COMMIT WORK) BY TERADATA;
                %END;
                %PUT VT %SCAN(&syspbuff,&parm) has been dropped;
            %END;
            %ELSE %DO;
            %PUT VT %SCAN(&syspbuff,&parm) does not exist;
            %END;   
        %end;

        CREATE TABLE VTs (RENAME=('Table Dictionary Name'n=TableName) KEEP='Table Dictionary Name'n)  AS  
              SELECT * FROM CONNECTION TO TERADATA (HELP VOLATILE TABLE);   

        EXECUTE(DROP TABLE XXXX) BY TERADATA;
        %IF %UPCASE(&USE_COMMIT_WORK)=Y %THEN %DO;
        EXECUTE(COMMIT WORK) BY TERADATA;
        %END;
    %END;
    %ELSE %DO;
        SELECT TableName 
            into :voltable1-:voltable999
        FROM VTs;

        %local table;
        %do table=1 %to &sqlobs;
                EXECUTE(DROP TABLE &&voltable&table) BY TERADATA;
                %IF %UPCASE(&USE_COMMIT_WORK)=Y %THEN %DO;
                EXECUTE(COMMIT WORK) BY TERADATA;
                %END;
        %end;
    %END;
    RESET PRINT; 
%mend;

Any help is greatly appreciated.

Upvotes: 0

Views: 163

Answers (1)

Tom
Tom

Reputation: 51581

What I did in this macro %TDEXIST() (which tests if a table, view or volatile table with a specific name exists and what type it is) is run a separate query first to check if there are any volatile tables or not.

So if you have a connection to TERADATA already existing and you want to test if a table with the name in the macro variable TABLE exists you could use this code.

*----------------------------------------------------------------------------;
* Check for any Volatile tables ;
*----------------------------------------------------------------------------;
%let exists=0;
select '1' into :exists from connection to TERADATA (help volatile table) ;

%if (&exists) %then %do;
*----------------------------------------------------------------------------;
* Check if this Volatile table exists ;
*----------------------------------------------------------------------------;
    select 'VOLATILE TABLE' into :result
      from connection to TERADATA (help volatile table)
%*----------------------------------------------------------------------------
Set VARNAME based on VALIDVARNAME setting.
-----------------------------------------------------------------------------;
  %if %sysfunc(getoption(validvarname))=ANY %then
      where upcase('table name'n) = "&table"
  ;%else
      where upcase(table_name) = "&table"
  ;
    ;
  %let exists=&sqlobs;
%end;

Upvotes: 2

Related Questions