Proc Sql Do Loop in SAS

Question

For an assignment I am asked to create a do loop in Proc Sql statement. My program is not recognizing the m1sales and m2sales. Here are the data sets and the macros that I had to create. The First macro is to set allow people to set the qtr to a number. The second macro set Months One, Two, and Three depending on the months. The first proc sql is doing what I want. The second is not when I add in the do statement. I was given the additional statement, "The %do loop isn't 'within' a create table command. The %do loop is replaced with a sequence of text that follows the create table command; that text may be though of as 'within' the create table statement." Can anyone tell me how to create that do loop correctly?

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 symbolgen;
%Macro quarter(quarter);
%Global qtr;
%Let qtr = &quarter;
%Mend quarter;
%quarter (1);

options mprint symbolgen;
%Macro Month(day);
%Global One;
%Global Two;
%Global Three;
%if &qtr = %eval(1) %then %do; 
    %Let One = 1;
    %Let Two = 2;
    %Let Three = 3;
    %end;
%Else %if &qtr = %eval(2) %then %do; 
    %Let One = 4;
    %Let Two = 5;
    %Let Three = 6;
    %end;
%Else %if &qtr = %eval(3) %then %do; 
    %Let One = 7;
    %Let Two = 8;
    %Let Three = 9;
    %end;
%Else %if &qtr = %eval(4) %then %do; 
    %Let One = 10;
    %Let Two = 11;
    %Let Three = 12;
    %end;   
%Mend Month;
%Month(&qtr);

Correct code:

%Macro qtrearn(x);
proc sql;
create table qtr&x as
select Month&One..name, month&One..sales as m&One.sales, month&Two..sales as m&Two.sales,
  month&Three..sales as m&Three.sales, sum(month&One..sales, month&Two..sales, month&Three..sales) as qtr&x.sales
  from month&One, month&Two, month&Three
  where month&One..name=month&Two..name=month&Three..name;
select sum(m&One.sales) as m&One.total, sum(m&Two.sales) as m&Two.total, sum(m&Three.sales) as m&Three.total,
  sum(qtr&x.sales) as qtr&x.total
  from qtr&x;
%Mend qtrearn;
%qtrearn(&qtr);

Code that is not working with the do loop. I need to insert the do loop for an assignment.

options mprint symbolgen;
%Macro qtrearn(x);
proc sql;
%do i = &One %to &Three;
create table qtr&x as
select Month&i..name, month&&i..sales as m&&i.sales,
  sum(month&One..sales, month&Two..sales, Month&Three..sales) as qtr&x.sales
  from month&One, month&Two, month&Three
  where month&One..name=month&Two..name=month&Three..name;
%end;
select sum(m&One.sales) as m&One.total, sum(m&Two.sales) as m&Two.total, sum(m&Three.sales) as m&Three.total,
  sum(qtr&x.sales) as qtr&x.total
  from qtr&x;

%Mend qtrearn;
%qtrearn(&qtr);

score 1 · Answer 1 · answered Jul 12 '17 at 20:52

I was able to solve it. In case someone else wants it. Here is the answer:

%Macro qtrearn(x);
proc sql;
create table qtr&x as
select Month&One..name, 
  %do i = &One %to &Three;
  month&&i..sales as m&&i.sales,
  %end;
  sum(month&One..sales, month&Two..sales, month&Three..sales) as qtr&x.sales
  from month&One, month&Two, month&Three
  where month&One..name=month&Two..name=month&Three..name;
create table totals_qtr&x as
select   %do i = &One %to &Three;
  sum(m&&i.sales) as m&&i.total,
  %end; 
  sum(qtr&x.sales) as qtr&x.total
  from qtr&x;
proc print data=work.qtr&x;
run;
proc print data=work.totals_qtr&x;
run;
%Mend qtrearn;
%qtrearn(&qtr);

Proc Sql Do Loop in SAS

1 Answers1