Macro question &SQLOBS not resolved

[ja_lew]

guys/gals- why is it when i put my rsubmit INSIDE my macro code my SQLOBS isnt resolved, but when i run the macro INSIDE a rsubmit it works as intended?

I'm fairly new to macros so I'm at a loss.

WARNING: Apparent symbolic reference SQLOBS not resolved.

/*this doesn't work*/

options mprint mlogic symbolgen;

%macro tbl_drop;

rsubmit;

proc sql noprint;

connect to oracle (user="&produser." pass="&prodpw." path="&dbpath" buffsize=200 preserve);

select table_name into :m1 - :m99999

from connection to oracle

(select table_name

          from all_tables

           where owner = %str(%')%upcase(&produser.)%str(%')

           and table_name like %nrstr(%'%%)%upcase(&CMPGN.)%nrstr(%%%')

           and table_name != %str(%')%upcase(&CMPGN._SAS)%str(%')

  );

%let lmt = &sqlobs;

%do i = 1 %to &lmt;

           proc sql noprint;

          connect to oracle (user="&produser." pass="&prodpw." path="&dbpath" buffsize=200 preserve);

             execute ( drop table &&m&i) by oracle;

%end;

quit;

endrsubmit;

%mend tbl_drop;

%tbl_drop;

/*this works*/

rsubmit;

options symbolgen mlogic mprint;

%macro tbl_drop;

proc sql noprint;

connect to oracle (user="&produser." pass="&prodpw." path="&dbpath" buffsize=200 preserve);

select table_name into :m1 - :m99999

from connection to oracle

(select table_name

          from all_tables

           where owner = %str(%')%upcase(&produser.)%str(%')

           and table_name like %nrstr(%'%%)%upcase(&CMPGN.)%nrstr(%%%')

           and table_name != %str(%')%upcase(&CMPGN._SAS)%str(%')

  );

%let lmt = &sqlobs;

%do i = 1 %to &lmt;

          proc sql noprint;

          connect to oracle (user="&produser." pass="&prodpw." path="&dbpath" buffsize=200 preserve);

          execute ( drop table &&m&i) by oracle;

%end;

quit;

%mend tbl_drop;

%tbl_drop;

endrsubmit;

[art297]

I think you will find the following note useful in understanding the timing issues:

http://support.sas.com/techsup/technote/ts697.pdf

[Tom]

Pain in the neck isn't it?  SAS is compiling and executing that %LET statement on the local machine and then sending the generated code to the remote.

Try wrapping the %LET inside of an %NRSTR() function.

%nrstr(%let lmt = &sqlobs) ;

Not sure if will help though as the %DO will not be able to run in open code in the remote submit block any way.

It is as if you submitted this code:

rsubmit ;

  %do i=1 %to 10; ... %end;

endrsubmit;

You will get an error about trying to execute a %DO loop in open code.

[FriedEgg]

I believe the issue is the that the SAS is running the %let in the active session rather that the remote session.  Wrapping the rsubmit in a macro seems to cause issues with macro statements.  If you want to run the first way you will need to avoid macro statements inside of the remote session:

%macro tblname;

signon task2 sascmd="/usr/local/SAS/SASFoundation/9.2/sas -spool -mprint -mlogic -symbolgen -details -msglevel=I" wait=no;

rsubmit task2;

proc sql noprint;

connect to oracle(user=foo pass=bar path=oradb);

select table_name into :m1-m99999 from connection to oracle

( select table_name from all_tables where table_name like 'FRIEDEGG_%' );

disconnect from oracle;

quit;

libname ort oracle user=foo pass=bar path=oradb;

data _null_;

do i=1 to symget('sqlobs');

   call execute("proc sql; drop table " || symget('m'||i) || "; quit;");

end;

run;

endrsubmit;

signoff task2;

%mend;

%tblname

EDIT: Thank you KSharp for pointing out that I had left off the noprint option from my first proc sql.  I had taken it off to debug something when I was testing and forgot to add it back.

[Ksharp]

You should not use noprint option in SQL.

If you want not to print output then use create table ...........

[Tom]

Which queries are you referring to?  The first of the two in the response from FriedEgg requires the NOPRINT option to prevent SAS from writing results to the output destinations in addition to storing the values into the macro variables.  In the second query that is used to drop tables it does not matter whether the noprint option is used as no output will be generated by a drop table statement.

[art297]

Like Tom I, too, have to ask why you would suggest NOT using noprint?  Often, the longest part of any large job is the IO time required to write a table.  If one doesn't need a table to begin with, what benefit would be achieved by writing one?

[Ksharp]

Dear Art and Tom.

Just Ignore me.

I misunderstood.

Ksharp

[ja_lew]

thanks for all the suggestions!  after quite a bit of debugging my final version works.

%macro tbl_clean;

%syslput produser=&produser;

%syslput prodpw=&prodpw;

%syslput cmpgn=&cmpgn;

%syslput dbpath=&dbpath;

%put ;

%put "SAS LOG TABLE CLEANUP FOR &CMPGN." ;

%put ;

rsubmit;

%macro tbl_drop;

proc sql noprint;

connect to oracle (user="&produser." pass="&prodpw." path="&dbpath.");

select table_name into :m1 - :m99999

          from connection to oracle

          (          select table_name

                              from all_tables

                              where owner = %upcase(%str(%')&produser.%str(%'))

                              and table_name like %upcase(%nrstr(%'%%)&CMPGN.%nrstr(%%%'))

                              and table_name != %upcase(%str(%')&CMPGN._SAS%str(%'))

          );

quit;

%nrstr( %let lmt = &sqlobs;)

%macro drop_em;

proc sql noprint;

connect to oracle (user="&produser." pass="&prodpw." path="&dbpath.");

%do i = 1 %to &lmt.;

                      execute ( drop table &&m&i) by oracle;

          %put ;

          %put "SAS LOG TABLE &&m&i DROPPED" ;

          %put ;

%end;

disconnect from oracle;

quit;

%mend drop_em;

%drop_em;

quit;

%mend;

%tbl_drop;

endrsubmit;

%mend;

%tbl_clean;