topic Re: Iteratively pass each row of a data file to a macro in SAS Programming

Iteratively pass each row of a data file to a macro

linlin87 — Wed, 25 May 2022 07:59:08 GMT

Hi SAS Forum,

I am a new user, and I want to pass rows from a data file into a macro. However I have no idea how to do this without hard coding. Is there a better way?

To try and explain what I want to do, lets say I have a data file:

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

And I have a macro that takes two inputs and produces a data set:

%macro mymacro(input1=,input2=);

[does stuff...] 

data output;
run;

%end
%mend

Then I want to somehow iteratively pass each row of datafile into the inputs of the macro mymacro, and at each row get a new data output. At the end, I want to concatenate these output data files.

Something like this (but obviously this is not working!):

data test;
set %mymacro(input1=parm1,input2=parm2)
run;

Any help on how I would go about this? Thanks!

Re: Iteratively pass each row of a data file to a macro

andreas_lds — Fri, 27 May 2022 07:39:06 GMT

At first write the code without any macro statements. Think about the creating the dataset "output", what happens to the dataset if you would call it again? Right, the dataset will be overwritten, so you get only the output of the last call. Why do you need to process the data of each observation separately? What exactly happens in "mymacro"? If you need to call a macro from within a data step, have a look at call execute, you will need it.

Re: Iteratively pass each row of a data file to a macro

PaigeMiller — Fri, 27 May 2022 09:49:17 GMT

You could use DOSUBL on each row to run the macro on the values of each row. Something like this (UNTESTED CODE):

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

A real-world example from @ChrisHemedinger is here: https://blogs.sas.com/content/sasdummy/2016/02/15/using-proc-iomoperate/

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 09:50:42 GMT

Hi Andreas,

Thanks for the help. I am not sure how call execute could be implemented for this problem. This is what I have written so far - it might help you understand what I am trying to do.

data sample;
	input param1 param2;
  	datalines;
  	1.0 1.5
	;
run;

data _null_;
	set sample;
  	call symput("x",param1);
  	call symput("y",param2);
run;

%put param1 is = &x;
%put param2 is = &y;

%mymacro(parameter1=&x,parameter2=&y,outputfileidx=01);

What I want is for the data sample to have multiple lines, which I can iteratively pass to %mymacro

Re: Iteratively pass each row of a data file to a macro

PaigeMiller — Fri, 27 May 2022 09:52:34 GMT

@linlin87 wrote:

Hi Andreas,

Thanks for the help. I am not sure how call execute could be implemented for this problem. This is what I have written so far - it might help you understand what I am trying to do.
data sample;
	input param1 param2;
  	datalines;
  	1.0 1.5
	;
run;

data _null_;
	set sample;
  	call symput("x",param1);
  	call symput("y",param2);
run;

%put param1 is = &x;
%put param2 is = &y;

%mymacro(parameter1=&x,parameter2=&y,outputfileidx=01);
What I want is for the data sample to have multiple lines, which I can iteratively pass to %mymacro

The DOSUBL idea I provided above should work, but there may be simpler methods that you ought to consider first. Exactly what is the problem you are trying to solve? At this time, please explain in words, not via SAS code.

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 09:57:53 GMT

Hi Paige,

Thanks for helping.

The macro is running a correlation of performance metrics for a device. It calls a data set, changes two parameters (param1 and param2) in this dataset, calculates the performance metrics, and outputs a dataset. I want to investigate how this performance metric depends on a large sample space of param1 and param2. I hope this answers your question?

I have also tried:

%mymacro(param1=RAND('normal'),param2=RAND('normal'),call=01);

because then I can just have a %do loop and repeatedly call this. But I actually get very strange results when I take this approach. The output is different to if I manually enter the values from RAND('normal').

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 09:58:39 GMT

Thanks Paige, I will try this.

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 10:00:09 GMT

Hi Paige,

The issue as that the output I am interested in extracting from %mymacro is a dataset.

How would you apply dosubl in this instance?

Re: Iteratively pass each row of a data file to a macro

PaigeMiller — Fri, 27 May 2022 10:02:16 GMT

@linlin87 wrote:
Hi Paige,

The issue as that the output I am interested in extracting from %mymacro is a dataset.

How would you apply dosubl in this instance?

No, I want a high level explanation of what this problem is. Not with reference to SAS code or SAS data sets or SAS macros. What is your data, what is the real-world problem you are trying to solve? What are the logical steps?

I have already given example code.

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 10:03:20 GMT

I should add, Andreas, that my macro has a third parameter, so the data file has an index on each run, namely:

%mymacro(1.02,12.5,01)

produces a dataset output_01

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 10:23:37 GMT

Hi Paige,

I am trying to understand how the accuracy of microneurography data is influenced by these two parameters. Is this the higher level you are after?

I can see you have provided code, which works - thank you.

I now have the following:

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

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

but how do I deal with the fact that at every iteration of %mymacro I am overwriting the data file that is generated? I introduced a new variable into the macro which appends an index to the file at each run, and then added a do loop to the data step, but it isn't doing what I expect:

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

I introduce

Re: Iteratively pass each row of a data file to a macro

PaigeMiller — Fri, 27 May 2022 10:24:51 GMT

I am trying to understand how the accuracy of microneurography data is influenced by these two parameters. Is this the higher level you are after?

That's a good start. Now explain the math (at a high level) that you need to do on this data.

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 10:27:16 GMT

Hi Paige,

I have a measure of bias/difference between the microneurography sensor and a reference electrode. I am changing the parameters of the calibration of the sensor (param1 and param2) and then recalculating the bias between this recalibrated sensor and the reference electrode.

Re: Iteratively pass each row of a data file to a macro

PaigeMiller — Fri, 27 May 2022 10:42:37 GMT

Thanks. I think that helps.

Now you say:

but it isn't doing what I expect:

This never helps. When you say something isn't working and provide no additional information, we can't help further. We don't know what you did, we don't know what happened, we don't know what it is doing and we don't know what you expect.

So if there are errors in the log ... you need to show us the ENTIRE log (not selected parts) for this DATA step (or PROC). You need to copy the log as text and then paste it into the window that appears when you click on the </> icon.

If there are no errors but the output isn't right, then EXPLAIN, and show us the incorrect output and describe the output you'd like.

Re: Iteratively pass each row of a data file to a macro

PaigeMiller — Fri, 27 May 2022 10:50:36 GMT

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.

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 10:54:25 GMT

Hi Paige,

Here is the code I now have is below. It is not producing the

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

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

data output_&file_id;
set output_final;
run;

%mend;

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

Re: Iteratively pass each row of a data file to a macro

linlin87 — Fri, 27 May 2022 10:55:37 GMT

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):

Re: Iteratively pass each row of a data file to a macro

PaigeMiller — Fri, 27 May 2022 11:16:26 GMT

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;

Re: Iteratively pass each row of a data file to a macro

Quentin — Fri, 27 May 2022 11:26:23 GMT

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;

Re: Iteratively pass each row of a data file to a macro

Quentin — Fri, 27 May 2022 11:54:03 GMT

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.