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

[Lipty]

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.

[PGStats]

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;

[Lipty]

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. 

If this is fixed, the codes are all set. Could you please help? Thank you again.

Samplereg:

Temp: 

[PGStats]

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...

[Lipty]

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

[PGStats · Accepted solution]

How about:

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;

[Lipty]

Many thanks~~~~~
You are so helpful. Without your neat code, I had no clue. ^_^

[Lipty]

@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;

[PGStats]

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