BookmarkSubscribeRSS Feed
Amalik
Calcite | Level 5

Hi,

 

Can anyone guide me on how to create a rolling window regression and create a start and end date from the date variable with the window of 36 months. I have attached the data for reference?

 

Regards,

Anum

3 REPLIES 3
Ksharp
Super User
Skeleton code like:

data have;
input start date9.;
end=intnx('month',start,36,'s');
cards;
01jan2016
.......
;
run;

%macro xx(start=,end=);
 proc reg data=x(where=(date between &start and &end));
..........
%mend;

data _null_;
 set have;
 call execute('%xx(start='||start||',end='||end||')');
run;
Amalik
Calcite | Level 5

Hi,

 

Can you please tell me where I am making mistake as the following code isn't working,

 

proc import datafile="C:\Users\amalik\Desktop\STYLEDATA1.csv"
out=return
dbms=csv
replace;
getnames=yes;
run;


%macro rollingreg
(data= work.return,
out_ds= work.returnout ,
model_equation= AMP_AIT = US_Small_Val US_Small_Gr US_Large_Gr US_Large_Val /noint
,id=permno , date=date ,
start_date=11/01/2007 ,
end_date=09/01/2016 ,
freq=month, s=1, n=36,
regprint=yes);
* 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= permno ; *blank default, no by variable;
%if %length(&id) > 0 %then %let by_id= by &id;
%* Determine date range variables;
%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;

Ksharp
Super User
Sorry. Your code is way too long.  I have no time to go through all of these.
I suggest you post a brand new session. Let others see your question.

I found a problem in your code:
model_equation= AMP_AIT = US_Small_Val US_Small_Gr US_Large_Gr US_Large_Val /noint

is not right which contain '=' in macro variable.
should something like:
model_equation=%str( AMP_AIT = US_Small_Val US_Small_Gr US_Large_Gr US_Large_Val /noint  )

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1958 views
  • 2 likes
  • 2 in conversation