[SOLVED] DROP TABLE IF EXISTS equivalent for TERADATA from SAS

DROP TABLE IF EXISTS equivalent for TERADATA from SAS

I'm trying to avoid the error:

ERROR: Teradata execute: Object 'MY_TABLE' does not exist.

When executing TERADATA SQL from SAS

This is the original SAS code I'm using:

proc sql;
    connect to TERADATA (user='my_user' password=XXXXXXXXXX MODE=TERADATA TDPID='bdpr');
    execute(database MY_DB) by TERADATA;
    execute(Drop table MY_TABLE;) by TERADATA;
    disconnect from TERADATA;
quit;

According to the documentation the .SET ERRORLEVEL 3807 SEVERITY 0 should fix my problem.

I tried inserting the following before my DROP TABLE statement:

execute(.SET ERRORLEVEL 3807 SEVERITY 0) by TERADATA;

execute(ECHO '.SET ERRORLEVEL 3807 SEVERITY 0') by TERADATA;

I tried combining both:

execute(ECHO '.SET ERRORLEVEL 3807 SEVERITY 0'; Drop table MY_TABLE;) TERADATA;

With either a syntax error for the calls without ECHO or no effect on the error when trying the ECHO variants.

The problem is that the .SET ERRORLEVEL is not a SQL statement but a BTEQ command. According to the docs it should be possible to execute BTEQ commands from standard TERADATA SQL should be possible using the ECHO construct. But from SAS this doesn't seem to be working.

I only need a solution to avoid the SAS error, both SAS side solutions as well as TERADATA solutions are ok for me.

Solution

Why not ask Teradata if the table exists and then have SAS conditionally run the drop?

%let tablekind=NONE;
select obj into :tablekind trimmed from connection to teradata
  (select case when (tablekind in ('T','O')) then 'TABLE'
          else 'VIEW' end as obj
    from dbc.tablesv
    where databasename = 'MY_DB' and tablename= 'MY_TABLE'
      and tablekind in ('V','T','O')
  )
;
%if &tablekind ne NONE %then %do;
  execute(drop &tablekind. MY_DB.MY_TABLE;) by teradata;
%end;