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

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

devnand — Fri, 10 May 2013 21:16:53 GMT

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.

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

RichardinOz — Sat, 11 May 2013 01:17:48 GMT

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

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

devnand — Sat, 11 May 2013 20:28:37 GMT

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?

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

Amir — Mon, 13 May 2013 12:38:44 GMT

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.

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

devnand — Tue, 04 Jun 2013 22:03:22 GMT

Thanks a much