topic Re: Rolling ERC regression in SAS Programming

Rolling ERC regression

vl12 — Tue, 11 Sep 2018 22:14:42 GMT

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

Re: Rolling ERC regression

vl12 — Tue, 11 Sep 2018 23:18:53 GMT

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

Re: Rolling ERC regression

Reeza — Wed, 12 Sep 2018 02:23:54 GMT

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?

Re: Rolling ERC regression

vl12 — Wed, 12 Sep 2018 03:06:57 GMT

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;

Re: Rolling ERC regression

vl12 — Wed, 12 Sep 2018 03:08:10 GMT

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?

Re: Rolling ERC regression

Reeza — Wed, 12 Sep 2018 03:15:22 GMT

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-data-AKA-generate/ta-p/258712

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?

Re: Rolling ERC regression

vl12 — Wed, 12 Sep 2018 03:24:47 GMT

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;

Re: Rolling ERC regression

PGStats — Wed, 12 Sep 2018 03:27:43 GMT

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;

Re: Rolling ERC regression

vl12 — Wed, 12 Sep 2018 16:51:06 GMT

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

Re: Rolling ERC regression

PGStats — Wed, 12 Sep 2018 18:03:39 GMT

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?

Re: Rolling ERC regression

vl12 — Wed, 12 Sep 2018 18:32:31 GMT

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

Re: Rolling ERC regression

Reeza — Wed, 12 Sep 2018 18:42:35 GMT

If you have SAS ETS try PROC TIMESERIES.

Re: Rolling ERC regression

vl12 — Wed, 12 Sep 2018 18:55:11 GMT

Thank you, Reesa! but I am not sure how

Re: Rolling ERC regression

Reeza — Wed, 12 Sep 2018 18:56:04 GMT

What did you try that didn't work?

@vl12 wrote:

Thank you, Reesa! but I am not sure how

Re: Rolling ERC regression

Reeza — Wed, 12 Sep 2018 18:58:52 GMT

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

Re: Rolling ERC regression

vl12 — Wed, 12 Sep 2018 19:02:46 GMT

thanks again. However the code won't work for me as it's only for one company ibm and has an exact beginning and ending dates. My sample companies have different beginning and ending dates.

PG's code works just that I need to back fill the missing quarter with previous quarter. If this can be solved in his original code, that would solve my problem.

Thank you so much for trying to help!

Re: Rolling ERC regression

PGStats — Thu, 13 Sep 2018 03:26:59 GMT

If all you want is a regression of the previous 8 obs, then the dates don't matter and the code is a bit different:

/* Create periods of 8 previous observations */
data long;
do obs = 1 by 1 until(last.gvKey);
    set sasforum.erctest;
    by gvkey notsorted;
    do grp = obs+1 to obs+8;
        if grp > 8 then output;
        end;
    end;
run;

proc sort data=long; by gvkey grp; run;

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

proc sql;
create table regStats as
select distinct
    b.gvKey,
    b.datadate,
    a.intercept,
    a.ue as slope,
    a._RSQ_
from 
    longEst as a inner join
    long as b on a.gvKey=b.gvKey and a.grp=b.obs
order by gvKey, datadate;
quit;

Re: Rolling ERC regression

vl12 — Thu, 13 Sep 2018 03:41:05 GMT

Hi PG,

yes. This would work. This code restrict the regression to be run on each gvkey datadate pair to 8 observations,which is what I ask for.

If I'd like to use all the observations in prior quarters but require at least 8 obs for a given gvkey and datadate, how can I do it? So some regression will have 8 obs, some will have 9 or more. And can you output the number of obs used in the regression as well?

Thank you so much!

Re: Rolling ERC regression

PGStats — Thu, 13 Sep 2018 03:48:34 GMT

You seem to want a cumulative regression now. That's a different matter. It might be better to put it as a new question to attact more attention.

Re: Rolling ERC regression

vl12 — Thu, 13 Sep 2018 03:52:14 GMT

thanks. will make a new post