Iteratively pass each row of a data file to a macro

[linlin87]

And the error is: 

WARNING: Apparent symbolic reference I not resolved.
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and
              COLUMN where the error has occurred.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;,
              _DATA_, _LAST_, _NULL_.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
      where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.OUTPUT_ may be incomplete.  When this step was stopped there
         were 0 observations and 6 variables.
WARNING: Data set WORK.OUTPUT_ was not replaced because this step was stopped.
WARNING: The data set WORK.I may be incomplete.  When this step was stopped there were 0
         observations and 6 variables.
WARNING: Data set WORK.I was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):

[PaigeMiller · Accepted solution]

You can't use macro variable &i because you have never defined it (this macro variable does not exist).

Your code seems to use a DO loop that doesn't make sense to me, and was not present in my original code

data _null_;
    set datafile;
    	do i=1 to 3;
	x=dosubl("%mymacro(input1="||parm1||",input2="||parm2||",file_id=&i)");
	end;
run;

Try this instead

data _null_;
    set datafile;
    x=dosubl("%mymacro(input1="||parm1||",input2="||parm2||",file_id="||_n_||")");
run;

[Quentin]

Would suggest changing Paige's example to have single quotes around the macro call.  This will prevent the macro call from resolving too early. Suggest:

data _null_;
    set datafile;
    x=dosubl('%mymacro(input1='||parm1||",input2="||parm2||",file_id="||_n_||")");
run;

[Quentin]

This is a great question.  You're new to SAS (?) my guess is not new to programming.  If you're really new to SAS or new to the macro language, I think you should take the time to work this through in steps.  And read a few papers (search lexjansen.com for "CALL EXECUTE and macro"). I would rephrase your question as "how do I use DATA to generate macro calls, rather than type macro calls myself." So this is data-driven programming, using data ("control tables") to generate code.

Paige has recommended DOSUBL.  I would recommend the older CALL EXECUTE, which is similar, but should be a bit easier to learn because it shows more information in the log.

First, with any macro development you need to start with working SAS code, and then move to working macro code.  You have that:

data output_final ;
  set sashelp.class ;
run ;

%macro mymacro(input1=,input2=,file_id=);
  *[code for recomputing bias];

  %put RUNNING %nrstr(%mymacro) with parameters &=input1 &=input2 &=file_id  ;

  data output_&file_id;
    set output_final;
  run;
%mend;

%mymacro(input1=1.1,input2=2.0,file_id=1)
%mymacro(input1=1.2,input2=2.0,file_id=2)
%mymacro(input1=1.3,input2=2.0,file_id=3)

So make sure you get to the point where your can write three macro calls, and get the output you want.

After that, you move on to generating three macro calls from data, instead of typing the macro calls.  

Within a DATA step, you can use the DOSUBL function or the CALL EXECUTE routine to generate SAS code, in this case generate macro calls.

data datafile;
input parm1 parm2;
datalines;
1.1 2.0
1.2 2.0
1.3 2.0
;
run;

data _null_;
  set datafile;
  **x=dosubl(
           '%mymacro(input1='||put(parm1,8.1)||",input2="||put(parm2,8.1)||",file_id="||put(_n_,8.)||")" 
           );

  call execute(
               '%nrstr(%mymacro)(input1='||put(parm1,8.1)||",input2="||put(parm2,8.1)||",file_id="||put(_n_,8.)||")"
           );
run;

The code for CALL EXECUTE is a bit uglier than DOSUBL because you need to add %NRSTR() to delay macro execution a bit.  But the benefit is CALL EXECUTE will show you the generated code in the log.  So in this case, you can see that CALL EXECUTE generated the three macro calls you intended:

29   data _null_;
30     set datafile;
31     **x=dosubl(
32              '%mymacro(input1='||put(parm1,8.1)||",input2="||put(parm2,8.1)||",file_id="||put(_
32 ! n_,8.)||")"
33              );
34
35     call execute(
36                  '%nrstr(%mymacro)(input1='||put(parm1,8.1)||",input2="||put(parm2,8.1)||",file
36 ! _id="||put(_n_,8.)||")"
37              );
38   run;

NOTE: There were 3 observations read from the data set WORK.DATAFILE.

NOTE: CALL EXECUTE generated line.
MPRINT(MYMACRO):   *[code for recomputing bias];
1   + %mymacro(input1=     1.1,input2=     2.0,file_id=       1)
RUNNING %mymacro with parameters INPUT1=1.1 INPUT2=2.0 FILE_ID=1
MPRINT(MYMACRO):   data output_1;
MPRINT(MYMACRO):   set output_final;
MPRINT(MYMACRO):   run;

NOTE: There were 19 observations read from the data set WORK.OUTPUT_FINAL.
NOTE: The data set WORK.OUTPUT_1 has 19 observations and 5 variables.

MPRINT(MYMACRO):   *[code for recomputing bias];
2   + %mymacro(input1=     1.2,input2=     2.0,file_id=       2)
RUNNING %mymacro with parameters INPUT1=1.2 INPUT2=2.0 FILE_ID=2
MPRINT(MYMACRO):   data output_2;
MPRINT(MYMACRO):   set output_final;
MPRINT(MYMACRO):   run;

NOTE: There were 19 observations read from the data set WORK.OUTPUT_FINAL.
NOTE: The data set WORK.OUTPUT_2 has 19 observations and 5 variables.

MPRINT(MYMACRO):   *[code for recomputing bias];
3   + %mymacro(input1=     1.3,input2=     2.0,file_id=       3)
RUNNING %mymacro with parameters INPUT1=1.3 INPUT2=2.0 FILE_ID=3
MPRINT(MYMACRO):   data output_3;
MPRINT(MYMACRO):   set output_final;
MPRINT(MYMACRO):   run;

NOTE: There were 19 observations read from the data set WORK.OUTPUT_FINAL.
NOTE: The data set WORK.OUTPUT_3 has 19 observations and 5 variables.

The macro language is inherently complex, because you're writing macro language code to generate SAS code.  Data-driven programming with macros is complex-er because your writing SAS code to generate macro language calls that generate SAS code.  If you work on it in steps (working SAS code, working macro calls, working generation of macro calls), it is much easier to debug when things break or give surprising results.  Because you can test whether the SAS code works, then test whether hand written macro calls work, then check whether you are actually generating the same macro calls as you are writing by hand.

[Tom]

So you have this macro definition.  Let's assume it works properly.

%macro mymacro(input1=,input2=,file_id=);
...
%mend mymacro;

So you want to call this macro once for each observation in a dataset.

So make a dataset with variables named INPUT1, INPUT2 and FILE_ID. (You will see why it helps to have the variable names match the parameter names later.)

data have;
  input input1 input2;
  file_id+1;
datalines;
1.1 2.0 
1.2 2.0 
1.3 2.0 
;

And use that to generate one call per observation.

So it is trivial it you treat it as a report generation step.

filename code temp;
data _null_;
  set have;
  file code;
  put '%mymacro(' input1= ',' input2= ',' file_id= ')' ;
run;

Now you can run the generated code using the %INCLUDE statement.

%include code / source2;

If you really want to use CALL EXECUTE() then it helps to define the macro to allow it to accept the parameters by position.  (Note that defining the macro to accept the parameters values by position does not prevent you form passing the values by name, so the previous example data step that generate the macro calls with name=value would still work with this updated macro statement.) 

%macro mymacro(input1,input2,file_id);

Since then you don't have to type both the parameter names and the variable names when generating the text of the macro call.  Make sure to wrap at least the %MYMACRO inside of %NRSTR() so that SAS does not try to expand the macro call while it is pushing the code onto the stack to run after the data step.

data _null_;
  set have;
  call execute(cats('%nrstr(%mymacro)(',input1,',',input2,',',file_id,')'));
run;

If you cannot modify the macro definition then just type more constant text into the call execute() statement.

  call execute(cats('%nrstr(%mymacro)(input1=',input1,',input2=',input2,',file_id=',file_id,')'));

[PaigeMiller]

ADDING: sometimes, when we ask, people say something like: "I want to compute the mean and standard deviation on each row" and then we tell them there are much simpler ways to do this without using macros or DOSUBL on each row. Now that you have explained, I think you are correct that this probably should be done by running a macro on each row.