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;
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("ed); 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.
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.
Ok, I will try that!
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 ?
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);
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
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.
*--------------------------------------------------------------------------------------------------+
| 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("ed);
%put &unquoted;
%Mend BlanksReplaceBycommas;
%BlanksReplaceBycommas (var1 var2)
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!
select distinct %BlanksReplaceBycommas(&samevarlist) , %BlanksReplaceBycommas(&diffvarlist) ,
Do I need call execute() ?
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("ed); 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.
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.