Rolling ERC regression

[vl12]

I need to run CAR=UE for every prior 8 quarters and output R^2 and coefficients. Can anyone help?

[vl12]

tried the data2datastep macro to post my dataset but did not work. I generated a zip file that can't be openned.

[Reeza]

If you have the code for the regression, we can help make it into a macro. What issues are you having with the data2datastep macro? Did you download the zip and run the programs? 

---

@vl12 wrote:

I need to run CAR=UE for every prior 8 quarters and output R^2 and coefficients. Can anyone help?

---

[vl12]

I just have the simple regression code. However, this is not what. I would need the regression run on prior 8 observation for current observation. HOpe I explained clearly. thanks!

data erc;
	set data.erctest1;

proc sort data=erc nodupkey;
by gvkey;

proc reg data=erc outest=cfsde adjrsq noprint;

	model  car= ue;
	by gvkey;

output out=varone r=residone;

[vl12]

Thank you for your reply.

I downloaded the zip file, run it, got a file which however is zipped and can't be opened. are there another way to post a sample of dataset?

[Reeza]

I updated the instructions and simplified them here. 

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

In addition to sample data, you'll also need to provide your regression code. 

---

@vl12 wrote:

Thank you for your reply.

I downloaded the zip file, run it, got a file which however is zipped and can't be opened. are there another way to post a sample of dataset?

---

[vl12]

Thank you! Now i can copy and paste my sample data.

data WORK.ERCTEST;

infile datalines dsd truncover;

input gvkey:$6. datadate:YYMMDDN8. ue:32. car:32.;

format datadate YYMMDDN8.;

label gvkey="Global Company Key" datadate="Data Date";

datalines;

001690 20070331 . .

001690 20070630 0.0004525206 0.4670161755

001690 20070930 0.0006423839 0.2271965327

001690 20071231 0.0038899365 0.0535349058

001690 20080331 -0.004237645 -0.04493543

001690 20080630 0.0001820518 0.1184123378

001690 20080930 0.0006338696 -0.242361881

001690 20081231 0.0147242809 -0.06718815

001690 20090331 -0.006772771 0.4468636412

001690 20090630 0.0016303555 0.0926635129

001690 20090930 0.0042211539 0.0569200846

001690 20091231 0.0044297225 0.0694110274

001690 20100331 -0.001422126 0.1062908265

001690 20100630 0.0007790463 0.1138357995

001690 20100930 0.0040591522 0.1280910542

001690 20101231 0.0057087261 -0.024709269

001690 20110331 -0.000052753 -0.053340722

001690 20110630 0.0042457609 0.1448117306

001690 20110930 -0.001933107 0.2521745565

001690 20111231 0.0170601425 -0.080865297

001690 20120331 -0.002572389 0.2810152156

001690 20120630 -0.005111778 0.0876576022

001690 20120930 -0.000959221 -0.032848439

001690 20121231 0.0097159076 -0.22854538

001690 20130331 -0.008485084 -0.223406274

001690 20130630 -0.007348195 -0.014162502

001690 20130930 0.0014275724 0.1887835903

001690 20131231 0.0111048837 0.0384054444

001690 20140331 -0.00615956 -0.078924939

001690 20140630 -0.004446851 0.1953453542

001690 20140930 0.0012165497 0.1066788925

001690 20141231 0.0148603652 0.0470219108

001690 20150331 -0.006213387 0.1303489251

001690 20150630 -0.004041365 0.0175582589

001690 20150930 0.0007264318 -0.071783252

001690 20151231 0.012400347 -0.069109732

001690 20160331 -0.013138595 -0.071681597

001690 20160630 -0.005275545 -0.119072014

001690 20160930 0.0020190515 0.2109433449

001690 20161231 0.0145839438 -0.026547759

001690 20170331 -0.009175424 0.1675285819

001690 20170630 -0.003105223 0.0070201798

001690 20170930 0.0025276878 0.0806324686

001690 20171231 0.010873663 -0.077033442

001690 20180331 -0.007527275 0.0769450604

001690 20180630 -0.002568958 0.1174891488

012141 20070331 . .

012141 20070630 -0.006840826 0.039301915

012141 20070930 0.0045501006 0.0791492442

012141 20071231 0.00125861 0.2159948102

012141 20080331 -0.001207337 -0.091396305

012141 20080630 -0.000361478 -0.013992142

012141 20080930 0.0003172004 0.2467016235

012141 20081231 -0.001151606 -0.171371867

012141 20090331 -0.00732306 -0.058326322

012141 20090630 0.0003211438 0.1484024492

012141 20090930 0.0023109721 -0.000563749

012141 20091231 0.0114983925 0.1113820094

012141 20100331 -0.010350052 -0.038707234

012141 20100630 0.0025670507 -0.094669369

012141 20100930 0.0042540329 -0.06558027

012141 20101231 0.005218999 0.0450195831

012141 20110331 -0.006549471 -0.113316172

012141 20110630 0.0029479832 0.0599936985

012141 20110930 -0.000649708 0.1114515078

012141 20111231 0.0040717526 -0.048890014

012141 20120331 -0.005595293 0.0330296286

012141 20120630 -0.021843021 -0.007530868

012141 20120930 0.0197814607 -0.068648255

012141 20121231 0.0085439504 -0.105270689

012141 20130331 -0.001348278 0.0355996234

012141 20130630 -0.00378879 0.1366469038

012141 20130930 0.0010044828 -0.111795675

012141 20131231 0.0042318432 -0.009889401

012141 20140331 -0.002652274 0.0791397488

012141 20140630 -0.003050357 0.066885466

012141 20140930 -0.000188136 0.0338976295

012141 20141231 0.0034658358 -0.001447478

012141 20150331 -0.002661945 -0.113894517

012141 20150630 -0.023081782 0.11049779

012141 20150930 0.0229078022 0.0872105183

012141 20151231 0.0002638288 0.204585892

012141 20160331 -0.002903418 -0.039893154

012141 20160630 -0.001586843 -0.090012729

012141 20160930 0.0056762626 0.0723214928

012141 20161231 0.0012491095 0.0365159269

012141 20170331 -0.001535477 0.033560407

012141 20170630 0.0048615462 0.0584440048

012141 20170930 -0.002596238 0.0345231002

012141 20171231 -0.019539189 0.1052017727

012141 20180331 0.0195564312 0.0792267377

012141 20180630 0.0019140615 0.0157609209

;;;;

 my code is:But this code does not do what I want. What I want would for each observation, run regression for each gvkey for prior 8 datadate periods

My code is

data erc;
	set data.erctest;

proc sort data=erc nodupkey;
by gvkey datadate;

proc reg data=erc outest=cfsde adjrsq noprint;

	model  car= ue;
	by gvkey;

output out=varone r=residone;

[PGStats]

Here is a fairly straitforward approach to this task:

/* Create periods of 9 quarters: previous 8 and current */
data long;
set sasforum.erctest;
do qtr = -8 to 0;
    qtrDate = intnx("qtr", datadate, -qtr, "end");
    output;
    end;
format datadate qtrDate yymmp.;
label qtrDate = "Last quarter of 9";
run;

proc sort data=long; by gvkey qtrdate qtr; run;

/* Fit regressions. Keep only full periods (9 quarters) in the results */
proc reg data=long rsquare noprint 
    outest=longEst(
        where=(_P_ + _EDF_= 9)
        keep= _P_  _EDF_ gvKey qtrDate intercept ue _RSQ_);
where cmiss(car, ue) = 0;
by gvkey qtrDate;
model car = ue / selection=none;
run;

[vl12]

Thank you so much PGStats!

How do I require each regression to have at 8 quarter observations?

is the code written this way now:

datadate	qtrdate	qtr
20100331	20120330	-8
20100630	20120330	-7
20100930	20120330	-6
20101231	20120330	-5
20110331	20120330	-4
20110630	20120330	-3
20110930	20120330	-2
20111231	20120330	-1
20120330	20120330	0

if for example 20110930 is missing, then it goes back to get another 8 quarter of data? so it would look like this?

datadate	qtrdate	qtr
20091231	20120330	-8
20100331	20120330	-7
20100630	20120330	-6
20100930	20120330	-5
20101231	20120330	-4
20110331	20120330	-3
20110630	20120330	-2
20110930	20120330	.
20111231	20120330	-1
20120330	20120330	0

[PGStats]

Which observations (dates) should be involved in the regression associated with datadate=20120330, for example ?

My code assumes that no date is missing. A missing date will remove all 9 regressions involving that date. This is true also for the first 8 dates. What would you like to do with missing observations?

[vl12]

Thank you so much, PG, for continued help!

for datadate=20120330, the regression should involve the following colored observations. Which means that I would go back to replace the missing obs by going back further to get 8 quarters. How would this to be done?

datadate	qtrdate	qtr
20091231	20120330	-8
20100331	20120330	-7
20100630	20120330	-6
20100930	20120330	-5
20101231	20120330	-4
20110331	20120330	-3
20110630	20120330	-2
20110930	20120330	.
20111231	20120330	-1
20120330	20120330	0

[Reeza]

If you have SAS ETS try PROC TIMESERIES.

[vl12]

Thank you, Reesa! but I am not sure how

[Reeza]

What did you try that didn't work?

---

@vl12 wrote:

Thank you, Reesa! but I am not sure how

---

[Reeza]

https://gist.github.com/statgeek/07a3708dee1225ceb9d4aa75daab2c52