Iteratively pass each row of a data file to a macro

[linlin87]

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!

[andreas_lds]

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.

[linlin87]

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

[PaigeMiller]

---

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

[linlin87]

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').

[linlin87]

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

[PaigeMiller]

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/

[linlin87]

Thanks Paige, I will try this.

[linlin87]

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?

[PaigeMiller]

---

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

[linlin87]

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 

[PaigeMiller]

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.

[linlin87]

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.

[PaigeMiller]

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.

[linlin87]

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;