DATA Step, Macro, Functions and more

How to create a "dynamic" macro?

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

How to create a "dynamic" macro?

Hey folks,

I am using the following macro a lot for data integrity queries. Note that var1 and var2 are used in a different manner. Now I'd like to generalize it in a way, that I can specify m variables of type 1 and n variables of type 2, i.e. the marco call should be something like name (Quelle, m, var_1, ... , var_m, n, var_m+1, var_m+n, Resultvar, Ergebnis). The important thing is that the number of variables in the macro call should depend on the numbers m and n in the macro call itself. Can that be done?

thx, Stefan

%Macro doppel (Quelle, Var1, Var2, Resultvar, Ergebnis);

proc sql;

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

      create table paare as

      select distinct &Var1, &Var2

      from &Quelle;

      /* 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 &Ergebnis as

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

      from &Quelle as r

      inner join doppel

      on doppel.&Var1=r.&Var1;

quit;

%Mend doppel;



Accepted Solutions
Solution
‎06-14-2013 11:19 AM
PROC Star
Posts: 1,233

Re: How to create a "dynamic" macro?

Hi,

To have your helper macro return a comma delimited variable list, you make it into a macro function, something like below:

29   %Macro BlanksReplaceBycommas (string);
30      %local quoted unquoted;
31      %let quoted = %sysfunc(tranwrd(%sysfunc(compbl(&string)),%str( ),%str(, )));
32      %let unquoted = %unquote(&quoted);
33      &unquoted    /*return this*/
34   %Mend BlanksReplaceBycommas;
35
36   %put %BlanksReplaceBycommas (var1 var2);
var1, var2


Note there is no semicolon on line 33.  The macro just returns the string.

Richard DeVenezia has a nice utility macro %seplist, which will allow you to specify delimiters, prefixes, suffixes, etc.  It's very helpful:

http://www.devenezia.com/downloads/sas/macros/index.php?m=seplist

HTH,

--Q.

View solution in original post


All Replies
PROC Star
Posts: 1,564

Re: How to create a "dynamic" macro?

You can do this but not quite the way you imagined.

Keep the original syntax


%Macro doppel (Quelle, Varlist1, Varlist2, Resultvar, Ergebnis);

but Varlist1 and Varlist2 will be a list of space-delimited variable names, and your macro will have to insert the separating commas.

Contributor
Posts: 52

Re: How to create a "dynamic" macro?

Ok, I will try that!

Contributor
Posts: 52

Re: How to create a "dynamic" macro?

I am almost there, the only problem I have is the following join:

on doppel.&Var1=r.&Var1;

is there an easy way to translate that to

doppel.var1=r.var1 doppel.var2=r.var2 ... doppel.varn=doppel.varn ?

Super User
Super User
Posts: 6,502

Re: How to create a "dynamic" macro?

Not clear how want to generalize those queries to support NxM variables, but I find it much easier to pass variable lists as space delimited.  It works well with macro calls and it matches normal SAS syntax for most statements. 

Your macro can then count them if it wants and loop over the list using %SCAN() .

%Macro doppel (Quelle, Var1list, Var2list, Resultvar, Ergebnis);

%local n m i j var1 var2 ;

%let n=%sysfunc(countw(&var1list));

%let m=%sysfunc(countw(&var2list));

%do i=1 %to &n;

   %let var1=%scan(&var1list,&i);

   %do j=1 %to &m;

    %let var2=%scan(&var2list,&j);


Contributor
Posts: 52

Re: How to create a "dynamic" macro?

Well, the Macro is finished, but somehow the calls of the sub-macros does now work... what did I do wrong?

%Macro MultiplevarvaluesFind (source, samevarlist, diffvarlist, resultvarlist, result);     

proc sql;

      /* Selektion aller vorhandenen, verschiedenen Paare von samevarlist und diffvarlist */

      create table paare as

      select distinct %BlanksReplaceBycommas(&samevarlist) , %BlanksReplaceBycommas(&diffvarlist) ,

      from &Source;

      /* Selektion aller Variablen aus der Samevarlist mit mehreren Einträgen in Variablen der Diffvarlist */

      create table doppel as

      select distinct %BlanksReplaceBycommas(&samevarlist) , count(*) as Anzahl

      from paare

      group by %BlanksReplaceBycommas(&samevarlist)

      having Anzahl > 1;

     

      /* Selektion aller Variablen aus der Samevarlist, der Diffvarlist und derResultvarlist */

      create table &Result as

      select distinct %StringwblanksInsertcommanprefix(r., &samevarlist) , %StringwblanksInsertcommanprefix (r., &diffvarlist) ,

                              %StringwblanksInsertcommanprefix(r., &resultvarlist) 

      from &Source as r

      inner join doppel

      on %TablesJoinWvariablesfromstring (doppel, r, &samevarlist);

quit;

%Mend MultiplevarvaluesFind;

LOG:

14         %gaccessible;

SYMBOLGEN:  Macro variable ACCESSIBLE resolves to ACCESSIBLE

15         %MultiplevarvaluesFind (p_hauptfilter, KontoID System, LimitOgID, system, tempi2);

SYMBOLGEN:  Macro variable SAMEVARLIST resolves to KontoID System

SYMBOLGEN:  Macro variable STRING resolves to KontoID System

SYMBOLGEN:  Macro variable QUOTED resolves to KontoID, System

SYMBOLGEN:  Macro variable DIFFVARLIST resolves to LimitOgID

SYMBOLGEN:  Macro variable STRING resolves to LimitOgID

SYMBOLGEN:  Macro variable QUOTED resolves to LimitOgID

NOTE 137-205: Line generated by the invoked macro "MULTIPLEVARVALUESFIND".

15          proc sql;    create table paare as   select distinct %BlanksReplaceBycommas(&samevarlist) ,

                                                                                                      _

                                                                                                      22

15       ! %BlanksReplaceBycommas(&diffvarlist) ,   from &Source;     create table doppel as   select distinct

15       ! %BlanksReplaceBycommas(&samevarlist) , count(*) as

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,

              a missing value, (, *, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, USER, ^, ~. 

SYMBOLGEN:  Macro variable SOURCE resolves to p_hauptfilter

NOTE: Line generated by the invoked macro "MULTIPLEVARVALUESFIND".

15          proc sql;    create table paare as   select distinct %BlanksReplaceBycommas(&samevarlist) ,

                                                                                                      _

                                                                                                      76

15       ! %BlanksReplaceBycommas(&diffvarlist) ,   from &Source;     create table doppel as   select distinct

15       ! %BlanksReplaceBycommas(&samevarlist) , count(*) as

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

SYMBOLGEN:  Macro variable SAMEVARLIST resolves to KontoID System

SYMBOLGEN:  Macro variable STRING resolves to KontoID System

SYMBOLGEN:  Macro variable QUOTED resolves to KontoID, System

NOTE 137-205: Line generated by the invoked macro "MULTIPLEVARVALUESFIND".

2                                                          The SAS System                                14:35 Friday, June 14, 2013

15          proc sql;    create table paare as   select distinct %BlanksReplaceBycommas(&samevarlist) ,

15       ! %BlanksReplaceBycommas(&diffvarlist) ,   from &Source;     create table doppel as   select distinct

15       ! %BlanksReplaceBycommas(&samevarlist) , count(*) as

                                                _

                                                22

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,

              a missing value, *, BTRIM, INPUT, PUT, SUBSTRING, USER. 

SYMBOLGEN:  Macro variable SAMEVARLIST resolves to KontoID System

SYMBOLGEN:  Macro variable STRING resolves to KontoID System

SYMBOLGEN:  Macro variable QUOTED resolves to KontoID, System

NOTE 137-205: Line generated by the invoked macro "MULTIPLEVARVALUESFIND".

15          Anzahl  from paare  group by %BlanksReplaceBycommas(&samevarlist)  having Anzahl > 1;      create table &Result as

                                                                                      ______

                                                                                      22

15       ! select distinct %StringwblanksInsertcommanprefix(r., &samevarlist) , %StringwblanksInsertcommanprefix (r., &diffvarlist)

15       ! ,      %

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, AND, EQ,

              EQT, GE, GET, GT, GTT, HAVING, LE, LET, LT, LTT, NE, NET, OR, ORDER, ^=, |, ||, ~=. 

NOTE: Line generated by the invoked macro "MULTIPLEVARVALUESFIND".

15          Anzahl  from paare  group by %BlanksReplaceBycommas(&samevarlist)  having Anzahl > 1;      create table &Result as

                                                                                      ______

                                                                                      76

15       ! select distinct %StringwblanksInsertcommanprefix(r., &samevarlist) , %StringwblanksInsertcommanprefix (r., &diffvarlist)

15       ! ,      %

ERROR 76-322: Syntax error, statement will be ignored.

SYMBOLGEN:  Macro variable RESULT resolves to tempi2

SYMBOLGEN:  Macro variable SAMEVARLIST resolves to KontoID System

SYMBOLGEN:  Macro variable PREFIX resolves to r.

SYMBOLGEN:  Macro variable STRING resolves to KontoID System

SYMBOLGEN:  Macro variable PREFIX resolves to r.

SYMBOLGEN:  Macro variable QUOTED resolves to r.KontoID ,r.System

SYMBOLGEN:  Macro variable DIFFVARLIST resolves to LimitOgID

SYMBOLGEN:  Macro variable PREFIX resolves to r.

SYMBOLGEN:  Macro variable STRING resolves to LimitOgID

SYMBOLGEN:  Macro variable PREFIX resolves to r.

SYMBOLGEN:  Macro variable QUOTED resolves to r.LimitOgID

SYMBOLGEN:  Macro variable RESULTVARLIST resolves to system

SYMBOLGEN:  Macro variable PREFIX resolves to r.

SYMBOLGEN:  Macro variable STRING resolves to system

SYMBOLGEN:  Macro variable PREFIX resolves to r.

SYMBOLGEN:  Macro variable QUOTED resolves to r.system

NOTE 137-205: Line generated by the invoked macro "MULTIPLEVARVALUESFIND".

15          Anzahl  from paare  group by %BlanksReplaceBycommas(&samevarlist)  having Anzahl > 1;      create table &Result as

15       ! select distinct %StringwblanksInsertcommanprefix(r., &samevarlist) , %StringwblanksInsertcommanprefix (r., &diffvarlist)

                                                                              _

                                                                              22

15       ! ,      %

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,

              a missing value, (, *, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, USER, ^, ~. 

SYMBOLGEN:  Macro variable SOURCE resolves to p_hauptfilter

NOTE: Line generated by the invoked macro "MULTIPLEVARVALUESFIND".

15          Anzahl  from paare  group by %BlanksReplaceBycommas(&samevarlist)  having Anzahl > 1;      create table &Result as

15       ! select distinct %StringwblanksInsertcommanprefix(r., &samevarlist) , %StringwblanksInsertcommanprefix (r., &diffvarlist)

                                                                              _

3                                                          The SAS System                                14:35 Friday, June 14, 2013

                                                                              76

15       ! ,      %

ERROR 76-322: Syntax error, statement will be ignored.

SYMBOLGEN:  Macro variable SAMEVARLIST resolves to KontoID System

SYMBOLGEN:  Macro variable STRING resolves to KontoID System

SYMBOLGEN:  Macro variable N resolves to 2

SYMBOLGEN:  Macro variable STRING resolves to KontoID System

SYMBOLGEN:  Macro variable I resolves to 1

SYMBOLGEN:  Macro variable I resolves to 1

SYMBOLGEN:  Macro variable TABLE1 resolves to doppel

SYMBOLGEN:  Macro variable VAR resolves to KontoID

SYMBOLGEN:  Macro variable TABLE2 resolves to r

SYMBOLGEN:  Macro variable VAR resolves to KontoID

SYMBOLGEN:  Macro variable STRING resolves to KontoID System

SYMBOLGEN:  Macro variable I resolves to 2

SYMBOLGEN:  Macro variable I resolves to 2

SYMBOLGEN:  Macro variable QUOTED resolves to doppel.KontoID=r.KontoID

SYMBOLGEN:  Macro variable TABLE1 resolves to doppel

SYMBOLGEN:  Macro variable VAR resolves to System

SYMBOLGEN:  Macro variable TABLE2 resolves to r

SYMBOLGEN:  Macro variable VAR resolves to System

SYMBOLGEN:  Macro variable QUOTED resolves to doppel.KontoID=r.KontoID and doppel.System=r.System

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

Super User
Posts: 17,868

Re: How to create a "dynamic" macro?

You  haven't provided the full code, specifically the submacro codes, so its hard to tell.

My guess would be that your submacros either miss a comma or introduce too many. 

Contributor
Posts: 52

Re: How to create a "dynamic" macro?

*--------------------------------------------------------------------------------------------------+

|                                   Ersetzt jeweils alle " "in dem String durch " ,"                                   |

*--------------------------------------------------------------------------------------------------;

%Macro BlanksReplaceBycommas (string);

%local quoted unquoted;

%let quoted = %sysfunc(tranwrd(%sysfunc(compbl(&string)),%str( ),%str(, )));

%let unquoted = %unquote(&quoted);

%put &unquoted;

%Mend BlanksReplaceBycommas;

%BlanksReplaceBycommas (var1 var2)

Contributor
Posts: 52

Re: How to create a "dynamic" macro?

This is the first submacro. I think the problem is that I don't know how to pass the string that is being generated by the sub-macro up to the macro! %put obliously does not work!

Super User
Posts: 17,868

Re: How to create a "dynamic" macro?

One bug...the last comma in this line doesn't make sense.


select distinct %BlanksReplaceBycommas(&samevarlist) , %BlanksReplaceBycommas(&diffvarlist) ,

Contributor
Posts: 52

Re: How to create a "dynamic" macro?

Do I need call execute() ?

Solution
‎06-14-2013 11:19 AM
PROC Star
Posts: 1,233

Re: How to create a "dynamic" macro?

Hi,

To have your helper macro return a comma delimited variable list, you make it into a macro function, something like below:

29   %Macro BlanksReplaceBycommas (string);
30      %local quoted unquoted;
31      %let quoted = %sysfunc(tranwrd(%sysfunc(compbl(&string)),%str( ),%str(, )));
32      %let unquoted = %unquote(&quoted);
33      &unquoted    /*return this*/
34   %Mend BlanksReplaceBycommas;
35
36   %put %BlanksReplaceBycommas (var1 var2);
var1, var2


Note there is no semicolon on line 33.  The macro just returns the string.

Richard DeVenezia has a nice utility macro %seplist, which will allow you to specify delimiters, prefixes, suffixes, etc.  It's very helpful:

http://www.devenezia.com/downloads/sas/macros/index.php?m=seplist

HTH,

--Q.

Super User
Super User
Posts: 6,502

Re: How to create a "dynamic" macro?

Looks like extra commas are being generated.

I find it much easier to use macro variables rather than embedding function style macro calls into statements in multiple places.

%macro MultiplevarvaluesFind (source, samevarlist, diffvarlist, resultvarlist, result);    

%local qsame qdiff ;

%let qsame=%sysfunc(tranwrd(%sysfunc(compbl(&samevarlist)),%str(,),%str( )));

%let qdiff=%sysfunc(tranwrd(%sysfunc(compbl(&diffvarlist)),%str(,),%str( )));

....

create table paare as

      select distinct &qsame, &qdiff

      from &Source;

PROC Star
Posts: 1,564

Re: How to create a "dynamic" macro?

Good on you for trying.

Something like this should do (note I create a view first, this should be faster):

%Macro doppel (Quelle, samevarlist, diffvarlist, resultvarlist, Ergebnis);

%local  samevarlist2 diffvarlist2 resultvarlist2 ;


%let samevarlist2   =r.%sysfunc(tranwrd(%sysfunc(compbl(&samevarlist))  ,%str( ),%str(, r.)));

%let diffvarlist2   =r.%sysfunc(tranwrd(%sysfunc(compbl(&diffvarlist))  ,%str( ),%str(, r.)));

%let resultvarlist2 =r.%sysfunc(tranwrd(%sysfunc(compbl(&resultvarlist)),%str( ),%str(, r.)));

proc sql;

  create view paare as

    select distinct &samevarlist2, &diffvarlist2

    from &Quelle r;

  create table doppel as

    select distinct &samevarlist2, count(*) as Anzahl

    from paare r

    group by &samevarlist2

    having Anzahl > 1;

  create table &Ergebnis as

    select distinct &samevarlist2, &diffvarlist2, &resultvarlist2

    from &Quelle as r

    inner join doppel

    on %do i=1 %to %sysfunc(countw(&samevarlist,%str( )));

         %if &i > 1 %then and; doppel.%scan(&samevarlist,&i,%str( ))=r.%scan(&samevarlist,&i,%str( ))

       %end;

    ;

quit;

%mend doppel;

Super User
Posts: 9,682

Re: How to create a "dynamic" macro?

Hi, Or you can try , if your macro has already been builded.

from &Quelle as r

      inner join doppel

      on

%if &Var1 ne and &Var2 eq %then %do;

doppel.&Var1=r.&Var1;

%end;

%else %if &Var1 ne and &Var2 ne %then %do;

doppel.&Var1=r.&Var1 and doppel.&Var2=r.&Var2;

%end;




Ksharp

Message was edited by: xia keshan

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 617 views
  • 6 likes
  • 6 in conversation