How to place a error check in the proc SQL so that it does not run any further process below?

Reply
Contributor
Posts: 39

How to place a error check in the proc SQL so that it does not run any further process below?

I do not want to run any process further if the return code is greater than zero?

%let nobs=&sqlobs;

%do i=1 %to &nobs ;

proc sql NOERRORSTOP EXEC;

connect to teradata as td (%login_td);

      create table dev.td_&&ID&I as

     select * from connection to td

     (&&td&i)

   ;

quit;

%if &sqlxrc gt 0 %then %do;

%goto return;

%end;

proc sql;

  connect to db2 as db  (%login) ;

create table dev.db2_&&ID&I as

    select * from connection to db

    (&&db&i)

   ;

  quit;

If the return code is greater than zero, I dont want the program to run the Db2 sql, but return to next teradata sql; else go to the db2 query and execute. I need to have a chekflow in this program

Please help.

Super Contributor
Posts: 644

Re: How to place a error check in the proc SQL so that it does not run any further process below?

I assume this code is in some larger marco otherwise the %do statements will not execute.

This may not be a complete answer but the first thing to do is to move the %end after the next SQL, and include a %return: label just before it.

%let nobs=&sqlobs;

%do i=1 %to &nobs ;

proc sql NOERRORSTOP EXEC;

...

%if &sqlxrc gt 0 %then %goto return;

...

create table dev.db2_&&ID&I as

    select * from connection to db

    (&&db&i)

   ;

  quit;

%return:

%end ;

I am not sure even this will work, it depends on &sqlxrc being updated during macro execution.

Richard

Contributor
Posts: 39

Re: How to place a error check in the proc SQL so that it does not run any further process below?

The macro test contains a %GOTO statement with an invalid statement label name.  The

       macro will not be compiled.

Branching with the %GOTO statement has two restrictions. First, the label

that is the target of the %GOTO statement must exist in the current macro; you cannot

branch to a label in another macro with a %GOTO statement. Second, a %GOTO

statement cannot cause execution to branch to a point inside an iterative %DO, %DO

%UNTIL, or %DO %WHILE loop that is not currently executing.

Is this what is causing the error?

Super Contributor
Posts: 282

Re: How to place a error check in the proc SQL so that it does not run any further process below?

Hi,

As has been mentioned, if you want to use certain macro statements then they have to be within a macro function.

In the code you have shown there is only 1 %end, but there are two instances of %do.

In the suggested *untested* code below I have assumed you want the the 2nd proc sql to also be in the %do i=1 %to &nobs loop.

Furthermore, instead of using a %goto statement with a label, I have changed the %if test so that the 2nd proc sql only runs if &sqlxrc eq 0, you can check if it is what you need.

%let nobs=&sqlobs;

/* define a macro to run the SQL steps */

%macro sqlmacro;

  %do i=1 %to &nobs ;


    proc sql NOERRORSTOP EXEC;

      connect to teradata as td (%login_td);

      create table dev.td_&&ID&I as

      select * from connection to td

      (&&td&i)

      ;

    quit;

    %if &sqlxrc eq 0 %then %do;

      proc sql;

        connect to db2 as db  (%login) ;

        create table dev.db2_&&ID&I as

        select * from connection to db

        (&&db&i)

        ;

      quit;

    %end;


  %end; /* %do i=1 %to &nobs*/

%mend sqlmacro;

/* execute the macro */

%sqlmacro;

If this is not what you want then please show the log with the error message you are getting.

Regards,

Amir.

Contributor
Posts: 39

Re: How to place a error check in the proc SQL so that it does not run any further process below?

Thanks a much

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