SAS PROC GLM in a loop

Question

I tried to run PROC GLM in a loop, because I have many models (different combinations of dependent and independent variables), and it’s very time consuming to run them one by one. But log error indicates only one MODEL statement allowed in PROC GLM, so any solutions for this?

my code looks like as below

data old;
  input year A1 A2 A3 A4 B C D;
datalines;
2000    22  22  30  37  4   13  14
2000    37  29  31  38  6   16  12
2000    42  29  34  37  3   15  15
2000    28  28  27  35  10  13  15
2000    33  22  37  40  9   12  15
2000    22  29  26  40  3   11  15
2000    37  20  32  40  6   12  13
2001    44  22  33  35  7   20  12
2001    33  20  26  40  6   13  15
2001    32  30  37  35  1   12  13
2001    44  25  31  39  4   20  14
2001    25  30  37  38  4   20  10
2001    43  21  35  38  6   11  10
2001    25  23  34  37  5   17  11
2001    45  30  35  37  1   13  14
2001    48  24  36  39  2   13  15
2001    25  24  35  40  9   16  11
2002    38  26  33  35  2   14  10
2002    29  24  35  36  1   16  13
2002    34  28  32  35  9   16  11
2002    45  26  29  35  9   19  10
2002    26  22  38  35  1   14  12
2002    20  26  26  39  8   17  10
2002    33  20  35  37  9   18  12
;
run;

    %macro regression (in, YLIST,XLIST);
    %local NYLIST;
    %let NYLIST=%sysfunc(countw(&YLIST));

    ods tagsets.ExcelXP path='D:\REG' file='Regression.xls';

    Proc GLM data=&in; class year;    

        %do i=1 %to &NYLIST;
          Model %scan(&YLIST,&i)=%scan(&XLIST,1) %scan(&XLIST,2)/ solution;
          Model %scan(&YLIST,&i)=%scan(&XLIST,1) %scan(&XLIST,2)/ solution;
          Model %scan(&YLIST,&i)=%scan(&XLIST,1) %scan(&XLIST,2)/ solution;
        %end;

        %do i=2 %to &NYLIST;
          Model %scan(&YLIST,&i)=%scan(&XLIST,1) %scan(&XLIST,2) %scan(&XLIST,3)/ solution; 
          Model %scan(&YLIST,&i)=%scan(&XLIST,1) %scan(&XLIST,2) %scan(&XLIST,3)/ solution;
          Model %scan(&YLIST,&i)=%scan(&XLIST,1) %scan(&XLIST,2) %scan(&XLIST,3)/ solution;
        %end;

    run;

    ods tagsets.excelxp close;

    %mend regression;
    options mprint;
    %regression
    (in=old
    ,YLIST= A1 A2 A3 A4
    ,XLIST= B C D);

/*potential solutions*/ 

    %macro regression(data,y,x1,x2,x3);
    proc glm data=&data;
    class year;
    model &y=&x1 &x2 &x3/solution;
    run;
    %mend regression;


    %macro sql (mydataset,y,x1,x2,x3);
    proc sql noprint;

    select cats('%regression(&mydataset,',&y,',',&x1,',',&x2,',',&x3,')')  
    into :calllist separated by ' ' from &mydataset;
    quit;
    &calllist.;

    %mend sql;
    %sql 
    (mydataset=old
    ,y=A1 
    ,X1=B
    ,X2=C
    ,X3=D);

Joe Joe · Accepted Answer · 2013-10-16T20:21:57

You should do this in two steps. One is a macro that contains one instance of PROC GLM:

%macro regression(data,y,x1,x2,x3);
proc glm data=&data;
class year;
model &y &x1 &x2 &x3/solution;
run;
%mend regression;

And then call that macro from something else, either a macro with the looping elements, or better, from a dataset that contains your y/x1/x2/x3 as columns (one row per model statement) using call execute or proc sql select into methods. For example, with a data set modeldata containing the y/x values for your model:

proc sql noprint;
select cats('%regression(mydataset,',y,',',x1,',',x2,',',x3,')') into :calllist separated by ' ' from modeldata;
quit;
&calllist.;

Or similar.

SAS PROC GLM in a loop

1 Answers