Dynamic macro values

[texasmfp]

I have a working macro, that substitutes a code value on each iteration. The following example has three codes/iterations

%macro imp (code); 
             various data tasks
%mend imp;

%imp(44123310)
%imp(44123320)
%imp(44123390)

However, the reality is that I have hundreds of codes/iterations to run, which can be tedious (and lead to human error).  Instead of hardcoding each value and tailoring the number of iterations by hand, I'd like to automate it from a list of values for the code variable.  Perhaps the list of code values is in a spreadsheet.   I'd appreciate any tips.  SAS 9.4 M6

[ballardw]

Look at CALL EXECUTE in a data step. You can use data step variable(s) in submitted code.

Note: there are some tricks about calling macros though as you don't want them called in the data step .

Example:

%macro dummy( parm);
  %put parm is: &parm.;
%mend;


data _null_;
   set sashelp.class;
   call execute ('%dummy('||name||')');
run;

Call execute places statements into a buffer that executes after the data step completes. So create a string that will not attempt to execute the macro by placing it in single quotes.

Or use a data step to write the macro calls to a program file and then %include the that file after the data step completes.

It might also be as simple as BY group processing, possibly with a WHERE statement. It might help to provide some details on the actual tasks. The macro may not be needed at all.

[yabwon]

Hi @texasmfp ,

You already got a bunch of great solutions frmo: @ballardw , @PaigeMiller , and @SASJedi 

I just want to share one more. It's called macroArray package.

When you install macroArray package and load it into your session, assuming you have an Excel with data (i'm using Mark's example here):

/* Create the Excel spreadsheet to use for demo purposes */
libname xl xlsx "myFile.xlsx";
data xl.myCodes;
	do Code=44123310,44123320,44123390;
		output;
	end;
run;
libname xl clear;

/* Compile the desired macro */
%macro imp (code); 
   %PUT NOTE: Macro &sysmacroname executing for &=code;
%mend imp;

all you need are the %array() and the %do_over() macros,  run then like this:

libname xl xlsx "R:\myFile.xlsx";
%array(ds=xl.myCodes, vars=Code, macarray=Y)

%do_over(Code,phrase=%nrstr(
  %imp(%code(&_i_)) 
))

and in the log you will see:

1    libname xl xlsx "R:\myFile.xlsx";
NOTE: Libref XL was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: R:\myFile.xlsx
2    %array(ds=xl.myCodes, vars=Code, macarray=Y)
NOTE:[ARRAY] 3 macrovariables created
3
4    %do_over(Code,phrase=%nrstr(
5      %imp(%code(&_i_))
6    ))
NOTE: Macro IMP executing for CODE=44123310
NOTE: Macro IMP executing for CODE=44123320
NOTE: Macro IMP executing for CODE=44123390

All the best

Bart

P.S. To install and use macroArray package do:

• Enable the framework [first time only]:

• filename SPFinit url "https://raw.githubusercontent.com/yabwon/SAS_PACKAGES/main/SPF/SPFinit.sas";
  %include SPFinit; /* enable the framework */

• Install the framework and the package on your machine in the folder you created:

• filename packages "</your/directory/for/packages/>"; 
  %installPackage(SPFinit macroArray) 

• From now on, in your SAS session just run it like this:

• filename packages "</your/directory/for/packages/>";
  %include packages(SPFinit.sas);
  
  %loadPackage(packageName)  

Link to details.

[PaigeMiller]

Since you don't tell us what these "various data tasks" are, I create my own. I will use each value of code in a text file (not an excel file) and then do, in my example: proc print data=real_data(where=(id=44123320)); run;

Here is the text file named example_data.txt

44123310
44123320
44123390

here is the SAS code

data _null_;
    infile "example_data.txt";
    input code;
    call execute(cats('proc print data=real_data(where=(id=',code,')); run;'));
run;

You will see in the log that the desired PROC PRINTs appear (they don't run because I don't really have data). You could also use macros inside CALL EXECUTE if that would work better. Depending on what you are trying to do (which you really didn't tell us), macros may or may not be needed.

[SASJedi · Accepted solution]

Here are a couple of suggestions:

First, let's make that Excel spreadsheet containing the codes, and compile the desired macro:

/* Create the Excel spreadsheet to use for demo purposes */
libname xl xlsx "myFile.xlsx";
data xl.myCodes;
	do Code=44123310,44123320,44123390;
		output;
	end;
run;
libname xl clear;

/* Compile the desired macro */
%macro imp (code); 
   %PUT NOTE: Macro &sysmacroname executing for &=code;
%mend imp;

Next, we can read the data from Excel using SQL and iterate over the codes:

/* Suggested solution #1 */
libname xl xlsx "myFile.xlsx";

/* Create a driver macro to drive execution */
%macro runIMP;
/* Get the codes in a series of macro variables Code1-CodeN */
proc sql noprint;
	select Code into :code1-
	from xl.myCodes
;
quit;

/* SQLOBS records number of rows returned by the query */
/* Execute the desired macro once for each code returned */
%do i=1 %to &sqlobs;
	%imp(&&Code&i)
%end;
%mend;

%runIMP;
libname xl clear;
/* End Solution #1 */

From the Log:

NOTE: Macro IMP executing for CODE=44123310
NOTE: Macro IMP executing for CODE=44123320
NOTE: Macro IMP executing for CODE=44123390

Another solution is to use a DATA step and DOSUBL:

/* Suggestion 2: Use DATA step and DOSUBL */
libname xl xlsx "myFile.xlsx";

data _null_;
	/* read inthe codes */
	set xl.myCodes;
	/* Execute the macro once for each code */
	rc=dosubl(cats('%imp(',code,')'));
run;
libname xl clear;
/* End Solution #2 */

From the Log:

NOTE: Macro IMP executing for CODE=44123310
NOTE: Macro IMP executing for CODE=44123320
NOTE: Macro IMP executing for CODE=44123390
NOTE: The import data set has 3 observations and 1 variables.
NOTE: There were 3 observations read from the data set XL.myCodes.

[texasmfp]

Thanks to all of you for excellent suggestions.  I am accepting this answer, but all are good options.  Thanks