Do loops and Macro errors

[Kyleuofi]

This is a program that was left to me and seems to be broke, I'm not sure how it's broke.  Essentially there are a few loops that need to run, one for the year, one for the break, and one for the breakdown.  I'm not sure if they are in the wrong order or what?  

option ps=75 ls=68 errors=1  nodate yearcutoff=1940 nocenter MLOGIC MPRINT SYMBOLGEN ;
%include "&path/macros/libname.sas";
%put &path/macros/libname.sas;
%include "&path/macros/tblup.sas";
%put &path/macros/tblup.sas;

/*Works just fine until this point*/
%macro filltableentire_breaks;
proc sql noprint;
              /*selecting count of distinct years into the macrovariable*/;
select count(distinct year) into :cntyr from dis_year;
              /*to remove the spaces created in the above step*/;
  %let cntyr=&cntyr;
              /*selecting the distinct year values into range of macro variables created dynamically by resolving the count macro variable*/;
select distinct year into :year1-:year&cntyr from dis_year;
              /*iterating for the number of years*/;
              /*selecting the count of distinct breakdowns in _breaks_this_indicator */
select count(distinct breakdown) into :cntbreakdown from _breaks_this_indicator;
              /*removing the leading spaces*/
  %let cntbreakdown=&cntbreakdown;
              /**selecting the count of distinct breaks in _breaks_this_indicato*/;
select count(distinct break) into :cntbreak from _breaks_this_indicator;
              /*removing leading spaces*/
  %let cntbreak=&cntbreak;
              /*create breakdown macro variables dynamically using the count macro variable and dash operator*/
select distinct breakdown into :breakdown1-:breakdown&cntbreakdown from _breaks_this_indicator;
              /*create break macro variables dynamically using the count macro variable and dash operator*/
select distinct break into :break1-:break&cntbreak from _breaks_this_indicator;
              /*creates a nested loop where the outer loop iterates for nunber of break values and the */
              /*inner loop iterates for number of breakdown values. A macro variable is created to hold the value of count between the iterations */

 %do i=1 %to &cntyr;
   %do j=1 %to &cntbreak;
    %let m=0;
    %let s=0;
     %do k=1 %to &cntbreakdown;
              /*count is selected into the count macro variable for breakdown and break value.Breakdown
                 and break values are resolved using forward scan rule of double ampersands. unquote and bquote are used to create value wih single quotes*/
        select median(months),months into :med_len from _master
               where &&breakdown&k=%unquote(%bquote('&&break&j'))
                    and year=%unquote(%bquote('&&year&i')) group by year order by months;
        select count(*) into :pcount from _master where &&breakdown&k=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i'));
              /*removes leading spaces*/;
              /*update entire table with s value*/;
     %end;
            update entire set p=&pcount, med_len=&med_len where break=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i'));
   %end;
 %end;
quit;

* %do i=1 %to &cntyr;
*   %do j=1 %to &cntbreak;
*    %let m=0;
*    %let s=0;
*     %do k=1 %to &cntbreakdown;
*              /*count is selected into the count macro variable for breakdown and break value.Breakdown and break values are resolved using forward scan rule of double ampersands. unquote and bquote are used to create value wih single quotes*/
*        select median(months),months into :med_len, :lmon from _master where &&breakdown&k=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i')) group by year order by months;
*        select count(*) into :pcount from _master where &&breakdown&k=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i'));
*              /*removes leading spaces*/;
*         %let pcount=&pcount;
*         %if &pcount=. %then %let pcount=0;
*         %let s=%sysevalf(&pcount+&s);
*              /*update entire table with s value*/;
*     %end;
*            update entire set p=&s, med_len=&med_len where break=%unquote(%bquote('&&break&j')) and year=%unquote(%bquote('&&year&i'));
*   %end;
* %end;
*quit;
*end of the macro program;
%mend;
*macro call;
%filltableentire_breaks;

%lib(dmachine);
%tblup(work.entire,dmedlos.entire)

[mkeintz]

Here's a few starter point for describing a problem that readers might diagnose:

1. Describe what you expected - i.e. amount , arrangement of data
2. Describe what you actually produced.
3. Show the log of the offending program.  Presumably the log induced you to assume you had "macro errors".  Let your advisors (that's us) see it too.   Help us help you.

[Kyleuofi]

Let me preface this with I had to cut a lot out of the log, it was 30 mb when it printed to the directory in plain text.  

What I'm needing this section of the code to do is loop through the different years, break, an breakdown.  create a mean and count and hold those values and append them to the entire table.  What is actually being produced is the entire table with 0's for the count and a table that has the first couple rows in the mean summary, and then the rest 0's which we know is not true.

[Tom]

Most of your log is SYMBOGLEN statements. Turn that option off and your SAS log will be much more readable.  MPRINT is much more important.  So let's look at the first three SAS statements from your log.

MPRINT(FILLTABLEENTIRE):   select count(*) into :pcount from _master where dem_rac='dekalb' and year='2015';
MPRINT(FILLTABLEENTIRE):   select median(months) into :med_lena from _master where dem_rac='dekalb' and year='2015' group by year ;
MPRINT(FILLTABLEENTIRE):   select months into :lmona from _master where dem_rac='dekalb' and year='2015' group by year order by months;
NOTE: No rows were selected.

Remember that if the query returns no rows because no observations match your WHERE condition then the macro variable is not updated.  This does not apply to aggregate functions like COUNT(*) or MEDIAN() as that should return a value even if no observations are found.

So you either need to test the SQLOBS automatic variable after the query to decide if the macro variable can be use.

Or just set some default value for the macro variable before the SELECT statement. 

%let lmona=.;
select months into :lmona from _master where dem_rac='dekalb' and year='2015' group by year order by months;

Plus that query is a little strange.  You are grouping by YEAR, but you already subset to a single year.  So if there is just one observation for that DEM_RAC + YEAR combination then you don't need to the group by.  And if there are multiple then only the first value will be put into the macro variable.  Looks like perhaps you want the min() value?  Note that using MIN(months) might be different than the current query if MONTHS is numeric and there are missing values.  Your current code will select the missing value. But MIN() aggregate function will ignore the missing values and find the smallest non-missing value instead. 

[PaigeMiller]

---

@Kyleuofi wrote:

Let me preface this with I had to cut a lot out of the log, it was 30 mb when it printed to the directory in plain text.  

What I'm needing this section of the code to do is loop through the different years, break, an breakdown.  create a mean and count and hold those values and append them to the entire table.  What is actually being produced is the entire table with 0's for the count and a table that has the first couple rows in the mean summary, and then the rest 0's which we know is not true.

---

Perhaps this is a time to re-do the code entirely. If you are creating means and counts for many different "groups", this can be done without macros and without looping by using PROC SUMMARY and/or PROC FREQ. And so this part of the log (and possibly other parts as well) can be eliminated from a loop and done in one or two PROCs with BY variables.

MPRINT(FILLTABLEENTIRE):   select count(*) into :pcount from _master where dem_rac='dekalb' and year='2015';
MPRINT(FILLTABLEENTIRE):   select median(months) into :med_lena from _master where dem_rac='dekalb' and year='2015' group by year ;
MPRINT(FILLTABLEENTIRE):   select months into :lmona from _master where dem_rac='dekalb' and year='2015' group by year order by months;

But I realize you might not want to re-write the whole thing, although in my mind that would be the best solution.

[PaigeMiller]

Add this command to the top of your program, run it again and show us the log.

options mprint;

[Tom]

Don't use * ... ; comments to comment out macro code.  To the macro process the * ... ; comment is no different than any other SAS statement you want the macro to generate.

Either use %* macro comments or /* ... */ block comments.

[ballardw]

One big hint: The macro processor does not use * <text> ; style for comments. You either want to use %*<text>; or

/* <text> */ style macros. The basic inline comment * <text>; does not keep macro triggers such as & or % from being seen and used.

90% plus of the code that looks like

%unquote(%bquote('&&break&j'))

indicates some serious reconsideration (and likely testing of smaller pieces of code). If the purpose of that construct is to yield the values of indirect macro variable reference in quotes you might try "&&break&j"

%let break= aaa;
%let j = 1;
%let break1 = resolved value;


%let untest = %unquote(%bquote('&&break&j'));
%put untest is &untest;

%let alt = "&&break&j";
%put Alt is &alt;