BookmarkSubscribeRSS Feed
devnand
Obsidian | Level 7

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.

4 REPLIES 4
RichardinOz
Quartz | Level 8

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

devnand
Obsidian | Level 7

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?

Amir
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 2957 views
  • 0 likes
  • 3 in conversation