Handling errors in batch process.

Reply
Contributor
Posts: 39

Handling errors in batch process.

I am running a program in batch mode that creates a table based on query for DB2 and teradata. And after I created those tables there are few steps such as proc sort, taking a sample data with proc sql, and then proc compare and then deleting all those datasets.

The program runs fine for the Sql returning rows from db2 and teradata, but after it has encountered first zero rows the the other data steps does not perform.

I have a second macro program appended to it. The program does not execute. The program is to read the proc report and report the number of observation and the differing attributes in between database,

It is not working after it has encountered the error.

Is there a way to handle those errors and tell says to ignore those errors?

Is there a way if the sql was not returning any records, not to run a proc compare or proc sort, just move on to the next sql statement.??

%macro query_test;

%do i= 1 to &sqlobs;

proc sql noerrorstop exec;

connect to db2(%login);

connect to teradata(%login_td)

create table db2_1 as select * from connection to db2 (sql);

create table td_1 as select * from connection to teradata(sql);

proc sort table =db2_1; by_all_; run;

proc sort table =td_1; by_all_; run;

proc compare base=db2_1 compare=td_1;

run;

%mend;

%query_test

Then the other part of the macro program starts.

Is there a way to give this macro program a fresh start? Even when there are those proc report it is not reading it. I can run the same code afterwards(when the batch mode is over) and gives me the desired output.

Super Contributor
Posts: 644

Re: Handling errors in batch process.

There is an error in your macro - no matching %end

I would put the second part of the program into a macro that only ran if the query returned 1 or mor obs.

You might not need a macro for the initial query if the %do statement is redundant.

Richard

Contributor
Posts: 39

Re: Handling errors in batch process.

Posted in reply to RichardinOz

I just wanted to give the scenario of the program. There is no such syntax issue in the code. It needs a macro as it the sql is paramaterized and passed to the second part of the sql;

macro querytest;

proc sql;

select td_query,

          db2_query,

into :td1 -:td50,

     :db1-db50

from test;

quit;

%let nobs=&sqlobs;

%do i= 1 to &sqlobs;

proc sql noerrorstop exec;

connect to db2(%login);

connect to teradata(%login_td)

create table db2_1 as select * from connection to db2 (sql);

create table td_1 as select * from connection to teradata(sql);

proc sort table =db2_1; by_all_; run;

proc sort table =td_1; by_all_; run;

proc compare base=db2_1 compare=td_1;

run;

%end;

%mend;

%query_test

%macro dir_list;

some data and proc step;

%mend dir_list;

%dirlist;

this is how the program looks like.

When there is error in the first part, I mean consecutive errors, And moves to the second part of the macro, The macro does not iterate.

No output

Super Contributor
Posts: 644

Re: Handling errors in batch process.

Just checking:

I would have expected the following:

%let nobs=&sqlobs;

%do i= 1 to &nobs;

as &sqlobs would be reset during each of the following iterations of Proc SQL.

In fact, if the Proc SQL in the %do loop returned fewer rows than the current value of %i then the loop would terminate before the next call to Proc SQL.

Richard

Super User
Posts: 11,343

Re: Handling errors in batch process.

Posted in reply to RichardinOz

And if &sqlobs > 0.

You may be forcing a loop to start where there is no data with the i=1

Ask a Question
Discussion stats
  • 4 replies
  • 534 views
  • 0 likes
  • 3 in conversation