BookmarkSubscribeRSS Feed
Calcite | Level 5

Hi! For one simulation, I have 108 combinations of simulation conditions that I specify using macro variables (variations on n, effect size, correlation, etc). I usually just change the values of the macro variables manually for each condition.  I know it must be possible to automate this. I have my conditions in an Excel spreadsheet. Can I have SAS access that spreadsheet, find the values of the macro variables for a specific condition, run the simulation, then automatically find the next set of values for the macro variables? It would do this 108 times, going row by row in Excel to get the values for macro variables each time, and my simulation could be completed with just one click. Any tips/resources greatly appreciated. I have tackled this issue on and off for a couple years and usually just resort to my manual method to "save time". :smileysilly:

Super User

Post your sample data and those condition in Excel file , and the output you need .

SAS Employee


This is a Base SAS solution.

In SAS/IML there might be similar or better, more effective solutions.

There you use IML functions instead of macro of course.

/*This is your simulation code put into a macro*/

%macro simulationMacro(scenario, simpar1, simpar2, simpar3);

     data step using &simpar1 and &simpar2.;

     proc step using &simpar3.;


     /*You might need a step at the end, that collects results into a single dataset*/

     proc append base=all_results data=current_result force;



/*I generate an example parameter table with 4 rows. You have 108 rows.*/

/*Of course you should access your real Excel workbook, with libname ex excel ...;  or proc import */

data data_in_Excel;


input scenario simpar1 simpar2 simpar3;

1 3 4 5

2 4 5 6

3 6 7 8

108 1 1 1



/*Creating a macro, that reads the parameter table, then calls simulation code 108 times*/

%macro do_cycle();

     proc sql noprint;

          select scenario, simpar1, simpar2, simpar3

          into :scenario_list separated by '*', :simpars_list1 separated by '*', :simpar_list2 separated by '*', :simpar_list3 separated by '*'


     %do i=1 %to 108; /* %sysfunc(countw(&scenario_list.,*) )  - instead of hardcoding 108... */

          %let scenario=%scan(&scenario_list.,&i.,*);/*extracting parameters from parameter lists*/

          %let simpar1=%scan(&simpar_list1.,&i.,*);

          %let simpar2=%scan(&simpar_list2.,&i.,*);

          %let simpar3=%scan(&simpar_list3.,&i.,*);

          %simulationMacro(&scenario., &simpar1., &simpar2., &simpar3.);/*calling the simulation*/



/*Calling the macro*/


post processing, etc.


This is a common way to structure a simulation. You don't need to use any macro variables.  In the following example, I use PROC PLAN to create a SAS data set that contains 36 sets of parameter values. I have three parameters for each run.  I use the DO DATA loop in SAS/IML to read a next set of parameter values and then run the simulation with that set of parameters:

/* Hierarchical design example from
proc plan seed=17431;
   factors Houses=3 Pots=4 Plants=3 / noprint;
   output out=Params;

proc iml;
use Params;
do data;
   read next var _NUM_;
   /* run simulation HERE with these parameter values */
   *print Houses Pots Plants;
close Params;

Here is an article about the DO DATA loop.  The book Simulating Data with SAS contains many tips and techniques for efficient simulation with SAS/IML.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 3 replies
  • 4 in conversation