topic Re: How to create a "dynamic" macro? in SAS Programming

How to create a "dynamic" macro?

sfmeier — Wed, 22 May 2013 13:03:35 GMT

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?

Re: How to create a "dynamic" macro?

Florent — Wed, 22 May 2013 14:43:12 GMT

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 :Samevar1-:Samevar%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

Re: How to create a "dynamic" macro?

ballardw — Wed, 22 May 2013 14:53:12 GMT

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.

Re: How to create a "dynamic" macro?

Tom — Wed, 22 May 2013 15:06:07 GMT

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;

Re: How to create a "dynamic" macro?

sfmeier — Thu, 23 May 2013 13:57:05 GMT

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?

Re: How to create a "dynamic" macro?

Astounding — Thu, 23 May 2013 14:13:01 GMT

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.

Re: How to create a "dynamic" macro?

sfmeier — Thu, 23 May 2013 15:33:18 GMT

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

Re: How to create a "dynamic" macro?

Astounding — Thu, 23 May 2013 15:41:01 GMT

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.