topic Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf in Advanced Programming

Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Tue, 04 Mar 2025 17:50:17 GMT

The question asks to produce means statistics for a list of number of years for the input dataset, and suggests using a %do %until statement and a %scan function inside a macro to do it. The solution offered is as follows:

For me, this solution is a little bit confusing and complicated because of 1) the %do %until statement is written in a not so frequently used way, i.e., without the i=xxx to xxx part and 2) the logic behind the repeated %let i= and %let yr= is difficult to understand. I think my solution(below) is simpler and easier (without the %do %until and use a %sysfunc(countw) instead) to produce reports or statistics for a list of years (or any other lists of things, as a macro variable).

%macro storms(list);
%let count=%sysfunc(countw(&list));
%do i=1 %to &count;
   %let yr=%scan(&list,&i);
   title "&yr Storms";
   proc means data=mc1.storm_final n min 
                    mean max maxdec=0;
      var MaxWindMPH MinPressure;
      where season=&yr;
   run;
%end;
%mend storms;
%storms(2011 2012 2014);

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

PaigeMiller — Tue, 04 Mar 2025 18:12:07 GMT

@dxiao2017 wrote:

The question asks to produce means statistics for a list of number of years for the input dataset, and suggests using a %do %until statement and a %scan function inside a macro to do it. The solution offered is as follows:

For me, this solution is a little bit confusing and complicated because of 1) the %do %until statement is written in a not so frequently used way, i.e., without the i=xxx to xxx part and 2) the logic behind the repeated %let i= and %let yr= is difficult to understand. I think my solution(below) is simpler and easier (without the %do %until and use a %sysfunc(countw) instead) to produce reports or statistics for a list of years (or any other lists of things, as a macro variable).
%macro storms(list);
%let count=%sysfunc(countw(&list));
%do i=1 %to &count;
   %let yr=%scan(&list,&i);
   title "&yr Storms";
   proc means data=mc1.storm_final n min 
                    mean max maxdec=0;
      var MaxWindMPH MinPressure;
      where season=&yr;
   run;
%end;
%mend storms;
%storms(2011 2012 2014);

The problem here is that although the course is about using macros, this is a problem that doesn't need to be solved by macros, and you would be much better off simply producing the results with PROC MEANS and a BY statement. So it is teaching you to use macros when macros are not needed, in my opinion, this is the wrong lesson to learn. I have said this many times: an important piece of learning to use macros is learning when NOT to use macros.

Example using SASHELP.CARS data set

proc sort data=sashelp.cars out=cars;
    by origin;
run;
proc means data=cars n min mean max maxdec=0;
    where origin='Asia' or origin='Europe';
    by origin;
    var msrp invoice;
run;

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

Tom — Tue, 04 Mar 2025 20:21:41 GMT

I am not sure where this appears in their overall instruction plan but I would definitely agree that it is better to learn how to perform a simple iterative %DO loop before trying to learn how to do a complex one where you have to code your own increments and boundary tests.

Also I am not sure why they left out the %LOCAL statement that should be there to prevent running this macro from modifying existing macro variables named I or YR.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

Quentin — Tue, 04 Mar 2025 20:43:19 GMT

That shown style of looping over a list with %DO %UNTIL was common prior to the introduction of the COUNTW function. I think COUNTW was introduced in v7 or v8.

I would agree that using COUNTW to count the number of items in a list seems clearer to me also.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

quickbluefish — Tue, 04 Mar 2025 23:04:08 GMT

Agree with others here - I would just add that with COUNTW and SCAN (and similar), it's a good idea to specify your delimiter as the optional last argument - otherwise, SAS will try to guess. And further, if the delimiter is a space (as it is here), never a bad idea to clean up potential multiple whitespace characters with %CMPRES:


%let YRLIST=2012 2014 2016;
%let YRLIST=%CMPRES(&YRLIST);
%let nYRS=%sysfunc(countW(&YRLIST,' '));

.... %let YR=%SCAN(&YRLIST, &i, ' ');

* you can also use %STR( ) instead of ' ' above ;

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Wed, 05 Mar 2025 00:49:27 GMT

Thanks a lot for your comments, I think it's a very good point that use macro (or any other advanced and complex procs and steps) only when it is necessary. However, I really do NOT think that question (as well as the solution) in the lesson was poorly designed, as 1) the learning material was to cover a lot of important contents of macro, and it was also designed for test preparation and had to include all the knowledge and techniques in the exam content guide, and therefore 2) while design the material, (I guess) the course developer was intended to integrate as much as possible contents in one question, and this makes the solution code complicated, and 3) besides the macro part of the built-in base 9.4 guide (SAS 9.4 macro language reference), as a new learner I think Macro1:Essential is the good official learning material I can find now.

For that question in my post, what made the solution code complex was the %let i=%eval(&i+1); statement, which now I guess was perhaps exactly what the material developer wanted learners to comprehend (which is not easy, e.g. how the two repeated %let i=; and %let yr=; statements work) , because that statement is used in a lot of scenarios when there is a do loop inside a macro.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Wed, 05 Mar 2025 01:01:06 GMT

There are sections and questions about %local statement in the material. In fact, while doing the question I tried to use %local statement inside the macro but did not run through (as using %local statement inside a macro makes the macro a lot more complicated, will try it later).

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Wed, 05 Mar 2025 01:50:08 GMT

Thanks a lot for your comments! I tested your examples and somehow got something like this (see below). I have three questions: 1) it looks like the %cmpres function is not necessary (at least in my example), if I run the %cmpres statement I got error message; 2) if I want to clean up whitespace characters the function to be use is compbl (I do not know how this function is written when it is a macro function), not compress, am I right or wrong? 3) this question is not relevant, how can I make the code posted here colored?

(after I post THIS message I found it is colored somehow, however, the code in my previous post were not)

%let yrlist=%nrstr(2010 % 2012 2014,2016   2018 & 2019);
*%let yrlist=%cmpres(&yrlist,'%,&');
*%put &yrlist;
%let nyrs=%sysfunc(countw(&yrlist,' %,&'));
%put &nyrs;

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

Quentin — Wed, 05 Mar 2025 03:11:28 GMT

@quickbluefish wrote:

Agree with others here - I would just add that with COUNTW and SCAN (and similar), it's a good idea to specify your delimiter as the optional last argument - otherwise, SAS will try to guess. And further, if the delimiter is a space (as it is here), never a bad idea to clean up potential multiple whitespace characters with %CMPRES:
%let YRLIST=2012 2014 2016;
%let YRLIST=%CMPRES(&YRLIST);
%let nYRS=%sysfunc(countW(&YRLIST,' '));

.... %let YR=%SCAN(&YRLIST, &i, ' ');

* you can also use %STR( ) instead of ' ' above ;

I agree it's good practice to specify the delimiter for COUNTW and %SCAN. Particularly because if you have an empty list, %sysfunc(countw()) will throw an error, but %sysfunc(countw(,%str( ))) will return 0.

That said, I think it's much clearer/better to use %STR( ) to specify the blank as delimiter, rather than ' '. If you use ' ' you're telling the macro processor to use both blank and a single quote as delimiters. In the macro language ' ' is a three character string, not a single blank.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Wed, 05 Mar 2025 04:26:30 GMT

Hi Tom, I have two further questions: 1) could you come up with an example (base on the code in my post) that use %local statement, and 2) explain a bit more on how the %let i=%eval(&i+1); statement work in the code in my post? Many thanks!

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

PaigeMiller — Wed, 05 Mar 2025 14:43:37 GMT

@dxiao2017 wrote:

Thanks a lot for your comments, I think it's a very good point that use macro (or any other advanced and complex procs and steps) only when it is necessary. However, I really do NOT think that question (as well as the solution) in the lesson was poorly designed, as 1) the learning material was to cover a lot of important contents of macro, and it was also designed for test preparation and had to include all the knowledge and techniques in the exam content guide, and therefore 2) while design the material, (I guess) the course developer was intended to integrate as much as possible contents in one question, and this makes the solution code complicated, and 3) besides the macro part of the built-in base 9.4 guide (SAS 9.4 macro language reference), as a new learner I think Macro1:Essential is the good official learning material I can find now.

For that question in my post, what made the solution code complex was the %let i=%eval(&i+1); statement, which now I guess was perhaps exactly what the material developer wanted learners to comprehend (which is not easy, e.g. how the two repeated %let i=; and %let yr=; statements work) , because that statement is used in a lot of scenarios when there is a do loop inside a macro.

A person who takes this macro course then goes out and has a real problem which is similar to the one presented, the person needs means by different years. And the person says "I learned how to do that in my class! I'll write a macro!" And I contend this is the wrong thing to do, and the person has learned the wrong lesson.

If I was going to design a macro class, I would not include using macros to replace other built-in tools in SAS. If the developer of the class wants to teach %let i=%eval(&i+1); there are plenty of ways to teach this without re-creating a BY statement with macro code.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

Tom — Wed, 05 Mar 2025 14:19:34 GMT

@dxiao2017 wrote:

Hi Tom, I have two further questions: 1) could you come up with an example (base on the code in my post) that use %local statement, and 2) explain a bit more on how the %let i=%eval(&i+1); statement work in the code in my post? Many thanks!

Local statement is very simple. Just list the macro variables that you want defined as local to the macro. In this program that would look like:

%macro storms(list);
%local count i yr ;
%let count=%sysfunc(countw(&list,%str( )));
%do i=1 %to &count;
   %let yr=%scan(&list,&i,%str( ));
   title "&yr Storms";
   proc means data=mc1.storm_final n min 
                    mean max maxdec=0
   ;
      where season=&yr;
      var MaxWindMPH MinPressure;
   run;
%end;
%mend storms;
%storms(2011 2012 2014);

To see the impact try running this little program with both versions of the macro. What value does COUNT have at the end?

%let count=100;
%storms(2011 2012 2014)
%put &=count;

Also try running it both ways when the COUNT macro variables does not exist before the macro runs:

%symdel count ;
%storms(2011 2012 2014)
%put &=count;

This statement:

%let i=%eval(&i+1);

Is a simple %LET to assign a value (a new value) to a macro variable. The macro function %EVAL() will evaluate simple integer arithmetic (and boolean) expressions. So &I+1 will convert to something like 1+1 or 2+1 and %EVAL() will then convert that into 2 or 3 and the %LET will store that new digit string into the macro variable I.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Thu, 06 Mar 2025 14:02:28 GMT

I agree with you on this point that when writing a macro is not necessary then do not use macro, when a macro is necessary then use macro and do not use other procedures. There is another question (m105a04) in the material on the PDF's page 262, the solution of which was to produce separate tables for different levels (categories) of a variable, and a cat() statement in a sql step was used to split the table (and this serves as the first step of producing separate TFLs for different levels of a variable), the code and output are as follows (BTW: in the demo m105d04 next to the question, the code was indeed developed to a macro, which I tried several times and was not able to run through, perhaps due to the difficulty in debugging (for both the cat() statement and the case when then; statement in the subsequent data step which was intended to produce separate datasets):

%let tab=sashelp.cars;
%let col=Origin;
proc sql;
select distinct &col
    from &tab;
select distinct cat('when ("', &col, '") output ', &col) 
    from &tab;
quit;

In the above code, the character string in the cat() statement consumes a lot of eyesight and detailed attention to type it. The more important issue is that I think this is a question which can be properly (and more appropriately) solved through a macro (there are other questions and sections for this type of macro in the material though, btw), i.e., a macro here is necessary (BTW, here as I mentioned in last paragraph, I made a mistake here - the code was indeed developed into a macro, yet which possibly may cost a lot debugging) . However, I guess the cat() statement here in the solution code for this question is to let leaners know that some steps in the macro can be replaced by the cat() statement, i.e., a possible usage of the cat() function and cat() statements in a sql step. The macro that I think can properly solve the question and the output are as follows (BTW: the code below was generated according to the solution code on page 286 of the PDF for another question, m105p01, as the reference code, which uses a proc print; step instead of proc sql; select * from; statement). A further step of consideration may be adding in the create table &xxx as to name the different datasets according to their respective categories/levels of that variable.

%macro splittable(tab,col);
proc sql;
select distinct &col
   into :cat1-
   from &tab;
quit;
%do i=1 %to &sqlobs;
proc sql outobs=2;
select * 
   from &tab
   where &col="&&cat&i";
quit;
%end;
%mend splittable;
%splittable(sashelp.cars,origin);

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Thu, 06 Mar 2025 17:09:46 GMT

Hi Tom, thanks a lot for your feedback, but I am not sure I understand your steps correctly or not, below is what I run and the output, there is no difference in this output and the output of that one without the %local statement. For my second question, I think what I asked was: for what scenario the %let i=%eval(&i+1); statement is useful and necessary, because for any list of strings or numbers, we can use select distinct var into :list; and &sqlobs or call symputx(xxx, yyy); or the countw() function to decide how many times the %do loop run.

%macro storms(list);
%local count i yr;
%let count=%sysfunc(countw(&list));
%do i=1 %to &count;
   %let yr=%scan(&list,&i);
   title "&yr Storms (local count)";
   proc means data=mc1.storm_final n min 
                    mean max maxdec=0;
      var MaxWindMPH MinPressure;
      where season=&yr;
   run;
%end;
%mend storms;
%let count=100;
%storms(2011 2012 2014);
%put &count;

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

Quentin — Thu, 06 Mar 2025 17:22:43 GMT

As your post showed, when you run:

%let count=100;
%storms(2011 2012 2014)
%put &count;

If your macro storms uses the %local statement to create a new local macro variable COUNT, the %PUT statement at the end will show that the global macro variable COUNT still has the value 100.

Now if you remove the %local statement from your macro definition and recompile the macro, then run the same code, the %PUT statement at the end will show that the global macro variable COUNT has a different value. Please try it out.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Thu, 06 Mar 2025 20:04:12 GMT

Hi Tom, (I do not know if I understand your comments correctly, it looks like the %local statement is for retaining a macro variable value only for the current use and prevent it from overwriting a previously created macro variable value that has the same macro name, am I right or wrong?) the material did not left out the %local statement and has section and questions for it, I have not get to there yet, because I personally would like to learn write a simple and useful macro (according to what I would like to produce) first, and then learn storing, re-using macros, and etc. alike contents, and thus I omitted the several questions about the %local and %global statements at the moment and will learn it later.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Thu, 06 Mar 2025 20:45:42 GMT

Thanks a lot for your suggestion, I tried some thing like this (see below) after read again in the learning material the section (p174 of the PDF) on %local and %global statements. I tested the code below in windowing (base 9.4) environment (this does not make anything different btw just somehow feel like to mention it for no reason). Will try other examples later.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

Tom — Thu, 06 Mar 2025 20:55:28 GMT

You are close.

You are right that for this example macro it does not impact how this macro behaves. You demonstrated that in your testing. The macro can still assign values to the macro variables it is using and retrieve the values it has assigned.

Where it does make a difference is for the environments (open code or some other running macros) that might call this macro.

This meaning of LOCAL is different than what you will find in most other computer languages that allow local variables. In those languages LOCAL is meant to protect your variables from being seen and/or modified by other programs. But in the SAS macro processor language you use LOCAL macro variables to prevent your program (the currently running macro) from modifying macro variables defined by other macros (or global macro variables).

The %LOCAL statement will create a macro variable in the symbol table of the current macro (if one already exists then it does nothing). Which symbol table the macro is in determines its "scope". You can see all macro variables that exist in the scope of macros that have called the current macro. Except when they are hidden by macro variables with the same name in a "lower" scope (that is closer to the scope of the currently executing macro).

You can also modify those macro variables that live (AKA are defined in; AKA LOCAL to) macros that have called you and global macros. The exception being some system/automatic macro variables that are readonly. And recently SAS added syntax that make it is possible for users to define readonly global macro variables.

When you reference a macro variable using &mvarname SAS will use the macro variable it finds in the lowest scope.

When you assign a value to a macro variable using %LET mvarname= SAS will use an existing macro variable before making a new one. When it does make a new one it is make in the current scope. Note actually when the current macro has not defined any macro variables then CALL SYMPUT() will create new macro variables in the lowest scope that does have macro variables. See discussion in CALL SYMPUT() documentation.

Note that parameters that the macro accepts (defined in the %MACRO statement) are always defined as LOCAL macro variables in the macro's scope.

And if you really want to find the value of macro variable that has been blocked/hidden by a macro variable with the same name at a lower scope you can use this macro: https://github.com/sasutils/macros/blob/master/symget.sas

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

Tom — Thu, 06 Mar 2025 21:05:27 GMT

Your example shows that calling TEST1 did NOT change the value of X defined in the global symbol table. Note that there was not need to wrap the %PUT inside of the macro named TEST2 to show this.

Now make a new version of TEST1 (or make a TEST3) that does NOT include the %LOCAL statement and see what value X has after you call that new macro. It should be 2 instead of the 1 you set it to at the top.

Re: Another way(and simpler) to solve practice question m104p11 on page 224 ,Macro1: Essentials pdf

dxiao2017 — Sat, 08 Mar 2025 15:56:58 GMT

Is this (see below) what you and Tom want me to try? Without the %local statement: got a warning message (&count cannot resolve) and something massed up with the report title(the 2011 one becomes 2014, which is not correct):

%macro storms(list);
*%local count i yr;
%let count=%sysfunc(countw(&list));
%do i=1 %to &count;
   %let yr=%scan(&list,&i);
   title "&yr Storms";
   proc means data=mc1.storm_final n min 
                    mean max maxdec=0;
      var MaxWindMPH MinPressure;
      where season=&yr;
   run;
%end;
%mend storms;
%storms(2011 2012 2014);

%let count=100;
%storms(2011 2012 2014);
%put &count;

%symdel count;
%storms(2011 2012 2014);
%put &count;