DATA Step, Macro, Functions and more

How to create a "dynamic" macro?

Reply
Contributor
Posts: 53

How to create a "dynamic" macro?

Problem

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?

Hi,

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%';

quit;

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;

quit;

%macro analysis;

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

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

      %end;

%mend analysis;

%analysis

I hope it helps.

Regards,

Florent

Super User
Posts: 11,343

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;

          end;

     %end;

%mend;

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: 7,074

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) ;

run;

%mend analysis;

Contributor
Posts: 53

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;

quit;

%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,516

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);

run;

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_;

quit;

Good luck.

Contributor
Posts: 53

Re: How to create a "dynamic" macro?

Posted in reply to Astounding

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

Super User
Posts: 5,516

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
  • 396 views
  • 0 likes
  • 5 in conversation