Statement is not valid or it is used out of proper order in macro

[David_Billa]

When I execute the following code, it's works for first iteration and it's throwing an error from second iteration. I believe the issue is in the index portion (proc contents and data _null_). It's not working after first iteration. May be code placement is not right. Any help?

%macro simple_loop;
proc sql noprint;
  
    connect using &db_lib as database;

    %do type_id=1 %to %sysfunc(countw(&type_list,,s));
/*some macro code*/
 execute(
        create or replace tbl &schema_temp..&prompt_tbl_name as
          select distinct &current_distinct_var_list
          from &schema_temp..&source_tbl
        ;
      ) by database;
      
      
      create tbl &prompt_tbl_name as
        select * from connection to database (
          select *
          from &schema_temp..&prompt_tbl_name;
        )
      ;
proc contents data=&prompt_tbl_name noprint
out=index_list(keep=libname memname name);
run;

filename code temp;
data _null_;
set index_list;
by libname memname ;
file code;
if first.libname then put 'proc datasets nolist lib=' libname ';' ;
if first.memname then put 'modify ' memname ';' / '  create index ' @;
put name @;
if last.memname then put ';' / 'run;' ;
if last.libname then put 'quit;' ;
run;  
%include code / source2;     
%end;    
quit;

%mend simple_loop;
%simple_loop;

[yabwon]

Before running the code add:

options mprint symbolgen;

to it and share log results. 

Bart

[David_Billa]

@yabwon Log is shown below. It's big file hence I shared only the portion of error log. Here the catch is index is created for all the iterations and from second iteration I could see the error before the index steps. However if I remove the index part from my code, it is executing without any issues for all the iteration.

SYMBOLGEN:  Macro variable CLASS_VAR_ID resolves to 2
MLOGIC(SIMPLE_LOOP):  %IF condition (&class_var_ID eq 1) is FALSE
MLOGIC(SIMPLE_LOOP):  %LET (variable name is PROMPT_TBL_NAME)
SYMBOLGEN:  Macro variable PROMPT_TBL_NAME resolves to dmm_tts_pt_div
SYMBOLGEN:  Macro variable CURRENT_CLASS_VAR resolves to upn
MLOGIC(SIMPLE_LOOP):  %DO loop index variable CLASS_VAR_ID is now 3; loop will not iterate again.
MLOGIC(SIMPLE_LOOP):  %PUT NOTE: &=prompt_TBL_name
SYMBOLGEN:  Macro variable PROMPT_TBL_NAME resolves to dmm_ttt_pt_div_upn
NOTE: PROMPT_TBL_NAME=dmm_ttt_pt_div_upn
NOTE: Line generated by the invoked macro "SIMPLE_LOOP".
235                  execute(         create or replace TBL &schema_temp..&prompt_TBL_name as           select distinct
                     -------
                     180
235  ! &current_distinct_var_list           from &schema_temp..&source_TBL         ;       ) by database;
SYMBOLGEN:  Macro variable SCHEMA_TEMP resolves to PMQ_TEMP_TBLS
SYMBOLGEN:  Macro variable PROMPT_TBL_NAME resolves to dmm_tts_pt_div_upn
SYMBOLGEN:  Macro variable CURRENT_DISTINCT_VAR_LIST resolves to bss_division,upn
SYMBOLGEN:  Macro variable SCHEMA_TEMP resolves to PMQ_TEMP_TBLS
SYMBOLGEN:  Macro variable SOURCE_TBL resolves to dmm_transfer_prompts
MPRINT(SIMPLE_LOOP):   execute( create or replace TBL PMQ_TEMP_TBLS.dmm_tts_pt_div_upn as select distinct bss_division,upn 
from PMQ_TEMP_TBLS.dmm_transfer_prompts ;
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the invoked macro "SIMPLE_LOOP".
235                  execute(         create or replace TBL &schema_temp..&prompt_TBL_name as           select distinct
235  ! &current_distinct_var_list           from &schema_temp..&source_TBL         ;       ) by database;
                                                                                             -
                                                                                             180
ERROR 180-322: Statement is not valid or it is used out of proper order.
MPRINT(SIMPLE_LOOP):   ) by database;

[yabwon]

There is this very old saying which states: >>Usually a code executed inside the Proc SQL works better if before it you write "Proc SQL;"...<< 

Look at your code, where the %do-loop is located and what it contains, you have: 

1) "proc SQL"

2) "%do-loop"

3) "sql code"

4) "proc contents"

5) "data step"

6) "%include with proc datasets"

7) "%end" for do-loop

😎 "quit;"

Basically point 4) turns off proc SQL and in the second iteration of %do-loop tries to execute "sql code" without Proc SQL... 

Bart

[David_Billa]

Thanks for the insights. Were you able to help me fix the issue? I'm not certain how to replace proc contents

[Kurt_Bremser]

Review your process. Start with simple, non-macro code. Then write the first iteration, again without macro language. From this, deduce how the loop should be built.

[yabwon · Accepted solution]

try this:

%macro simple_loop;

%do type_id=1 %to %sysfunc(countw(&type_list,,s));

  proc sql noprint;
    
  connect using &db_lib as database;

  /*some macro code*/
   execute(
          create or replace tbl &schema_temp..&prompt_tbl_name as
            select distinct &current_distinct_var_list
            from &schema_temp..&source_tbl
          ;
        ) by database;
        
        
        create tbl &prompt_tbl_name as
          select * from connection to database (
            select *
            from &schema_temp..&prompt_tbl_name;
          )
        ;
  quit;

  proc contents data=&prompt_tbl_name noprint
  out=index_list(keep=libname memname name);
  run;

  filename code temp;
  data _null_;
  set index_list;
  by libname memname ;
  file code;
  if first.libname then put 'proc datasets nolist lib=' libname ';' ;
  if first.memname then put 'modify ' memname ';' / '  create index ' @;
  put name @;
  if last.memname then put ';' / 'run;' ;
  if last.libname then put 'quit;' ;
  run;  
  %include code / source2;     
%end;    

%mend simple_loop;
%simple_loop;

Bart

[Kurt_Bremser]

Your first statement created by the %DO loop is a PROC SQL statement (EXECUTE), but your loop also contains a PROC CONTENTS, which implicitly terminates your PROC SQL right there.

[David_Billa]

Yes I did this with one iteration before placing my code inside macro. I didn't realise that it will fail in macro looping. Now I'm not sure to correct it inside macro 

[Kurt_Bremser]

That's why you need to first get a complete second iteration of the intended loop to run before coding the macro. Only then will you see which components MUST be inside the loop.