Create Loops and variables dynamically

Hi, I am trying to build a macro which is able to test different models and save the results in a table so I can easily compare them. This will save a lot of time so I think it can be useful for many users I have my variables in one table and they are organised by groups (two variables in the same group shouldn't enter the model at the same time). The problem is that the number of groups is variable so I would like to adapt the number of loops depending on the groups in the table. Is there any way to create loops automatically? %macro reg_loop; %do i=1 %to 2; %do j=1 %to 3; %do k=1 %to 3; proc reg data= input OUTEST=coefficients all; model dep_var = group1var&i group2var&j group3var&k /; output out= model&i&j&k; run; %end; %end; %end; %mend; Thanks in advance for your help!

On Nov 17, 8:49=A0am, Matt Roberts <mattrobertslond...@gmail.com> wrote: > Hi, > I am trying to build a macro which is able to test different models > and save the results in a table so > I can easily compare =A0them. > This will save a lot of time so I think it can be useful for many > users > I have my variables in one table and they are organised by groups (two > variables in the same group shouldn't enter the model at the same > time). > The problem is that the number of groups is variable so I would like > to adapt the number of loops depending on the groups in the table. > Is there any way to create loops automatically? > > %macro reg_loop; > %do i=3D1 %to 2; > %do j=3D1 %to 3; > %do k=3D1 %to 3; > > proc reg data=3D input OUTEST=3Dcoefficients all; > model dep_var =3D group1var&i group2var&j group3var&k > /; > output out=3D model&i&j&k; > run; > > %end; > %end; > %end; > > %mend; > > Thanks in advance for your help! Not every elegant, but will generate the macro code needed: data xx; array indvar group1var1 group1var2 group2var1 group2var2 group2var3 group3var1 group3var2 group4var1 group4var2 group4var3 group4var4; run; proc sql; create table driver as select scan(name,1,'v') as groupnum, max(input(scan(name,3,'r'),best.)) as numvar from dictionary.columns where libname=3D'WORK' and memname=3D'XX' group by groupnum ; proc transpose data=3Ddriver out=3Dtdriver (drop=3D_name_); id groupnum; run; filename mcro temp; data _null_; set tdriver; array group(*) _numeric_; * file mcro; put '%macro reg_loop;'; do i=3D1 to dim(group); put @i '%do lp' i '=3D1 %to ' group(i) ';'; end; put @i 'proc reg data=3D input OUTEST=3Dcoefficients all;'; put @i 'model dep_var =3D '; do j=3D1 to dim(group); put 'group' j +(-1) 'var' '&lp' j @; end; put ';'; put @i 'output out=3D model' @; do k=3D1 to dim(group); put '&lp' k +(-1) @; end; put ';'; put @i 'run;'; do l=3D1 to dim(group); put '%end;'; end; put '%mend reg_loop;'; run; %*include mcro; From log: %macro reg_loop; %do lp1 =3D1 %to 2 ; %do lp2 =3D1 %to 3 ; %do lp3 =3D1 %to 2 ; %do lp4 =3D1 %to 4 ; proc reg data=3D input OUTEST=3Dcoefficients all; model dep_var =3D group1var&lp1 group2var&lp2 group3var&lp3 group4var&lp4 ; output out=3D model&lp1&lp2&lp3&lp4; run; %end; %end; %end; %end; %mend reg_loop; NOTE: There were 1 observations read from the data set WORK.TDRIVER. Uncomment the 'file mcro' statement, and %include, then the macro will be included and compiled. HTH Ya

Hi "I have my variables in one table and they are organised by groups " It would help if you could provide a sample data set for this. Would this variables be in the ds "input" or is this a additional data set? Thanks Patrick

Hi, Thank you your replies.. The code is very useful and now I have a starting point which it is great.. About the datasets: I have two source tables. One with the data which will be included in the model and other table with the groups. For each model I need to enter one variable of each group and two variables of the same group can't enter together. My idea is to use macro variables to store the relationship group- variable and use those as loops control variables. But I am more than happy to try different things in order to improve the efficiency of the macro.. Thanks again for your ideas. ** Data exaple data data; input postcode $. date DATE9. var1 var2 var3 var4 var5 var6; datalines; NW67NA 01MAY2008 4 1 3 10 1 4 NW67NA 01JUN2008 8 2 8 12 2 4 NW77NB 01MAY2008 9 3 8 11 3 3 NW77NB 01JUN2008 3 1 9 13 3 3 NW87NC 01MAY2008 12 4 9 7 1 3 NW87NC 01JUN2008 6 2 9 9 7 3 ; run; ** Group information example data groups; input group $ varname $ varnumber; datalines; group1 value 1 group1 units 2 group2 weight 3 group2 height 4 group2 type 5 group3 price 6 ; run; On Nov 18, 9:05=A0am, Patrick <patrick.mat...@gmx.ch> wrote: > Hi > > "I have my variables in one table and they are organised by groups " > > It would help if you could provide a sample data set for this. Would > this variables be in the ds "input" or is this a additional data set? > > Thanks > Patrick

Hi, Thank you for your replies.. The code is very useful and now I have a starting point which it is great.. About the datasets: I have two source tables. One with the data which will be included in the model and other table with the groups. For each model I need to enter one variable of each group and two variables of the same group can't enter together. My idea is to use macro variables to store the relationship group- variable and use those as loops control variables. But I am more than happy to try different things in order to improve the efficiency of the macro.. Thanks again for your ideas. ** Data exaple data data; input postcode $. date DATE9. var1 var2 var3 var4 var5 var6; datalines; NW67NA 01MAY2008 4 1 3 10 1 4 NW67NA 01JUN2008 8 2 8 12 2 4 NW77NB 01MAY2008 9 3 8 11 3 3 NW77NB 01JUN2008 3 1 9 13 3 3 NW87NC 01MAY2008 12 4 9 7 1 3 NW87NC 01JUN2008 6 2 9 9 7 3 ; run; ** Group information example data groups; input group $ varname $ varnumber; datalines; group1 value 1 group1 units 2 group2 weight 3 group2 height 4 group2 type 5 group3 price 6 ; run; On Nov 18, 9:05=A0am, Patrick <patrick.mat...@gmx.ch> wrote: > Hi > > "I have my variables in one table and they are organised by groups " > > It would help if you could provide a sample data set for this. Would > this variables be in the ds "input" or is this a additional data set? > > Thanks > Patrick

Hi, I have adapted your code to my data and it is working. The only problems that I have now are the label of the model ( I need to store all the models in one table in order to do be able to analyse the results) and the output. I tried several things for the label of the model but I can't find a way to have a counter (in this case &n) which is updated dynamically within the macro. About the output, I would like to keep all the models together. Is there anyway to force SAS to append new records to the output tables instead of overwriting them?? Thanks a lot. filename mcro temp; data _null_; set tdriver; array group(*) _numeric_; file mcro; put '%macro reg_loop;'; %let n=3D1; put @i 'ods output acovest=3Da_est corr=3Da_corr dwstatistic=3Da_dw ParameterEstimates=3Da_parms cookd =3D a_cookd;' ; put @i ' proc reg data=3Dfinal_transformed2 OUTEST=3Dcoefficients all;'; do i=3D1 to dim(group); put @i '%do lp' i '=3D1 %to ' group(i) ';'; end; put @i 'model&n' ':' @; put ' model dep_var =3D '; do j=3D1 to dim(group) ; put '&&g' j +(-1) 'v&lp' j @; end; put ';'; mdl=3Dmdl +1; do l=3D1 to dim(group); put '%end;'; end; put @i 'run;'; put @i 'quit;'; put '%mend reg_loop;'; run; %include mcro; %reg_loop; On Nov 17, 7:00=A0pm, Ya <huang8...@gmail.com> wrote: > On Nov 17, 8:49=A0am, Matt Roberts <mattrobertslond...@gmail.com> wrote: > > > > > > > Hi, > > I am trying to build a macro which is able to test different models > > and save the results in a table so > > I can easily compare =A0them. > > This will save a lot of time so I think it can be useful for many > > users > > I have my variables in one table and they are organised by groups (two > > variables in the same group shouldn't enter the model at the same > > time). > > The problem is that the number of groups is variable so I would like > > to adapt the number of loops depending on the groups in the table. > > Is there any way to create loops automatically? > > > %macro reg_loop; > > %do i=3D1 %to 2; > > %do j=3D1 %to 3; > > %do k=3D1 %to 3; > > > proc reg data=3D input OUTEST=3Dcoefficients all; > > model dep_var =3D group1var&i group2var&j group3var&k > > /; > > output out=3D model&i&j&k; > > run; > > > %end; > > %end; > > %end; > > > %mend; > > > Thanks in advance for your help! > > Not every elegant, but will generate the macro code needed: > > data xx; > array indvar group1var1 group1var2 group2var1 group2var2 group2var3 > =A0 =A0 =A0 =A0 =A0 =A0 =A0group3var1 group3var2 group4var1 group4var2 gr= oup4var3 > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0group4var4; > run; > > proc sql; > create table driver as > select scan(name,1,'v') as groupnum, > =A0 =A0 =A0 =A0max(input(scan(name,3,'r'),best.)) as numvar > from dictionary.columns > where libname=3D'WORK' and memname=3D'XX' > group by groupnum > ; > > proc transpose data=3Ddriver out=3Dtdriver (drop=3D_name_); > id groupnum; > run; > > filename mcro temp; > > data _null_; > =A0set tdriver; > =A0array group(*) _numeric_; > * file mcro; > =A0put '%macro reg_loop;'; > =A0do i=3D1 to dim(group); > =A0 put @i '%do lp' i =A0'=3D1 %to ' group(i) ';'; > =A0end; > =A0put @i 'proc reg data=3D input OUTEST=3Dcoefficients all;'; > =A0put @i 'model dep_var =3D '; > =A0do j=3D1 to dim(group); > =A0 put 'group' j +(-1) 'var' '&lp' j @; > =A0end; > =A0put ';'; > =A0put @i 'output out=3D model' @; > =A0do k=3D1 to dim(group); > =A0put '&lp' k +(-1) @; > =A0end; > =A0put ';'; > =A0put @i 'run;'; > =A0do l=3D1 to dim(group); > =A0 put '%end;'; > =A0end; > =A0put '%mend reg_loop;'; > run; > > %*include mcro; > > From log: > > %macro reg_loop; > %do lp1 =3D1 %to 2 ; > =A0%do lp2 =3D1 %to 3 ; > =A0 %do lp3 =3D1 %to 2 ; > =A0 =A0%do lp4 =3D1 %to 4 ; > =A0 =A0 proc reg data=3D input OUTEST=3Dcoefficients all; > =A0 =A0 model dep_var =3D > group1var&lp1 group2var&lp2 group3var&lp3 group4var&lp4 ; > =A0 =A0 output out=3D model&lp1&lp2&lp3&lp4; > =A0 =A0 run; > %end; > %end; > %end; > %end; > %mend reg_loop; > NOTE: There were 1 observations read from the data set WORK.TDRIVER. > > Uncomment the 'file mcro' statement, and %include, then the macro will > be > included and compiled. > > HTH > > Ya- Hide quoted text - > > - Show quoted text -

On Nov 19, 11:29=A0am, Matt Roberts <mattrobertslond...@gmail.com> wrote: > Hi, > I have adapted your code to my data and it is working. The only > problems that I have now are the label of the model ( I need to > store all the models in one table in order to do be able to analyse > the results) and the output. > I tried several things for the label of the model but I can't find a > way to have a counter (in this case &n) which is updated dynamically > within the macro. > About the output, I would like to keep all the models together. Is > there anyway to force SAS to append new records to the output tables > instead of overwriting them?? > Thanks a lot. > > filename mcro temp; > > data _null_; > =A0set tdriver; > =A0 array group(*) _numeric_; > file mcro; > =A0put '%macro reg_loop;'; > =A0 %let n=3D1; > =A0 put @i 'ods output acovest=3Da_est =A0corr=3Da_corr dwstatistic=3Da_d= w > ParameterEstimates=3Da_parms cookd =3D a_cookd;' ; > =A0 put @i ' proc reg data=3Dfinal_transformed2 =A0OUTEST=3Dcoefficients > all;'; > =A0 do i=3D1 to dim(group); > =A0 put @i '%do lp' i =A0'=3D1 %to ' group(i) ';'; > =A0 end; > =A0 =A0put @i 'model&n' =A0 ':' @; > =A0 =A0put ' model dep_var =3D '; > =A0 =A0 do j=3D1 to dim(group) ; > =A0 =A0 =A0 =A0 put '&&g' j +(-1) 'v&lp' j @; > =A0 =A0 =A0 =A0 end; > =A0put ';'; > =A0mdl=3Dmdl +1; > =A0do l=3D1 to dim(group); > =A0 put '%end;'; > =A0 end; > =A0 put @i 'run;'; > =A0 =A0put @i 'quit;'; > =A0put '%mend reg_loop;'; > run; > > %include mcro; > > %reg_loop; > > On Nov 17, 7:00=A0pm, Ya <huang8...@gmail.com> wrote: > > > > > On Nov 17, 8:49=A0am, Matt Roberts <mattrobertslond...@gmail.com> wrote= : > > > > Hi, > > > I am trying to build a macro which is able to test different models > > > and save the results in a table so > > > I can easily compare =A0them. > > > This will save a lot of time so I think it can be useful for many > > > users > > > I have my variables in one table and they are organised by groups (tw= o > > > variables in the same group shouldn't enter the model at the same > > > time). > > > The problem is that the number of groups is variable so I would like > > > to adapt the number of loops depending on the groups in the table. > > > Is there any way to create loops automatically? > > > > %macro reg_loop; > > > %do i=3D1 %to 2; > > > %do j=3D1 %to 3; > > > %do k=3D1 %to 3; > > > > proc reg data=3D input OUTEST=3Dcoefficients all; > > > model dep_var =3D group1var&i group2var&j group3var&k > > > /; > > > output out=3D model&i&j&k; > > > run; > > > > %end; > > > %end; > > > %end; > > > > %mend; > > > > Thanks in advance for your help! > > > Not every elegant, but will generate the macro code needed: > > > data xx; > > array indvar group1var1 group1var2 group2var1 group2var2 group2var3 > > =A0 =A0 =A0 =A0 =A0 =A0 =A0group3var1 group3var2 group4var1 group4var2 = group4var3 > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0group4var4; > > run; > > > proc sql; > > create table driver as > > select scan(name,1,'v') as groupnum, > > =A0 =A0 =A0 =A0max(input(scan(name,3,'r'),best.)) as numvar > > from dictionary.columns > > where libname=3D'WORK' and memname=3D'XX' > > group by groupnum > > ; > > > proc transpose data=3Ddriver out=3Dtdriver (drop=3D_name_); > > id groupnum; > > run; > > > filename mcro temp; > > > data _null_; > > =A0set tdriver; > > =A0array group(*) _numeric_; > > * file mcro; > > =A0put '%macro reg_loop;'; > > =A0do i=3D1 to dim(group); > > =A0 put @i '%do lp' i =A0'=3D1 %to ' group(i) ';'; > > =A0end; > > =A0put @i 'proc reg data=3D input OUTEST=3Dcoefficients all;'; > > =A0put @i 'model dep_var =3D '; > > =A0do j=3D1 to dim(group); > > =A0 put 'group' j +(-1) 'var' '&lp' j @; > > =A0end; > > =A0put ';'; > > =A0put @i 'output out=3D model' @; > > =A0do k=3D1 to dim(group); > > =A0put '&lp' k +(-1) @; > > =A0end; > > =A0put ';'; > > =A0put @i 'run;'; > > =A0do l=3D1 to dim(group); > > =A0 put '%end;'; > > =A0end; > > =A0put '%mend reg_loop;'; > > run; > > > %*include mcro; > > > From log: > > > %macro reg_loop; > > %do lp1 =3D1 %to 2 ; > > =A0%do lp2 =3D1 %to 3 ; > > =A0 %do lp3 =3D1 %to 2 ; > > =A0 =A0%do lp4 =3D1 %to 4 ; > > =A0 =A0 proc reg data=3D input OUTEST=3Dcoefficients all; > > =A0 =A0 model dep_var =3D > > group1var&lp1 group2var&lp2 group3var&lp3 group4var&lp4 ; > > =A0 =A0 output out=3D model&lp1&lp2&lp3&lp4; > > =A0 =A0 run; > > %end; > > %end; > > %end; > > %end; > > %mend reg_loop; > > NOTE: There were 1 observations read from the data set WORK.TDRIVER. > > > Uncomment the 'file mcro' statement, and %include, then the macro will > > be > > included and compiled. > > > HTH > > > Ya- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text - PROC REG has lots of options for variable selection but I could see no way to get the models you want other than using code gen techniques. PROC SUMMARY can easily output a data with suitable properties using the TYPES statement. You can use data GROUPS to gen that statement too. data data; input postcode $ date :DATE. value units weight height type price; y =3D rannor(1234)*4 + 10; format date date9.; datalines; NW67NA 01MAY2008 4 1 3 10 1 4 NW67NA 01JUN2008 8 2 8 12 2 4 NW77NB 01MAY2008 9 3 8 11 3 3 NW77NB 01JUN2008 3 1 9 13 3 3 NW87NC 01MAY2008 12 4 9 7 1 3 NW87NC 01JUN2008 6 2 9 9 7 3 ;;;; run; data groups; input group $ varname $ varnumber; datalines; group1 value 1 group1 units 2 group2 weight 3 group2 height 4 group2 type 5 group3 price 6 ;;;; run; filename FT32F001 temp; data _null_; file FT32F001; put +3 'Types '; do until(eof); put +6 '(' @; do until(last.group); set groups end=3Deof; by group; put varname @; end; put ')' @; if not eof then put ' * '; end; put ';'; stop; run; proc summary missing chartype data=3Ddata(obs=3D1); class value--price; *types (value units)*(weight height type)*price; %inc FT32F001 / source2; output out=3Dmodels; run; proc print; run; data _null_; file FT32F001; set models; array v[*] value--price; length rhs $200; do i =3D 1 to length(_type_); if substr(_type_,i,1) eq '1' then rhs =3D catx(' ',rhs,vname(v[i])); end; put +3 'model' _n_ :2.-l +(-1) ': model y=3D' rhs ';'; run; proc reg data=3Ddata; %inc FT32F001 / source2; run; quit;