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

How to create a "dynamic" macro?

sfmeier — Fri, 14 Jun 2013 07:08:55 GMT

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;

Re: How to create a "dynamic" macro?

ChrisNZ — Fri, 14 Jun 2013 11:48:33 GMT

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.

Re: How to create a "dynamic" macro?

sfmeier — Fri, 14 Jun 2013 12:34:42 GMT

Ok, I will try that!

Re: How to create a "dynamic" macro?

sfmeier — Fri, 14 Jun 2013 13:21:20 GMT

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 ?

Re: How to create a "dynamic" macro?

Tom — Fri, 14 Jun 2013 13:25:14 GMT

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

Re: How to create a "dynamic" macro?

sfmeier — Fri, 14 Jun 2013 14:33:54 GMT

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

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

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

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

______

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

______

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)

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

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

Re: How to create a "dynamic" macro?

Reeza — Fri, 14 Jun 2013 14:39:08 GMT

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.

Re: How to create a "dynamic" macro?

sfmeier — Fri, 14 Jun 2013 14:44:42 GMT

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

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

Re: How to create a "dynamic" macro?

sfmeier — Fri, 14 Jun 2013 14:45:42 GMT

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!

Re: How to create a "dynamic" macro?

Reeza — Fri, 14 Jun 2013 15:00:58 GMT

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

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

Re: How to create a "dynamic" macro?

sfmeier — Fri, 14 Jun 2013 15:03:01 GMT

Do I need call execute() ?

Re: How to create a "dynamic" macro?

Quentin — Fri, 14 Jun 2013 15:19:35 GMT

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.

Re: How to create a "dynamic" macro?

Tom — Fri, 14 Jun 2013 16:02:56 GMT

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;

Re: How to create a "dynamic" macro?

ChrisNZ — Fri, 14 Jun 2013 23:24:22 GMT

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;

Re: How to create a "dynamic" macro?

Ksharp — Mon, 17 Jun 2013 04:02:51 GMT

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

from &Quelle as r

inner join doppel

%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

Re: How to create a "dynamic" macro?

sfmeier — Mon, 17 Jun 2013 12:12:54 GMT

Thank you all for your help. It is finally working and I learned a lot!

Stefan