Thoughts and questions: why this kind of macro is so difficult to debug.

[dxiao2017]

Hi @ballardw thanks very much for your comments, it's a very good question which makes me explore and learn more.

Part of the purpose of the macro in the practice question I mentioned on page 285, Macro Essential1: course note pdf, is like this: 1) using proc sql to generate a country code &list from a dataset, and 2) if the macro parameter value (i.e., the country code) is among the &list then produce a plot, otherwise put an error message. The first time I did that question I made several mistakes and the macro did not work. Then I simplified the code by create a macro through %let list=; instead of using proc sql and made it worked (as you can see from this post). However, today, I run that part of code using the &list produced through proc sql, it did not work again, the code I tried is as follows:

%macro customerlist(ctry)/minoperator;
proc sql noprint;
select distinct country
   into :ctrylist separated by " "
   from mc1.customers;
quit;
%if &ctry in &ctrylist %then %do;
   title "customers from &ctry";
   proc sgplot data=mc1.customers;
      vbar group;
      yaxis grid;
      where country="&ctry";
   run;
%end;
%else %do;
   %put ERROR: &ctry is an invalid country code.;
   %put ERROR: Valid country codes include &ctrylist..;
%end;
%mend customerlist;
%customerlist(AU);
%customerlist(zz);

The %customerlist(AU) worked well, which produced a plot, but the %customerlist(zz) did not work and there was no error message in the log:

 69         %macro customerlist(ctry)/minoperator;
 70         proc sql noprint;
 71         select distinct country
 72            into :ctrylist separated by " "
 73            from mc1.customers;
 74         quit;
 75         %if &ctry in &ctrylist %then %do;
 76            title "customers from &ctry";
 77            proc sgplot data=mc1.customers;
 78               vbar group;
 79               yaxis grid;
 80               where country="&ctry";
 81            run;
 82         %end;
 83         %else %do;
 84            %put ERROR: &ctry is an invalid country code.;
 85            %put ERROR: Valid country codes include &ctrylist..;
 86         %end;
 87         %mend customerlist;
 88         %customerlist(AU);
NOTE: PROCEDURE SGPLOT used (Total process time):
NOTE: There were 90 observations read from the data set MC1.CUSTOMERS.
       WHERE country='AU';
 89         %customerlist(zz);
 NOTE: PROCEDURE SQL used (Total process time):
NOTE: PROCEDURE SGPLOT used (Total process time):

I did not know why the &list created through %let list=; statement worked but the &list created through proc sql did not work. And in fact another technique that practice question to introduce is the usage of %superq(). I omitted that part because I thought it was too advanced for me and will leave it later. Then I think the reason the macro did not work was I did not use %superq(). After I added it the macro worked, the code and log is as follows:

%macro customerlist(ctry)/minoperator;
proc sql noprint;
select distinct country
   into :ctrylist separated by " "
   from mc1.customers;
quit;
%if %superq(ctry) in %superq(ctrylist) %then %do;
   title "customers from &ctry";
   proc sgplot data=mc1.customers;
      vbar group;
      yaxis grid;
      where country="&ctry";
   run;
%end;
%else %do;
   %put ERROR: &ctry is an invalid country code.;
   %put ERROR: Valid country codes include &ctrylist..;
%end;
%mend customerlist;
%customerlist(AU);
%customerlist(zz);

 69         %macro customerlist(ctry)/minoperator;
 70         proc sql noprint;
 71         select distinct country
 72            into :ctrylist separated by " "
 73            from mc1.customers;
 74         quit;
 75         %if %superq(ctry) in %superq(ctrylist) %then %do;
 76            title "customers from &ctry";
 77            proc sgplot data=mc1.customers;
 78               vbar group;
 79               yaxis grid;
 80               where country="&ctry";
 81            run;
 82         %end;
 83         %else %do;
 84            %put ERROR: &ctry is an invalid country code.;
 85            %put ERROR: Valid country codes include &ctrylist..;
 86         %end;
 87         %mend customerlist;
 88         %customerlist(AU);
 NOTE: PROCEDURE SQL used (Total process time):
 69         %macro customerlist(ctry)/minoperator;
 70         proc sql noprint;
 71         select distinct country
 72            into :ctrylist separated by " "
 73            from mc1.customers;
 74         quit;
 75         %if %superq(ctry) in %superq(ctrylist) %then %do;
 76            title "customers from &ctry";
 77            proc sgplot data=mc1.customers;
 78               vbar group;
 79               yaxis grid;
 80               where country="&ctry";
 81            run;
 82         %end;
 83         %else %do;
 84            %put ERROR: &ctry is an invalid country code.;
 85            %put ERROR: Valid country codes include &ctrylist..;
 86         %end;
 87         %mend customerlist;
 88         %customerlist(AU);
 NOTE: PROCEDURE SQL used (Total process time):
 NOTE: PROCEDURE SGPLOT used (Total process time):
 NOTE: There were 90 observations read from the data set MC1.CUSTOMERS.
       WHERE country='AU';
 89         %customerlist(zz);
 ERROR: zz is an invalid country code.
 ERROR: Valid country codes include AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN 
 IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA.

It's a very brilliant point you said that "the macro processor generates code" and before writing a macro one should know what the code generated look like. I compared the log using macro debug options for the %let list=; macro and the proc sql &list macro, the logs are as follows. Without %superq(), SAS executed the proc sgplot step, whereas with the %superq(), SAS excuted the %put ERROR message step (see the part marked in red).

 69         %macro customerlist(ctry)/minoperator;
 70         proc sql noprint;
 71         select distinct country
 72            into :ctrylist separated by " "
 73            from mc1.customers;
 74         quit;
 75         %if &ctry in &ctrylist %then %do;
 76            title "customers from &ctry";
 77            proc sgplot data=mc1.customers;
 78               vbar group;
 79               yaxis grid;
 80               where country="&ctry";
 81            run;
 82         %end;
 83         %else %do;
 84            %put ERROR: &ctry is an invalid country code.;
 85            %put ERROR: Valid country codes include &ctrylist..;
 86         %end;
 87         %mend customerlist;
 88         /*%customerlist(AU);*/
 89         options symbolgen mprint mlogic;
 90         %customerlist(zz);
 MLOGIC(CUSTOMERLIST):  Beginning execution.
 MLOGIC(CUSTOMERLIST):  Parameter CTRY has value zz
 MPRINT(CUSTOMERLIST):   proc sql noprint;
 MPRINT(CUSTOMERLIST):   select distinct country into :ctrylist separated by " " from mc1.customers;
 MPRINT(CUSTOMERLIST):   quit;

 SYMBOLGEN:  Macro variable CTRY resolves to zz
 SYMBOLGEN:  Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN 
 IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA
 MLOGIC(CUSTOMERLIST):  %IF condition &ctry in &ctrylist is TRUE
 SYMBOLGEN:  Macro variable CTRY resolves to zz
 MPRINT(CUSTOMERLIST):   title "customers from zz";
 MPRINT(CUSTOMERLIST):   proc sgplot data=mc1.customers;
 MPRINT(CUSTOMERLIST):   vbar group;
 MPRINT(CUSTOMERLIST):   yaxis grid;
 SYMBOLGEN:  Macro variable CTRY resolves to zz
 MPRINT(CUSTOMERLIST):   where country="zz";
 MPRINT(CUSTOMERLIST):   run;

 MLOGIC(CUSTOMERLIST):  Ending execution.
 91         options nosymbolgen nomprint nomlogic;

 69         %macro customerlist(ctry)/minoperator;
 70         proc sql noprint;
 71         select distinct country
 72            into :ctrylist separated by " "
 73            from mc1.customers;
 74         quit;
 75         %if %superq(ctry) in %superq(ctrylist) %then %do;
 76            title "customers from &ctry";
 77            proc sgplot data=mc1.customers;
 78               vbar group;
 79               yaxis grid;
 80               where country="&ctry";
 81            run;
 82         %end;
 83         %else %do;
 84            %put ERROR: &ctry is an invalid country code.;
 85            %put ERROR: Valid country codes include &ctrylist..;
 86         %end;
 87         %mend customerlist;
 88         /*%customerlist(AU);*/
 89         options symbolgen mprint mlogic;
 90         %customerlist(zz);
 MLOGIC(CUSTOMERLIST):  Beginning execution.
 MLOGIC(CUSTOMERLIST):  Parameter CTRY has value zz
 MPRINT(CUSTOMERLIST):   proc sql noprint;
 MPRINT(CUSTOMERLIST):   select distinct country into :ctrylist separated by " " from mc1.customers;
 MPRINT(CUSTOMERLIST):   quit;

 MLOGIC(CUSTOMERLIST):  %IF condition %superq(ctry) in %superq(ctrylist) is FALSE
 MLOGIC(CUSTOMERLIST):  %PUT ERROR: &ctry is an invalid country code.
 SYMBOLGEN:  Macro variable CTRY resolves to zz
 ERROR: zz is an invalid country code.
 MLOGIC(CUSTOMERLIST):  %PUT ERROR: Valid country codes include &ctrylist..
 SYMBOLGEN:  Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT 
 LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA
 ERROR: Valid country codes include AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL 
 NO NZ PL PT SE SI TR US YU ZA.
 MLOGIC(CUSTOMERLIST):  Ending execution.
 91         options nosymbolgen nomprint nomlogic;

I agree with you that many of the "loop" type questions about macro can be solved with by group processing, but here according to my understanding so far, I do not think the requirements can be processed through by group, in that the error message cannot be generated through by group processing.

Thanks very much @ballardw again for your valuable input, which enables me learnt more!

[Tom]

Message was too long for me, but I have a quick answer for the first thing.

---

@dxiao2017 wrote:

The correct code is as follows. But I used almost an hour to figure it out and still have questions about it. (ps: I made these mistakes while doing the practice questions on page 285 in Macro1: essentials course notes pdf, that was a complex question.)

1) The correct code and results

%macro ctrylist(ctry1)/minoperator;
%let list1=AT AU CA CH;
%put &list1;
%if &ctry1 in &list1 %then %do;
   %put &ctry1;
%end;
%else %do;
   %put ERROR: &ctry1 is an invalid country code.;
   %put ERROR: Valid country codes include &list1.;
%end;
%mend ctrylist;
%ctrylist(AU);
%ctrylist(zz);

You could use a pre-debugged macro for testing if values are valid, like this one:

https://github.com/sasutils/macros/blob/master/parmv.sas

Then developing your new macro is going to be easier.

%macro ctrylist(ctry1) ;
  %local parmerr;
  %parmv(ctry1,_val=AT AU CA CH)
  %if (&parmerr) %then %do;
      ... code to handle user entry errors ...
  %end;
  %else %do;
     ... code to actually do what you want ...
  %end;
%mend ctrylist;

Example:

 73         %macro ctrylist(ctry1) ;
 74           %local parmerr;
 75           %parmv(ctry1,_val=AT AU CA CH)
 76           %if (&parmerr) %then %do;
 77               %put ... code to handle user entry errors ... ;
 78           %end;
 79           %else %do;
 80              %put &=ctry1 ;
 81              %put ... code to actually do what you want ...;
 82           %end;
 83         %mend ctrylist;
 84         
 85         %ctrylist(AT)
 CTRY1=AT
 ... code to actually do what you want ...
 86         %ctrylist(ca)
 CTRY1=CA
 ... code to actually do what you want ...
 87         %ctrylist(zz)
  
 ERROR: Macro CTRYLIST user error.
 ERROR: ZZ is not a valid value for the CTRY1 parameter.
 ERROR: Allowable values are: AT AU CA CH.
 ... code to handle user entry errors ...
 88         %ctrylist(or)
  
 ERROR: Macro CTRYLIST user error.
 ERROR: OR is not a valid value for the CTRY1 parameter.
 ERROR: Allowable values are: AT AU CA CH.
 ... code to handle user entry errors ...

[dxiao2017]

Hi @Tom although I cannot access github, thanks a lot for telling me this!

So the pre-debugged macro you mentioned was to use the &parmerr combined with the %parmv (which has two parameters, one is the parameter of the outside macro such as ctry1, the other is the list of values such as _val=AT AU CA CH), if the condition of &parmerr is matched then put error message, if not then execute the code to produce what I want. Does this pre-debugged macro need to be downloaded from github? Does SAS has any built-in macro or options (I guess not‌‌😀) I can use for macro debugging (aside from the handy and simple options such as mlogic, mprint, and symbolgen) or options like validate in proc sql (in fact I personally do not think using validate helps much because when there is syntax error the log will give me error message even if I do not use validate, but at least when my syntax is ok it helps confirm that I do not have syntax error. On the other hand, in fact, when my code does not run through, I am more inclined to inspect all the details of the code by myself, as using extra tools sometimes make things more complex):

 69         proc sql number;
 70         validate
 71         select make,origin,msrp
 72            from sashelp.cars
 73            where msrp<(select mean(msrp)+2*std(msrp)
 74                         from sashelp.cars);
 NOTE: PROC SQL statement has valid syntax.
 75         quit;

 76         proc sql number;
 77         validate
 78         select make,origin,msrp
 79            from sashelp.cars
 80            where msrp<(select mean(msrp)+2*std(msrp)
 81                         from sashelp.cars;
                                             _
                                             79
 ERROR 79-322: Expecting a ).
 
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 81                                           )
                                              _
                                              180
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 82         quit;
 NOTE: The SAS System stopped processing this step because of errors.

[Tom]

Validation of the inputs to your macro program has little to do with whatever that SQL VALIDATE statement does.  They just happen to be using the same verb, which is a common issue with computer terminology which picks (sometimes seemingly random) words and gives them special meanings.

I would assume that the VALIDATE statement you played with in SQL is of some value to someone that is writing code that programmatically generates SQL statements and wants to test if the generated syntax is valid before actually running them.  But that is not something I have ever needed to do.

You can go look at the PARMV macro in GITHUB on some other computer (or your phone for that matter).  Read the header to see if what it can do for you has any value for you now (or perhaps in the future for some other project.)

To learn more about how to COMPILE a macro definition so that you can use it in your program check the SAS documentation.  You might want to look at the section on autocall macro libraries.

[Astounding]

Does the original question insist on using the IN operator?  It would be mildly simpler to use %INDEX:

%macro ctrylist(ctry1);
%let list1=AT AU CA CH;
%put &list1;
%if %index(&list1, &ctry1) %then %do;
   %put &ctry1;
%end;
%else %do;
   %put ERROR: &ctry1 is an invalid country code.;
   %put ERROR: Valid country codes include &list1..;
%end;
%mend ctrylist;
%ctrylist(AU)
%ctrylist(zz) 

In general, if you start with simpler tools, adding the complexity of macro language can produce a simpler result.

[dxiao2017]

Hi @Astounding thanks very much for telling me the usage of %index😀, I tested it on the code and it works brightly😀, saves a lot of headache😀. The materials did not insist on using IN operator, it was to let leaners practice the usage of MINOPERATOR. But I'll just use %index(&valuelist,&parametervalue) from now on, it's handy and simple. The code I tested is as follows:

%macro customerlist(ctry)/minoperator;
proc sql noprint;
select distinct country
   into :ctrylist separated by " "
   from mc1.customers;
quit;
%if %index(&ctrylist,&ctry) %then %do;
   title "customers from &ctry";
   proc sgplot data=mc1.customers;
      vbar group;
      yaxis grid;
      where country="&ctry";
   run;
%end;
%else %do;
   %put ERROR: &ctry is an invalid country code.;
   %put ERROR: Valid country codes include &ctrylist..;
%end;
%mend customerlist;
%customerlist(AU);
options symbolgen mprint mlogic;
%customerlist(zz);
options nosymbolgen nomprint nomlogic;

 71         %macro customerlist(ctry)/minoperator;
 72         proc sql noprint;
 73         select distinct country
 74            into :ctrylist separated by " "
 75            from mc1.customers;
 76         quit;
 77         %if %index(&ctrylist,&ctry) %then %do;
 78            title "customers from &ctry";
 79            proc sgplot data=mc1.customers;
 80               vbar group;
 81               yaxis grid;
 82               where country="&ctry";
 83            run;
 84         %end;
 85         %else %do;
 86            %put ERROR: &ctry is an invalid country code.;
 87            %put ERROR: Valid country codes include &ctrylist..;
 88         %end;
 89         %mend customerlist;
 90         %customerlist(AU);

 91         options symbolgen mprint mlogic;
 92         %customerlist(zz);
 MLOGIC(CUSTOMERLIST):  Beginning execution.
 MLOGIC(CUSTOMERLIST):  Parameter CTRY has value zz
 MPRINT(CUSTOMERLIST):   proc sql noprint;
 MPRINT(CUSTOMERLIST):   select distinct country into :ctrylist separated by " " 
 from mc1.customers;
 MPRINT(CUSTOMERLIST):   quit;

 SYMBOLGEN:  Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN 
 IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA
 SYMBOLGEN:  Macro variable CTRY resolves to zz
 MLOGIC(CUSTOMERLIST):  %IF condition %index(&ctrylist,&ctry) is FALSE
 MLOGIC(CUSTOMERLIST):  %PUT ERROR: &ctry is an invalid country code.
 SYMBOLGEN:  Macro variable CTRY resolves to zz
 ERROR: zz is an invalid country code.
 MLOGIC(CUSTOMERLIST):  %PUT ERROR: Valid country codes include &ctrylist..
 SYMBOLGEN:  Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL 
 IN IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA
 ERROR: Valid country codes include AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ 
 NL NO NZ PL PT SE SI TR US YU ZA.
 MLOGIC(CUSTOMERLIST):  Ending execution.
 93         options nosymbolgen nomprint nomlogic;

[Tom]

If you want to use %INDEX() to search for WORDS instead of SUBSTRINGS then it helps to add the delimiter into both the search term and the valid value list. SInce you are using space as the delimiter use %STR() to add macro quoting so that the macro processor sees the leading/trailing spaces.

%if %index(%str( &ctrylist ),%str( &ctry )) %then %do;

This will prevent you from getting false positives for single letter values instead of the two letter codes you want to match.

1    %let CTRYLIST=AT AU BE CA CH CI ;
2    %let CTRY=AU;
3    %put &=ctry is located at byte %index(&ctrylist,&ctry);
CTRY=AU is located at byte 4
4    %put &=ctry is located at byte %index(%str( &ctrylist ),%str( &ctry ));
CTRY=AU is located at byte 4
5
6    %let CTRY=B;
7    %put &=ctry is located at byte %index(&ctrylist,&ctry);
CTRY=B is located at byte 7
8    %put &=ctry is located at byte %index(%str( &ctrylist ),%str( &ctry ));
CTRY=B is located at byte 0

[dxiao2017]

I forgot to delete the minoperator options when use %index, the code which use %index and without the minoperator is as follows:

%macro customerlist(ctry);
proc sql noprint;
select distinct country
   into :ctrylist separated by " "
   from mc1.customers;
quit;
%if %index(&ctrylist,&ctry) %then %do;
   title "customers from &ctry";
   proc sgplot data=mc1.customers;
      vbar group;
      yaxis grid;
      where country="&ctry";
   run;
%end;
%else %do;
   %put ERROR: &ctry is an invalid country code.;
   %put ERROR: Valid country codes include &ctrylist..;
%end;
%mend customerlist;
options symbolgen mprint mlogic;
%customerlist(AU);
%customerlist(zz);
options nosymbolgen nomprint nomlogic;

 SYMBOLGEN:  Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN 
 IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA
 SYMBOLGEN:  Macro variable CTRY resolves to AU
 MLOGIC(CUSTOMERLIST):  %IF condition %index(&ctrylist,&ctry) is TRUE
 SYMBOLGEN:  Macro variable CTRY resolves to AU
 MPRINT(CUSTOMERLIST):   title "customers from AU";
 MPRINT(CUSTOMERLIST):   proc sgplot data=mc1.customers;
 MPRINT(CUSTOMERLIST):   vbar group;
 MPRINT(CUSTOMERLIST):   yaxis grid;
 SYMBOLGEN:  Macro variable CTRY resolves to AU
 MPRINT(CUSTOMERLIST):   where country="AU";
 MPRINT(CUSTOMERLIST):   run;

 NOTE: There were 90 observations read from the data set MC1.CUSTOMERS.
       WHERE country='AU';
 
 MLOGIC(CUSTOMERLIST):  Ending execution.
 90         %customerlist(zz);
 MLOGIC(CUSTOMERLIST):  Beginning execution.
 MLOGIC(CUSTOMERLIST):  Parameter CTRY has value zz
 MPRINT(CUSTOMERLIST):   proc sql noprint;
 MPRINT(CUSTOMERLIST):   select distinct country into :ctrylist separated by " " from mc1.customers;
 MPRINT(CUSTOMERLIST):   quit;

 
 SYMBOLGEN:  Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT 
LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA
 SYMBOLGEN:  Macro variable CTRY resolves to zz
 MLOGIC(CUSTOMERLIST):  %IF condition %index(&ctrylist,&ctry) is FALSE
 MLOGIC(CUSTOMERLIST):  %PUT ERROR: &ctry is an invalid country code.
 SYMBOLGEN:  Macro variable CTRY resolves to zz
 ERROR: zz is an invalid country code.
 MLOGIC(CUSTOMERLIST):  %PUT ERROR: Valid country codes include &ctrylist..
 SYMBOLGEN:  Macro variable CTRYLIST resolves to AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN 
IT LT LU MZ NL NO NZ PL PT SE SI TR US YU ZA
 ERROR: Valid country codes include AT AU BE CA CH CI DE DK EG ES FI FR GB GR HR IE IL IN IT LT LU MZ NL
 NO NZ PL PT SE SI TR US YU  ZA.
 MLOGIC(CUSTOMERLIST):  Ending execution.
 91         options nosymbolgen nomprint nomlogic;

[PaigeMiller]

Related to the original question, there is also the %INM macro which may be of use, and is a lot less typing than creating your own macro to find matches in a text string.

%macro inm(slist,s);
    /* SAS Macro %inm to see if &s is contained in a string or list &slist                 */
    /* Borrowed from https://groups.google.com/forum/#!topic/comp.soft-sys.sas/fWcSDgg11tE */
    %if %sysfunc(indexw(&slist,&s)) gt 0 %then 1 ;
    %else 0;
%mend;

The output of the macro is a 1 indicating a match, or a zero indicates no match.

Example of use:

%let countrylist=AU AT CH;
%let thiscountry=AT;
%if %inm(&countrylist,&thiscountry) %then %do;
    %put This country is &thiscountry, Match found;
%end;
%let thiscountry=RP;
%if not %inm(&countrylist,&thiscountry) %then %do;
    %put This country is &thiscountry, Match not found;
%end;

Sometimes (most times?) asking here in the forum if there is a macro to perform your task is a good first step, rather than just going ahead and trying to write the macro yourself.