BookmarkSubscribeRSS Feed
meckarthik
Quartz | Level 8

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 

 

3 REPLIES 3
ballardw
Super User

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
PROC Star

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
Super User

@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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 517 views
  • 0 likes
  • 3 in conversation