SAS Sum function inside macro trouble

Question

data Month1;
input Name $ sales;
cards;
Joyce 235
Marsha 352
Bill 491
Vernon 210
Sally 418
;
data Month2;
input Name $ sales;
cards;
Joyce 169
Marsha 281
Bill 315
Vernon 397
Sally 305
;
data Month3;
input Name $ sales;
cards;
Joyce 471
Marsha 314
Bill 394
Vernon 291
Sally 337
;
data Month4;
input Name $ sales;
cards;
Joyce 338
Marsha 259
Bill 310
Vernon 432
Sally 362
;
data Month5;
input Name $ sales;
cards;
Joyce 209
Marsha 355
Bill 302
Vernon 416
Sally 475
;
data Month6;
input Name $ sales;
cards;
Joyce 306
Marsha 472
Bill 351
Vernon 405
Sally 358
;

options sgen;
%let qtr=qtr1;
%Macro ProcSql;
    Proc Sql;
    %if &qtr=qtr1 %then %do;
        %let month1=month1;
        %let month2=month2;
        %let month3=month3;
    %end;
    %else %if &qtr=qtr2 %then %do;
        %let month1=month4;
        %let month2=month5;
        %let month3=month6;
    %end;
    %else %if &qtr=qtr3 %then %do;
        %let month1=month7;
        %let month2=month8;
        %let month3=month9;
    %end;
    %else %%if &qtr=qtr4 %then %do;
        %let month1=month10;
        %let month2=month11;
        %let month3=month12;
    %end;
    create table &qtr as
    select &month1.name, &month1.sales as m1sales, &month2.sales as m2sales,
            &month3.sales as m3sales, sum(m1sales, m2sales, m3sales) as 
qtrsales
        from &month1, &month2, &month3
        where &month1.name=&month2.name=&month3.name;
    select sum(m1sales) as m1total, sum(m2sales) as m2total, sum(m3sales) as 
m3total,
            sum(qtrsales) as qtrtotal
        from &qtr;
%mend ProcSql;
    %ProcSql;

I am getting all of the

I am getting this error:

ERROR: Function SUM requires a numeric expression as argument 1. ERROR: Function SUM requires a numeric expression as argument 2. ERROR: Function SUM requires a numeric expression as argument 3. ERROR: The following columns were not found in the contributing tables: m1sales, m2sales, m3sales.

ERROR: File WORK.QTR1.DATA does not exist.

Answer 1

If you want to reference a value derived in the current SELECT statement then you need to add the CALCULATED keyword to your query.

create table &qtr as
  select &month1.name
       , &month1.sales as m1sales
       , &month2.sales as m2sales
       , &month3.sales as m3sales
       , sum(calculated m1sales,calculated m2sales,calculated m3sales) as qtrsales
   from &month1, &month2, &month3
   where &month1.name=&month2.name
     and &month1.name=&month3.name
;

Answer 2

Get rid of multiple datasets as early as possible.

I'd just concatenate the data into a single dataset. Having multiple identical datasets for mutiple time periods (or other variables) is in my experience one of SAS's worst anti-patterns.

data sales;
 set month1 (in=m1) month2 (in=m2) month3 (in=m3) month4 (in=m4) month5 (in=m5)  month6 (in=m6);
 if m1 then month=1;
 if m2 then month=2;
 if m3 then month=3;
 if m4 then month=4;
 if m5 then month=5;
 if m6 then month=6;
 qtr = ceil(month/3);
run;

With the data in one dataset it's much easier to manipulate. You can easily aggregate it in SQL:

proc sql;
 create table monthly_sales as
 select qtr,
        month,
        sum(sales) as monthly_sales
 from sales
 group by month ;

 create table quarterly_sales as
 select month,
        qtr,
        monthly_sales,
        sum(monthly_sales) as quarterly_sales
 from monthly_sales
 group by qtr;
quit;

Or tabulate it:

proc tabulate data=sales;
 var sales;
 class month qtr;
 table qtr*(month all='total')*sales=''*sum='';
run;

Or transpose it:

proc sort data=sales; by name;
proc transpose data=sales out=sales_wide;
 by name;
 var sales;
 id month;
run;

Use macros to generate code, not for control-flow

If you have to use macros, try using a macro to generate code inside a data step instead of looping over multiple datasets. (Macros are supposed to be used to generate code, that's what they were designed for). They far too often get abused as a proxy for program control structures, which often leads to an un-maintainable mess).

Here I use a macro to generate the data step used to concatenate the months, where the number of months is a variable:

%macro myset(months);
 set %do i=1 %to &months; month&i (in=m&i) %end;  ;
 %do i=1 %to &months; 
    if m&i then month=&i;
 %end;
%mend;

data sales;
 %myset(months=6);
 qtr = ceil(month/3);
run;

If you use options mprint you can see that the generated code is the same as above.