topic Re: How to run regressions before certain date and get coefficient for each year in SAS Programming

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

Lipty — Sun, 29 Nov 2020 16:38:45 GMT

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.

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

PGStats — Sun, 29 Nov 2020 17:35:25 GMT

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;

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

Lipty — Sun, 29 Nov 2020 18:36:31 GMT

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:

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

PGStats — Sun, 29 Nov 2020 18:58:47 GMT

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

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

Lipty — Sun, 29 Nov 2020 19:39:21 GMT

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

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

Lipty — Sun, 29 Nov 2020 21:13:16 GMT

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

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

PGStats — Sun, 29 Nov 2020 21:22:38 GMT

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;

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

PGStats — Sun, 29 Nov 2020 21:27:14 GMT

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

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

Lipty — Sun, 29 Nov 2020 21:40:54 GMT

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