How to suppress error messages from SAS proc sql execute statement

1
votes

I use SAS proc sql execute statement to run sql and access tables on Oracle server. In order to let the script run automatically, I normally drop tables or indice before creating them. Sometimes however, when the drop sql code runs, the table or index doesn't exist. An error message will be logged in SAS about dropping something that doesn't exist. But since the SAS code will continue after the drops, this kind of error message doesn't matter at all. I want to suppress these error messages, stop SAS from logging them because I don't care about them. How can I do this in proc sql?

Thanks for any help.

1
sas
Are you using SQL passthrough or SAS libnames to access your tables? If you are using libnames can you use proc datasets to drop the index? – Robert Penridge
I am using SQL passthrough. Not sure how to drop an index from SAS PROC. To me, index is a pure database thing and SAS may not even know the existence of a database index. Correct me if I am wrong. – Steve
I found that if you use SQL passthrough to drop a table, all the indice belonging to that table will be dropped together. So there is no need to drop an index in my work. I can always drop tables. From this point, cmjohns' idea works for me. Thanks. – Steve

1 Answers

2
votes

I'm not aware of an option to supress this single type of error. There are a couple of options though.

The best bet is to clean up your code so you check to see if the data set exists before trying to act on it:

%if %sysfunc(exist(&name_of_data_set)) %then %do;

You could also redirect all logging during these steps where you get messages you don't want with proc printto, but you would potentially lose valuable information.