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.
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)
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
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.