rolling regression with macro

[katya]

I am desperately trying to run a rolling regression of a time series of a hedge fund return on a set of 6 equity and bond factors.I've just tried doing a macro, but still without success.Is there someone who can help me out? I attach the code I've written and the error log information.

data mylib1.proj;

set mylib1.myseries(keep= date Optimus MSCINordicLV MSCINordicSV SandPSwedenSG CreditSuisseHY EcapEuroCorp lag1Optimus);

run;

%macro rollingreg

(data=mylib1.proj ,

out_ds=mylib1.proj1 ,

model_equation= Optimus= MSCINordicLV MSCINordicSV SandPSwedenSG CreditSuisseHY EcapEuroCorp lag1Optimus / noint,

id= , date=date ,

start_date=1/10/7 ,

end_date=8/11/13 ,

freq=month, s=1, n=12,

regprint=noprint);

%* Start with empty output data sets;

proc datasets nolist;

delete _all_ds _outest_ds;

run;

* Prepare input data for by-id-date use;

proc sort data=&data;

by &id &date;

run;

%* Set the 'by-id' variable;

%let by_id= ; *blank default, no by variable;

%if %length(&id) > 0 %then %let by_id= by &id;

%* Determine date range variables;

%if %lowcase(%substr(&date,1,4))= year %then %let year_date=1;

%else %let year_date=0;

%let sdate1 = &start_date;

%let sdate2 = &end_date;

%* Make start and end date if missing;

%if &start_date = %str() | &end_date = %str() %then %do;

proc sql noprint;

create table _dx1 as

select min(&date) as min_date, max(&date) as max_date

from &data where not missing(&date);

select min_date into : min_date from _dx1;

select max_date into : max_date from _dx1;

quit;

%end;

%* SDATE1 and SDATE2 put in sas date number form (1/1/1960=0);

%if &sdate1 = %str() %then %do;

%let sdate1= &min_date;

%end;

%else %do;

%if (%index(&sdate1,%str(-)) > 1) | (%index(&sdate1,%str(/)) > 1)

%then %let sdate1= %sysfunc(inputn(&sdate1,mmddyy10.));

%else %if ( %length(&sdate1)=7 )

%then %let sdate1= %sysfunc(inputn(01&sdate1,date9.));

%else %if ( %length(&sdate1)=8 | %length(&sdate1)=9 )

%then %let sdate1= %sysfunc(inputn(&sdate1,date9.));

%else %if ( %length(&sdate1)=4 )

%then %let sdate1= %sysfunc(inputn(01JAN&sdate1,date9.));

%if &year_date=1 %then %let sdate1=%sysfunc(year(&sdate1));

%end;

%if &sdate2 = %str() %then %do;

%let sdate2= &max_date;

%end;

%else %do;

%if (%index(&sdate2,%str(-)) > 1) | (%index(&sdate2,%str(/)) > 1)

%then %let sdate2= %sysfunc(inputn(&sdate2,mmddyy10.));

%else %if ( %length(&sdate2)=7 ) %then %do;

%let sdate2= %sysfunc(inputn(01&sdate2,date9.));

%let sdate2= %sysfunc(intnx(month,&sdate2,0,end));

%end;

%else %if ( %length(&sdate2)=8 | %length(&sdate2)=9 )

%then %let sdate2= %sysfunc(inputn(&sdate2,date9.));

%else %if ( %length(&sdate2)=4 )

%then %let sdate2= %sysfunc(inputn(31DEC&sdate2,date9.));

%if &year_date=1 %then %let sdate2=%sysfunc(year(&sdate2));

%end;

%*Determine loop frequency parameters;

%if %eval(&n)= 0 %then %let n= &s;

%* if n blank use 1 period (=&s) assumption;

%if &year_date=1 %then %let freq=year;

%* year frequency case;

%put Date variable: &date year_date: &year_date;

%put Start and end dates: &start_date &end_date // &sdate1 &sdate2;

%if &year_date=0 %then

%put %sysfunc(putn(&sdate1,date9.)) %sysfunc(putn(&sdate2,date9.));

%put Freq: &freq s: &s n: &n;

%* Preliminary date setting for each iteration/loop;

%* First end date (idate2) is n periods after the start date;

%if &year_date=1 %then %let idate2= %eval(&sdate1+(&n-1));

%else %let idate2= %sysfunc(intnx(&freq,&sdate1,(&n-1),end));

%if &year_date=0 %then %let idate1= %sysfunc(intnx(&freq,&idate2,-&n+1,begin));

%else %let idate1= %eval(&idate2-&n+1);

%put First loop: &idate1 -- &idate2;

%put Loop through: &sdate2;

%if (&idate2 > &sdate2) %then %do;

%* Dates are not acceptable-- show problem, do not run loop;

%put PROBLEM-- end date for loop exceeds range : ( &idate2 > &sdate2 );

%end;

%else %do; *Dates are accepted-- run loops;

%let jj=0;

%do %while(&idate2 <= &sdate2);

%let jj=%eval(&jj+1);

%*Define loop start date (idate1) based on inherited end date (idate2);

%if &year_date=0 %then %do;

%let idate1= %sysfunc(intnx(&freq,&idate2,-&n+1,begin));

%let date1c= %sysfunc(putn(&idate1,date9.));

%let date2c= %sysfunc(putn(&idate2,date9.));

%end;

%if &year_date=1 %then %do;

%let idate1= %eval(&idate2-&n+1);

%let date1c= &idate1;

%let date2c= &idate2;

%end;

%let idate1= %sysfunc(max(&sdate1,&idate1));

%put Loop: &jj -- &date1c &date2c;

%put &jj -- &idate1 &idate2;

proc datasets nolist;

delete _outest_ds;

run;

%***** analysis code here -- for each loop;

%* noprint to just make output set;

%let noprint= noprint;

%if %upcase(®print) = yes | %upcase(®print) = print %then %let noprint= ;

proc reg data=&data

outest=_outest_ds edf

&noprint;

where &date between &idate1 and &idate2;

model &model_equation;

&by_id;

run;

%* Add loop date range variables to output set;

data _outest_ds;

set _outest_ds;

regobs= _p_ + _edf_; %* number of observations in regression;

date1= &idate1;

date2= &idate2;

%if &year_date=0 %then format date1 date2 date9.;

run;

%* Append results;

proc datasets nolist;

append base=_all_ds data=_outest_ds;

run;

%* Set next loop end date;

%if &year_date=0 %then %let idate2= %sysfunc(intnx(&freq,&idate2,&s,end));

%else %if &year_date=1 %then %let idate2= %eval(&idate2+&s);

%end; *% end of loop;

%* Save outout set to desired location;

data &out_ds;

set _all_ds;

run;

proc sort data=&out_ds;

by &id date2;

run;

%end; %* end for date check pass section;

%mend;

proc print data=mylib1.proj1(rename= (MSCINordicLV=beta1 MSCINordicSV=beta2 SandPSwedenSG=beta3 CreditSuiseeHY=beta4 EcapEurpoCorp=beta5 lag1Optimus=beta6));

by date;

id date1 date2;

var rmse beta1 beta2 beta3 beta4 beta5 beta6 rsq regobs;

run;

LOG error information:

160  data mylib1.proj;

161  set mylib1.myseries(keep= date Optimus MSCINordicLV MSCINordicSV SandPSwedenSG CreditSuisseHY

161! EcapEuroCorp lag1Optimus);

162  run;

NOTE: There were 1595 observations read from the data set MYLIB1.MYSERIES.

NOTE: The data set MYLIB1.PROJ has 1595 observations and 8 variables.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

163  %macro rollingreg

164  (data=mylib1.proj ,

165  out_ds=mylib1.proj1 ,

166  model_equation= Optimus= MSCINordicLV MSCINordicSV SandPSwedenSG CreditSuisseHY EcapEuroCorp

166! lag1Optimus / noint,

167  id= , date=date ,

168  start_date=1/10/7 ,

169  end_date=8/11/13 ,

170  freq=month, s=1, n=12,

171  regprint=noprint);

172  %* Start with empty output data sets;

173  proc datasets nolist;

174  delete _all_ds _outest_ds;

175  run;

176  * Prepare input data for by-id-date use;

177  proc sort data=&data;

178  by &id &date;

179  run;

180  %* Set the 'by-id' variable;

181  %let by_id= ; *blank default, no by variable;

182  %if %length(&id) > 0 %then %let by_id= by &id;

183  %* Determine date range variables;

184  %if %lowcase(%substr(&date,1,4))= year %then %let year_date=1;

185  %else %let year_date=0;

186  %let sdate1 = &start_date;

187  %let sdate2 = &end_date;

188  %* Make start and end date if missing;

189  %if &start_date = %str() | &end_date = %str() %then %do;

190  proc sql noprint;

191  create table _dx1 as

192  select min(&date) as min_date, max(&date) as max_date

193  from &data where not missing(&date);

194  select min_date into : min_date from _dx1;

195  select max_date into : max_date from _dx1;

196  quit;

197  %end;

198  %* SDATE1 and SDATE2 put in sas date number form (1/1/1960=0);

199  %if &sdate1 = %str() %then %do;

200  %let sdate1= &min_date;

201  %end;

202  %else %do;

203  %if (%index(&sdate1,%str(-)) > 1) | (%index(&sdate1,%str(/)) > 1)

204  %then %let sdate1= %sysfunc(inputn(&sdate1,mmddyy10.));

205  %else %if ( %length(&sdate1)=7 )

206  %then %let sdate1= %sysfunc(inputn(01&sdate1,date9.));

207  %else %if ( %length(&sdate1)=8 | %length(&sdate1)=9 )

208  %then %let sdate1= %sysfunc(inputn(&sdate1,date9.));

209  %else %if ( %length(&sdate1)=4 )

210  %then %let sdate1= %sysfunc(inputn(01JAN&sdate1,date9.));

211  %if &year_date=1 %then %let sdate1=%sysfunc(year(&sdate1));

212  %end;

213  %if &sdate2 = %str() %then %do;

214  %let sdate2= &max_date;

215  %end;

216  %else %do;

217  %if (%index(&sdate2,%str(-)) > 1) | (%index(&sdate2,%str(/)) > 1)

218  %then %let sdate2= %sysfunc(inputn(&sdate2,mmddyy10.));

219  %else %if ( %length(&sdate2)=7 ) %then %do;

220  %let sdate2= %sysfunc(inputn(01&sdate2,date9.));

221  %let sdate2= %sysfunc(intnx(month,&sdate2,0,end));

222  %end;

223  %else %if ( %length(&sdate2)=8 | %length(&sdate2)=9 )

224  %then %let sdate2= %sysfunc(inputn(&sdate2,date9.));

225  %else %if ( %length(&sdate2)=4 )

226  %then %let sdate2= %sysfunc(inputn(31DEC&sdate2,date9.));

227  %if &year_date=1 %then %let sdate2=%sysfunc(year(&sdate2));

228  %end;

229  %*Determine loop frequency parameters;

230  %if %eval(&n)= 0 %then %let n= &s;

231  %* if n blank use 1 period (=&s) assumption;

232  %if &year_date=1 %then %let freq=year;

233  %* year frequency case;

234  %put Date variable: &date year_date: &year_date;

235  %put Start and end dates: &start_date &end_date // &sdate1 &sdate2;

236  %if &year_date=0 %then

237  %put %sysfunc(putn(&sdate1,date9.)) %sysfunc(putn(&sdate2,date9.));

238  %put Freq: &freq s: &s n: &n;

239  %* Preliminary date setting for each iteration/loop;

240  %* First end date (idate2) is n periods after the start date;

241  %if &year_date=1 %then %let idate2= %eval(&sdate1+(&n-1));

242  %else %let idate2= %sysfunc(intnx(&freq,&sdate1,(&n-1),end));

243  %if &year_date=0 %then %let idate1= %sysfunc(intnx(&freq,&idate2,-&n+1,begin));

244  %else %let idate1= %eval(&idate2-&n+1);

245  %put First loop: &idate1 -- &idate2;

246  %put Loop through: &sdate2;

247  %if (&idate2 > &sdate2) %then %do;

248  %* Dates are not acceptable-- show problem, do not run loop;

249  %put PROBLEM-- end date for loop exceeds range : ( &idate2 > &sdate2 );

250  %end;

251  %else %do; *Dates are accepted-- run loops;

252  %let jj=0;

253  %do %while(&idate2 <= &sdate2);

254  %let jj=%eval(&jj+1);

255  %*Define loop start date (idate1) based on inherited end date (idate2);

256  %if &year_date=0 %then %do;

257  %let idate1= %sysfunc(intnx(&freq,&idate2,-&n+1,begin));

258  %let date1c= %sysfunc(putn(&idate1,date9.));

259  %let date2c= %sysfunc(putn(&idate2,date9.));

260  %end;

261  %if &year_date=1 %then %do;

262  %let idate1= %eval(&idate2-&n+1);

263  %let date1c= &idate1;

264  %let date2c= &idate2;

265  %end;

266  %let idate1= %sysfunc(max(&sdate1,&idate1));

267  %put Loop: &jj -- &date1c &date2c;

268  %put &jj -- &idate1 &idate2;

269  proc datasets nolist;

270  delete _outest_ds;

271  run;

272  %***** analysis code here -- for each loop;

273  %* noprint to just make output set;

274  %let noprint= noprint;

275  %if %upcase(®print) = yes | %upcase(®print) = print %then %let noprint= ;

276  proc reg data=&data

277  outest=_outest_ds edf

278  &noprint;

279  where &date between &idate1 and &idate2;

280  model &model_equation;

281  &by_id;

282  run;

283  %* Add loop date range variables to output set;

284  data _outest_ds;

285  set _outest_ds;

286  regobs= _p_ + _edf_; %* number of observations in regression;

287  date1= &idate1;

288  date2= &idate2;

289  %if &year_date=0 %then format date1 date2 date9.;

290  run;

291  %* Append results;

292  proc datasets nolist;

293  append base=_all_ds data=_outest_ds;

294  run;

295  %* Set next loop end date;

296  %if &year_date=0 %then %let idate2= %sysfunc(intnx(&freq,&idate2,&s,end));

297  %else %if &year_date=1 %then %let idate2= %eval(&idate2+&s);

298  %end; *% end of loop;

299  %* Save outout set to desired location;

300  data &out_ds;

301  set _all_ds;

302  run;

303  proc sort data=&out_ds;

304  by &id date2;

305  run;

306  %end; %* end for date check pass section;

307  %mend;

308  proc print data=mylib1.proj1(rename= (MSCINordicLV=beta1 MSCINordicSV=beta2 SandPSwedenSG=beta3

308! CreditSuiseeHY=beta4 EcapEurpoCorp=beta5 lag1Optimus=beta6));

ERROR: File MYLIB1.PROJ1.DATA does not exist.

309  by date;

310  id date1 date2;

311  var rmse beta1 beta2 beta3 beta4 beta5 beta6 rsq regobs;

312  run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

[PaigeMiller]

It appears you are missing the %rollingreg statement to execute the macro

[Reeza]

Debugging a macro is not fun, but here are some steps that can help:

1. Turn on Options SYMBOLGEN MPRINT;

This will allow you to see what's going on in your code.

2. Add selective %PUT/PUT statements as required to see variables values throughout the process.

3. Move the %mend up to your first "point" in the macro where you can safely run it and test it in iterations. Get one step working and then work on the next step.

Additionally, when first writing a macro it helps to first have working code that is non-macrotized and start from there. Always test changes in as small a batch as you can.