topic Why would SAS generate an error instead of a warning trying to drop a non existent Teradata table? in SAS Programming

Why would SAS generate an error instead of a warning trying to drop a non existent Teradata table?

Maninspecs — Wed, 27 Jul 2022 15:59:39 GMT

Hallo SAS Programmers, would anyone know why SAS would generate an error message instead of the usual warning message when attempting to drop a non existent Teradata table? The code is existing code where this situation would have come up before but would have generated a warning message. I've made the code and log generic but it follows the code I ran.

libname mylib teradata user=( <connection to Teradata>);

proc sql;

drop table mylib.mytable;

create table mylib.mytable (FASTLOAD=YES) as

select distinct *

from work.mydata;

quit;

Normally this would generate a warning message if the Teradata table does not exist but the following was generated in the program output. Any idea why it would be an ERROR and not just a WARNING that I would normally see when I attempt to delete a Teradata table that does not exist using the code format above. I wasn't able to reproduce this error. Successive attempts generated the warning message and no error message.

Thanks for any info.

ERROR: Teradata execute: Object 'tera_table.mytable' does not exist.
WARNING: Table mylib.mytable has not been dropped.

Re: Why would SAS generate an error instead of a warning trying to drop a non existent Teradata tabl

AMSAS — Wed, 27 Jul 2022 16:30:58 GMT

First question would be what changed/different from the runs that generate a WARNING

I suspect SAS is just passing back a message from Teradata, so maybe the Teradata version has changed and now passes back an error.

Do you have a DROP that produces the WARNING to compare against

Re: Why would SAS generate an error instead of a warning trying to drop a non existent Teradata tabl

Ksharp — Thu, 28 Jul 2022 12:46:00 GMT

Could you try Pass-through SQL ? Like
proc sql;
connect to teradata..........
execute(
drop table ........
)
by teradata ;
quit;

Re: Why would SAS generate an error instead of a warning trying to drop a non existent Teradata tabl

Maninspecs — Fri, 29 Jul 2022 12:49:45 GMT

Thanks everyone who responded. I wasn't able to reproduce the issue using the suggestions provided. My best guess is it was a one off resulting from weekend maintenance on SAS, Teradata or somewhere else. I've got into the habit of using the macro below when writing new code or where I can easily include it with code updates. I think I got it from a paper on lexjansen.com so thanks to the author. It first checks whether a table or file exists, and only drops it, if it does exist. This avoids generating unnecessary warning messages and presumably would have avoided the error message in my original post. You can use this as accept as solution. I don't seem to be able to do this for my own posts.

/* Drop table macro to avoid warning message if table does not exist. Warning message will cause autosys run to fail */
%macro checkDrop(tmpData);
%if %SYSFUNC(exist(&tmpData))
%then %do;
proc sql;
drop table &tmpData;
quit;
%end;
%mend checkDrop;

%checkDrop(mydb.mytable)