rolling regression with macro

katya — Wed, 09 Apr 2014 11:54:58 GMT

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

topic Re: rolling regression with macro in SAS Procedures

rolling regression with macro

Re: rolling regression with macro

Re: rolling regression with macro