DATA Step, Macro, Functions and more

How to create a "dynamic" macro?

Posts: 52

How to create a "dynamic" macro?


I am performing repetitive queries on tables with two kinds of variables, lets call them samevar1-m, diffvar1-n.

I have no idea now to create a macro where I can enter the disired number of variables because once I compile it, the number cannot me changed. How can I make it more flexible?

Is there any way to create a macro

%macro analysis (m, samevar1-m, n, diffvar1-n);


%mendo analysis (m, samevar1-m, n, diffvar1-n);

that uses the desired number of variables?

Frequent Contributor
Posts: 127

Re: How to create a "dynamic" macro?


You could make it 100% dynamic by first querying the dictionnary view containing the column names. This way, you can count how many variables are named like 'samevar%' and then make a loop on these variables.

I would do it as follow:

proc sql;

      select count(distinct name)

      into :m

      from sashelp.vcolumn

      where upcase(libname) = 'YOUR_LIBNAME'

        and upcase(memname) = 'YOUR_TABLENAME'

        and upcase(name) like 'SAMEVAR%';


Then you can use the macro-variable to loop on your variables "Samevar", with something as follow (or any other logic you want to apply):

proc sql;

      select Samevar1-Samevar%left(&m.)

      into Smiley Frustratedamevar1-Smiley Frustratedamevar%left(&m.)

      from your_libname.your_tablename;


%macro analysis;

      %do i= 1 %to %left(&m);

            %put Samevar&i = &&Samevar&i.;


%mend analysis;


I hope it helps.



Super User
Posts: 10,497

Re: How to create a "dynamic" macro?

You don't give any example of what you're doing but another approach is to provide a "base" for the variable and then suffixes;

If numeric it could be something like this:

%macro process(base=samevar, startsuf=1, endsuf=10);

     %do i = &startsuf %to &endsuf;

          proc freq data=dsn;

          tables &base.&i;




If you have two variables with the same suffixes you would add anothe "base". if the suffixes weren't the same add suffix variables.

Super User
Super User
Posts: 6,499

Re: How to create a "dynamic" macro?

I am not sure what the issue here is. 

Being able to provide the input parameters is why you would want to create a macro!!

So if your analysis requires two lists of variables then give the user two macro parameters for them to specify the list.

%macro analysis(data=,list1=,list2=);

proc freq data=&data ;

  tables (&list1)*(&list2) ;


%mend analysis;

Posts: 52

Re: How to create a "dynamic" macro?

Thank you all for you replies.. However I meant something totally different. I have the following macro:


|     Auffinden von allen &Var1 mit verschiedenen Einträgen in der &Var2 in der Tabelle &Source           |

|                        !!!  ALLE Inputvariablen müssen befüllt sein  !!!                                                |

|            Die Variable &Resultvar wird in der Tabelle &Result zusätzlich ausgegeben                      |


%Macro doppel (Source, Var1, Var2, Resultvar, Result);     

proc sql;

      /* Selektion aller vorhandenen, verschiedenen Paare von Var1 und Var2 */

      create table paare as

      select distinct &Var1, &Var2

      from &Source;

      /* Selektion aller Var1 mit mehreren Var2 */

      create table doppel as

      select distinct &Var1, count(*) as Anzahl

      from paare

      group by &Var1

      having Anzahl > 1;


      /* Selektion "Var1", "Var2" und zusätzlicher "Resultvar"iable mit mehrfachen "Namen" */

      create table &Result as

      select distinct r.&Var1, r.&Var2, r.&resultvar

      from &Source as r

      inner join doppel

      on doppel.&Var1=r.&Var1;


%Mend doppel;

It is working. But I want to be able to select up to n Var1-Var_n and m Var_n+1-Var_m+n in the macro call. So how do I do that?

Super User
Posts: 5,081

Re: How to create a "dynamic" macro?

It looks like you are asking for a way to have a macro parse a variable list.  For example, instead of using Var1 as the value of the second parameter, use something like var45-var63.  If that's the case, here's the easy way:

proc contents data=&source (keep=&var1) noprint out=_contents_ (keep=name);


That will get you a data set with all the proper variable names in it.  It doesn't necessarily preserve the order, and Tom has published a neat way to use PROC TRANSPOSE to maintain the order.  But order doesn't seem to matter in your program, so let's stick to the simple way.

Then create a set of macro variables as needed:

proc sql noprint;

   select distinct(name) into : list1 from _contents_;

   select distinct(name) into : list2 separated by ', ' from _contents_;

   select distinct('r.' || name) into : list3 separated by ', ' from _contents_;


Good luck.

Posts: 52

Re: How to create a "dynamic" macro?

Thx... I'd do that, but how can I perform a join on the variable list?

Super User
Posts: 5,081

Re: How to create a "dynamic" macro?

That's just a more complex SELECT statement in the final SQL.  You know the syntax you're looking for better than I, but something along these lines would be close:

select distinct('doppel.' || strip(name) || '=r.' || strip(name)) into : list4 separated by ', ' from _contents_;

The more I think about it, DISTINCT might be overkill here.  There variable names are already distinct ... just a side issue though.

Good luck.

Ask a Question
Discussion stats
  • 7 replies
  • 5 in conversation