Obsidian | Level 7

## How to run regressions before certain date and get coefficient for each year

I have the following variables:

ID, year, TE, LTE

In year t, I would like to perform the following pooled cross-sectional/time-series regression using all firm-years with available data in all prior years:

TE(t-1)=a+b*LTE(t-1)+error

For example, In 2007, we use all observations prior to 2007 (2005-2006), and in 2018 we use all observations prior to 2018 (2005-2017), etc.

The desired table should be

Year              Coefficient for this year

2006

2007

2008

2009

...

2018

2019

Attached is my sample data. Thank you very much in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: How to run regressions before certain date and get coefficient for each year

``````proc sql;
create table temp as
select
a.id,
a.year,
b.year as pyear,
b.te,
b.lte
from
sasforum.samplereg as a inner join
sasforum.samplereg as b on a.id=b.id and b.year < a.year
order by year, pyear;
quit;

proc reg data=temp outest=sampleEst noprint;
by year;
model te = lte;
run;

proc print noobs data=sampleEst; var year intercept lte; run;``````

PG
8 REPLIES 8
Opal | Level 21

## Re: How to run regressions before certain date and get coefficient for each year

This way:

``````proc sql;
create table temp as
select
a.id,
a.year,
b.year as pyear,
a.te,
b.te as lte
from
sasforum.samplereg as a inner join
sasforum.samplereg as b on a.id=b.id and b.year < a.year
order by year, pyear;
quit;

proc reg data=temp outest=sampleEst noprint;
by year;
model te = lte;
run;

proc print noobs data=sampleEst; var year intercept lte; run;``````

PG
Obsidian | Level 7

## Re: How to run regressions before certain date and get coefficient for each year

Thank you very much, @PGStats  you are so helpful. You also helped me last time. The codes worked well.

Your codes are so neat. I have one question regarding sql, in which b.te as lte.

I compared the two data sets as follows. The TE and LTE of pyear 2007 in Temp should be equal to TE and LTE of year 2007 in Samplereg. The TE and LTE pair should be the same and not changing.

Samplereg:

Temp:

Opal | Level 21

## Re: How to run regressions before certain date and get coefficient for each year

Try looking at the data sorted by year and pyear:

``````proc sort data=temp out=tempSort; where id="001004"; by year pyear; run;

proc print data=tempSort; by id year; id id year; run;``````

.... in dataset temp, lte is te for the same id in year pyear (a previous year).

Or maybe I don't understand what you are trying to do...

PG
Obsidian | Level 7

## Re: How to run regressions before certain date and get coefficient for each year

Thanks, @PGStats . Sorry for the confusion. The desired output is as follows:

TE and lTE are a pair. They will stay the same. : ) When pyear is 2005 for any firm, TE and LTE should be the actual TE and LTE of 2005.

Many thanks.

 id year pyear te lte 001004 2006 2005 -17.63 -31.89 id 001004 2007 2005 -17.63 -31.89 2006 -2.606 -17.63 id 001004 2008 2005 -17.63 -31.89 2006 -2.606 -17.63 2007 8.721 -2.606 id 001004 2009 2005 -17.63 -31.89 2006 -2.606 -17.63 2007 8.721 -2.606 2008 16.435 8.721

Opal | Level 21

## Re: How to run regressions before certain date and get coefficient for each year

``````proc sql;
create table temp as
select
a.id,
a.year,
b.year as pyear,
b.te,
b.lte
from
sasforum.samplereg as a inner join
sasforum.samplereg as b on a.id=b.id and b.year < a.year
order by year, pyear;
quit;

proc reg data=temp outest=sampleEst noprint;
by year;
model te = lte;
run;

proc print noobs data=sampleEst; var year intercept lte; run;``````

PG
Obsidian | Level 7

## Re: How to run regressions before certain date and get coefficient for each year

Many thanks~~~~~
Obsidian | Level 7

## Re: How to run regressions before certain date and get coefficient for each year

@PGStats It works now. : ) Thank you very much.

proc sql;
create table temp as
select
a.id,
a.year,
b.year as pyear,
a.te,
b.te as pte,
b.lte as plte
from
lf.samplereg as a inner join
lf.samplereg as b on a.id=b.id and b.year < a.year
order by year, pyear;
quit;

Opal | Level 21

## Re: How to run regressions before certain date and get coefficient for each year

Great, you figured it out before I replied, which shows that you know what you are doing! Congrats!

PG
Discussion stats
• 8 replies
• 795 views
• 4 likes
• 2 in conversation