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);
Accepted Solutions
@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;
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);
My comments in this post also apply to the practice question (level-challenge) on page 317 of SAS SQL1: Essentials course note PDF, the question and answer code (which I guess I would not use the code if there is any other alternative I can choose) are as follows:
And have to say countw() function is not good here because the character string is complex (has several ampersand& and one hyphen-) and struggle many times using %nrstr to deal with delimiters (and this till now I do not think I can figure out the thorough and detailed usage) was not able to fix the errors (see below). The best solution perhaps is the select into :list1- and %do i=1 %to &sqlobs one.
/* %put ®ionvalue; */
/* */
/* %let n=%sysfunc(countw(a&b,c&d,e&f,g-h)); */
/* %put &n; */
/* */
/* %let n=%sysfunc(countw(®ionvalue)); */
/* %put &n; */
/* */
/* %let string=%nrstr(®ionvalue); */
/* %put &string; */
/* %let i=%sysfunc(countw(&string,',')); */
/* %put &i; */
%macro split(tab,col);
proc sql;
select distinct &col
into :colvalue1-
from &tab
where &col^=' ';
quit;
%do i=1 %to &sqlobs;
proc sql;
select *
from &tab
where &col="&&colvalue&i";
quit;
%end;
%mend split;
%split(sq.globalmetadata,region);
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;
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.
