topic Re: most efficient method to loop a macro ? in SAS Programming

most efficient method to loop a macro ?

kansas — Sun, 07 Aug 2011 04:02:53 GMT

Hi,

After researching i found out two methods of looping a macro based on a dataset variable.

suppose my

dataset = data1 and variable=var1

Purpose: I need to run "my_macro" for each var1 value in the dataset

method1

%macro my_macro(var);

%mend;

data _null_;

set data1;

call execute("%my_macro(" || var1 || ");");

run;

method2

Can use macro arrays introduced in http://www2.sas.com/proceedings/sugi31/040-31.pdf (Tight looping with macro arrays)

Both methods work perfectly.. But i want to know the most recommended method. (By number of lines, method1 seems the best for me. )

Thanks in advance.

most efficient method to loop a macro ?

art297 — Sun, 07 Aug 2011 04:06:30 GMT

Do you need to run the macro for each record or just for each unique value in var1?

Re: most efficient method to loop a macro ?

kansas — Sun, 07 Aug 2011 04:12:54 GMT

above is an example art297. My aim is to change the parameter value in a macro based on a dataset vallues.

yes for each record

Re: most efficient method to loop a macro ?

sbb — Sun, 07 Aug 2011 14:23:06 GMT

Note from the SAS documentation, your CALL EXECUTE will not be executed until the DATA step has been completed (up to the RUN; statement). And so the macro code cannot be a continuation of the current DATA step. Given this situation, it will be important to know what SAS statements are contained in your SAS macro for execution.

Also you will see the SAS execution logic with this additional diagnostic statement, at least prior to your DATA step:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN /* MLOGIC */ MPRINT;

Scott Barry

SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

call execute site:sas.com

Re: most efficient method to loop a macro ?

kansas — Mon, 08 Aug 2011 04:17:20 GMT

ok. this is what i'm trying to achieve
suppose a dataset containining var1
values
22

i want to run my macro for each and every value of var1 (var1 values are unique).

so basically,this is what im going to achieve by using a call execute step.

%my_macro(22);

%my_macro(36);

%my_macro(25);

and how does "SAS statements are contained in your SAS macro for execution" going to affect the program ?

my macro has some proc statements and some datasteps.

The code works as expected. I want know whether this code is against the coding best practices.

Thanks.

Re: most efficient method to loop a macro ?

art297 — Mon, 08 Aug 2011 12:31:51 GMT

Can you post an actual example, including sample data along with your macro and code that calls it?

Re: most efficient method to loop a macro ?

mojerry2 — Mon, 08 Aug 2011 12:48:47 GMT

there are multiple solutions to have the same result.

I think your method is the best one even if the call execute will be executed at the end of your datastep. so first reading the whole dataset and then execute your macro calls.

the other method is like a nice "brainfuck" but can be really useful.

but if you want to use this option then you have to put all your variables in an array and then use the %do_over

Re: most efficient method to loop a macro ?

DLing — Mon, 08 Aug 2011 14:08:44 GMT

Here's a method to loop a macro over every value in a dataset. The macro itself sequentially reads a SAS dataset, so it doesn't use a macro array, and since it's done in a macro, there's no call execute. All the methods above should work, this is another one of many approaches possible.

%macro loop( data );

%let dsid=%sysfunc(open(&data));

%if (&dsid = 0) %then %do;

%put %sysfunc(sysmsg());

%return;

%end;

%syscall set(dsid); * links sas dataset variables to macro variables, watch out for name collision ;

%do %while( %sysfunc(fetch(&dsid)) = 0 );

%work(&msgid) * replace with anything you need like a macro call with arguments ;

%end;

%let dsid=%sysfunc(close(&dsid));

%mend;

%macro work( arg );

%put &arg;

%mend;

%loop( sashelp.adomsg )

This version lists the "msgid" column from sashelp.adomsg dataset.

It illustrates the idea of using a macro to loop over SAS dataset and generate code. And because it's all inside a macro, the looping and code generation is easier without needing to maintain arrays... etc.

Modify to suite your needs

Re: most efficient method to loop a macro ?

data_null__ — Mon, 08 Aug 2011 14:20:03 GMT

Most of the time when a macro is called with CALL EXECUTE and the macro generates SAS datastep and proc code you want to delay execution of the macro until after the data step is finished.

Therefore you would change your code to include %NRSTR.

call execute("%my_macro(" || var1 || ");");

call execute("%nrstr(%my_macro(" || var1 || ");)");

Using NRSTR will prevent the macro from being called when CALL EXECUTE "executes". This is usually the action you need, especially if the macro being called uses data or proc steps to create or alter macro variables that are then used in subsequent program steps.

I think it is all explained in detail in the documentation which I encourage you to read.

Re: most efficient method to loop a macro ?

kansas — Mon, 08 Aug 2011 16:52:48 GMT

"so first reading the whole dataset and then execute your macro calls."
so macro statement wont be executed until the datastep completely finishes ? All this time i was thinking that macro statement gets executed at the end of each iteration .

Re: most efficient method to loop a macro ?

kansas — Mon, 08 Aug 2011 18:07:24 GMT

Thanks Dling. Will definitely try your code as well

most efficient method to loop a macro ?

Peter_C — Tue, 09 Aug 2011 13:25:56 GMT

As a list of calling parameters is not always sourced from a data set, you might like a look at this alternative.

Not limited to execution within a data step, there is a macro looper described in a SUGI paper http://www2.sas.com/proceedings/sugi31/012-31.pdf

peterC