Error Handling

[meckarthik]

Hi All,

I'm working on a project that involves multiple SQL tables. We are running the program in scheduler using macros that run each table in the loop (i.e. 1 to n ). Sometimes, some of the SQL tables being drop by other user causing SAS to stop proceed further. Is there any way to bypass,  if the table does not exist then move on to pick the next one 

[ballardw]

From the documentation for the function Exists:

Example 1: Verifying the Existence of a Data Set

This example verifies the existence of a data set. If the data set does not exist, then the example displays a message in the log:

%let dsname=sasuser.houses;

%if %sysfunc(exist(&name)) %then %do;
   <your remaining macro code goes here>
%end;
%else %put Data set &name does not exist.;

The bit you want is the highlighted in magenta above. The line tests the existence of the data set. What would follow the %then is your current macro generated code. A simple %else; does nothing if the data set isn't present though a note similar to the above might be helpful in the Log for job.

[SASKiwi]

If you are running a Production SAS process from an SQL Server database where tables are being dropped, then I suggest you have a data management problem rather than a coding problem. Production database tables should be truncated, that is all rows removed, not dropped entirely - that is simply poor practice.

I don't think the EXIST function as suggested by @ballardw will work for external databases like SQL Server. You will probably have to query the database's dictionary tables to check for the existence of a required table. 

[ballardw]

---

@SASKiwi wrote:

If you are running a Production SAS process from an SQL Server database where tables are being dropped, then I suggest you have a data management problem rather than a coding problem. Production database tables should be truncated, that is all rows removed, not dropped entirely - that is simply poor practice.

I don't think the EXIST function as suggested by @ballardw will work for external databases like SQL Server. You will probably have to query the database's dictionary tables to check for the existence of a required table. 

---

Good point. I didn't catch that the SQL might be in an external system as I do not read "SQL table" to be specific SQL Server or other data base, just lazy reference to a SAS data set referenced with Proc SQL when an external source is not explicitly stated.