BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sfmeier
Obsidian | Level 7

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;


1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

15 REPLIES 15
ChrisNZ
Tourmaline | Level 20

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.

sfmeier
Obsidian | Level 7

Ok, I will try that!

sfmeier
Obsidian | Level 7

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 ?

Tom
Super User Tom
Super User

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


sfmeier
Obsidian | Level 7

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

Reeza
Super User

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. 

sfmeier
Obsidian | Level 7

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

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

sfmeier
Obsidian | Level 7

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!

Reeza
Super User

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


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

sfmeier
Obsidian | Level 7

Do I need call execute() ?

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Tom
Super User Tom
Super User

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;

ChrisNZ
Tourmaline | Level 20

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;

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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