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

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

David_Billa — Tue, 28 Feb 2023 07:33:47 GMT

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;

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

yabwon — Tue, 28 Feb 2023 07:54:25 GMT

Before running the code add:

options mprint symbolgen;

to it and share log results.

Bart

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

David_Billa — Tue, 28 Feb 2023 08:16:21 GMT

@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;

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

yabwon — Tue, 28 Feb 2023 08:25:42 GMT

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

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

David_Billa — Tue, 28 Feb 2023 08:28:32 GMT

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

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

Kurt_Bremser — Tue, 28 Feb 2023 08:29:07 GMT

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.

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

Kurt_Bremser — Tue, 28 Feb 2023 08:31:13 GMT

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.

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

David_Billa — Tue, 28 Feb 2023 08:34:16 GMT

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

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

yabwon — Tue, 28 Feb 2023 08:34:36 GMT

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

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

Kurt_Bremser — Tue, 28 Feb 2023 08:42:48 GMT

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.