Value not Setting in Macro or Loop

[DavidPhillips2]

I have a stored process that uses a looping macro technique to look to loop through all departments within a college.  The loop stores the max academic period for each cycle.  Using:

proc sql;

            select max(academic_period) into: max_academic_period from Enrollment;

quit;

The structure contains a number of statements like %str(“Text Goes here.”). 

When calling the macro one time without a loop max_academic_period is set.  When I run this with the loop table Enrollment has data but max_academic_period is not set. 

The single call is:

%callStudentBody(No, Music);

The loop call is

data _null_; set departmentList;                                                                                                                    

            length str $ 200;                                                                                                                      

            str =cats('%callStudentBody(No, %bquote(',department,'))');                                                                                            

            call execute(str);                                                                                                                     

run; 

I cannot post the entire code in this forum.

[PaigeMiller]

I cannot post the entire code in this forum.

It's really difficult (probably impossible) to debug macro code that also uses CALL EXECUTE that we can't see.

[DavidPhillips2]

I am asking if there is generically something I can look into for why a call execute would produce two different results.  One when I feed the logic the same values manually and one when I use a call execute.

[PaigeMiller]

There's plenty of debugging you can do. Make sure you have at the top of your code.

OPTIONS MPRINT SYMBOLGEN MLOGIC;

[DavidPhillips2]

This is the call that is not pulling a max anything:

/*this table has rows*/
Proc SQL;
Create table Enrollment as
Select Academic_year, Term, Student_level, degree_level, degree_level_id,Student_population, academic_period,
College_desc, schev_ethnicity_desc, Minority_group as Minority_group2, Gender_desc,
students_enrolled as Headcount, credit_hours, annual_headcount, FTE
From mytable
Where academic_year in ("2012-2013","2013-2014","2014-2015","2015-2016","2016-2017", "2017-2018", "2018-2019") and
college_desc = "Arts" and department ="Art Education"
order by academic_year, Student_level desc;
quit;

/*this does not produce an error in the log but doesn't seem to do anything.*/
proc sql;
select max(academic_period) into: max_academic_period from Enrollment;
quit;

[PaigeMiller]

Explain "doesn't seem to do anything".

What do you get if you execute

%put &=max_academic_period;

[Kurt_Bremser]

How do you test for the presence/contents of the macro variable?

I see two possible causes

• macro scope (global vs local)
• timing if you test before a step boundary has been reached (the code from the call execute has not yet been able to run)

[Astounding]

There is a generic issue, although it's difficult to tell how that applies here.  When CALL EXECUTE adds statements to a program, the program is in the middle of a DATA step.  So any DATA or PROC statements that get added just stack up and wait for the current DATA step to finish.  However, any macro language statements execute right away.  The order changes.  We can't see what macro language statements are contained within %CallStudentBody, but any of those execute right away without waiting for the current DATA step to finish.

If you determine that is the issue, the usual solution is to apply %nrstr within CALL EXECUTE.  But first determine if that is actually the problem.

[Tom]

There is timing problem when using CALL EXECUTE() to call a macro. It is an issue with macros that modify macro variables from data (call symputx() or select ... into) that are then used to control what code gets generated later in the macro.

If you call the macro like this:

call execute('%mymacro(xxx)');

Then SAS will execute the macro and put the generated code onto the stack to run after the current data step finishes.  So any decisions on values of macro variables is done BEFORE the code that was supposed to set the macro variables has run.

You can delay this by using %NRSTR() macro function, like this:

call execute('%nrstr(%mymacro)(xxx)');

That way just the call to the macro is placed onto the stack to execute after the data step stops.

[data_null__]

---

@Tom wrote:

There is timing problem when using CALL EXECUTE() to call a macro. It is an issue with macros that modify macro variables from data (call symputx() or select ... into) that are then used to control what code gets generated later in the macro.

If you call the macro like this:

call execute('%mymacro(xxx)');

Then SAS will execute the macro and put the generated code onto the stack to run after the current data step finishes.  So any decisions on values of macro variables is done BEFORE the code that was supposed to set the macro variables has run.

You can delay this by using %NRSTR() macro function, like this:

call execute('%nrstr(%mymacro)(xxx)');

That way just the call to the macro is placed onto the stack to execute after the data step stops.

---

In addition to @Tom reply that I'm 99.9% sure is the correct answer I would consider writing the macro calls to file that you %INC, and NOT use CALL EXECUTE.

[DavidPhillips2]

%nrstr 

seems to be in the right direction.  I am testing using it. 

[DavidPhillips2]

nrstr seems to be in the right direction.  I am testing using it. 

[ballardw]

---

@DavidPhillips2 wrote:

I have a stored process that uses a looping macro technique to look to loop through all departments within a college.  The loop stores the max academic period for each cycle.  Using:

proc sql;

            select max(academic_period) into: max_academic_period from Enrollment;

quit;

The structure contains a number of statements like %str(“Text Goes here.”). 

When calling the macro one time without a loop max_academic_period is set.  When I run this with the loop table Enrollment has data but max_academic_period is not set. 

The single call is:

%callStudentBody(No, Music);

The loop call is

data _null_; set departmentList;                                                                                                                    

            length str $ 200;                                                                                                                      

            str =cats('%callStudentBody(No, %bquote(',department,'))');                                                                                            

            call execute(str);                                                                                                                     

run; 

I cannot post the entire code in this forum.

---

It may be worth it to write the strings to a text file and the use %include to execute the code.

That type of values are in the variable academic_period?

[DavidPhillips2]

Academic period is character e.g.

201810

201820

201830

[Tom · Accepted solution]

I do find that writing the generated code to a file is usually much easier to do than using CALL EXECUTE().  You don't have to worry about concatenating values.  And you can run the code generation step and examine the file to make sure your logic is correct.

filename code temp;
data _null_;
  set departmentList;
  file code;
  put '%callStudentBody(No, %bquote(' department +(-1) '))';
run; 
%include code / source2;