Cross sectional regression over time looping and saving intercept and residuals

[Sofia Frascalo]

Dear community,


I am trying to generate a cross-sectional regression for multiple industries and through time.
However, I can't seem to find a way to obtain a column with the constant of each regression and I only get the intercept of the last regression.
This is what I have so far:
foreach i of varlist Oil Util Telcm PerSv BusSv Hardw Chips LabEq Boxes Trans Whlsl Rtail Meals Banks Insur RlEst Fin Other {
eststo: reg `i' MktRF
display [#2]_b[_cons]
predict newvar`i', residuals
gen coefficient`i'=.
replace coefficient `i'= _b[_cons]
}

Stata keeps giving me the error that the second equation couldn't be found.
Does someone know what I am doing wrong??

[William Lisowski]

Welcome to Statalist.

I cannot quite follow your logic, but I can point out two syntax errors in your code.

foreach i of varlist Oil Util ... {
eststo: reg `i' MktRF 
display [#2]_b[_cons]
predict newvar`i', residuals
gen coefficient`i'=.
replace coefficient `i'= _b[_cons]
}

Your reg command estimates a single equation, not a system of equations, so the equation number prefix [#2] is incorrect and should be removed from the third line.

In the sixth line, there is a space in coefficient `i' that must be deleted.

[Sofia Frascalo]

Hi William,


Thank you for your feedback.
Now I finally understand why the display command isn't working.
My loop was not working because the space between coefficient and `i'.
Now it works!
Thank you again.

[Sofia Frascalo]

I am also trying to regress the data over a window of three year.
I created different dummies.
However, then I would have to run the loop 21 times
Is there an easier way to do this?
the attached file shows the regression that I have to run over different industries i and the last 36 months (s)
**creation of dummies
gen D1=.
replace D1=1 if Date>=195201 & Date<=195412
gen D2=.
replace D2=1 if Date>=195501 & Date<=195712
gen D3=.
replace D3=1 if Date>=195801 & Date<=196012
gen D4=.
replace D4=1 if Date>=196101 & Date<=196312
gen D5=.
replace D5=1 if Date>=196401 & Date<=196612
gen D6=.
replace D6=1 if Date>=196701 & Date<=196912
gen D7=.
replace D4=1 if Date>=197001 & Date<=197212
gen D8=.
replace D8=1 if Date>=197301 & Date<=197512
gen D9=.
replace D9=1 if Date>=197601 & Date<=197812
gen D10=.
replace D4=1 if Date>=197901 & Date<=198112
gen D11=.
replace D11=1 if Date>=198201 & Date<=198412
gen D12=.
replace D12=1 if Date>=198501 & Date<=198712
gen D13=.
replace D13=1 if Date>=198801 & Date<=199012
gen D14=.
replace D14=1 if Date>=199101 & Date<=199312
gen D15=.
replace D15=1 if Date>=199401 & Date<=199612
gen D16=.
replace D16=1 if Date>=199701 & Date<=199912
gen D17=.
replace D17=1 if Date>=200001 & Date<=200212
gen D18=.
replace D18=1 if Date>=200301 & Date<=200512
gen D19=.
replace D19=1 if Date>=200601 & Date<=200812
gen D20=.
replace D20=1 if Date>=200901 & Date<=201112
gen D21=.
replace D21=1 if Date>=201201 & Date<=201512
f
oreach i of varlist Oil Util Telcm PerSv BusSv Hardw Chips LabEq Boxes Trans Whlsl Rtail Meals Banks Insur RlEst Fin Other {
reg `i' MktRF if D1==1
predict newvar`i', residuals
gen coefficient`i'=.
replace coefficient`i'= _b[_cons]
}

[Nick Cox]

Your image is too much like hard work for me to decode if you don't even define your notation. This isn't a code-breaking forum!

Turning to the Stata side: First, your dates are more awkward then they need to be.

Consider

gen mdate = ym(floor(Date/100), mod(Year, 100))
gen year = floor(Date/100)
format mdate %tm

Now you want disjoint three-year windows 1952-1954, 1955-1957, .... (except that the last window is 4 years!) .

A little experimentation gives you a way to get those windows in a variable:

. mata: round((1952, 1953, 1954, 1955, 1956, 1957), 3)
          1      2      3      4      5      6
    +-------------------------------------------+
  1 |  1953   1953   1953   1956   1956   1956  |
    +-------------------------------------------+

Consider then

gen year3 = round(year, 3)
replace year3 = 2013 if year3 == 2016 

Now what you want is perhaps more like

foreach i of varlist Oil Util Telcm PerSv BusSv Hardw Chips LabEq Boxes Trans Whlsl Rtail Meals Banks Insur RlEst Fin Other {
      gen intercept`i' = .
      gen residual`i'  = .
      
      forval y = 1953(3)2013 { 
            reg `i' MktRF if year3 == `y'
            replace intercept`i'= _b[_cons] if year3 == `y'
            predict work, residual
            replace residual`i' = work if year3 == `y'
            drop work
      }
}

I am guessing here and there. Don't you want to save your slope coefficients?

Consider also using a program like rangestat (SSC). This is what you could with one of your predictors:

rangestat (reg) Oil  MktRF, int(year3 0 0)
gen residualOil = Oil - b_cons - b_Oil * Oil

So, you could call up rangestat in a loop over predictors, but you would need to ensure distinct variable names.

[Sofia Frascalo]

Dear Nick,


Thank you very much for the code.
However, my regression needs to be a rolling window. I apologize for not being specific about it.
I need a rolling window of the last 36 months, so the first regression needs to start at time j=36 and then move further.
I think that I need a double loop, like the one you showed above.
Do you know the command for a rolling window?


Thank you in advance,

[Nick Cox]

Your code in #4 seemed clearly intended for disjoint windows. No matter.

This works and can be checked yourself:

webuse grunfeld , clear

foreach v in mvalue  kstock {
    rangestat (reg) `v' invest, int(year -2 0)
    gen `v'_residual = `v' - b_cons - b_invest * invest
    rename (reg_* b_* se_*) (`v'_=)
}

From that I guess that you want something like this:

local Y Oil Util Telcm PerSv BusSv Hardw Chips LabEq Boxes Trans Whlsl Rtail Meals Banks Insur RlEst Fin Other  

foreach y of local Y  {      
    rangestat (reg) `y' MktRf, int(year -2 0)    
    gen `v'_residual = `v' - b_cons - b_MktRf * MktRf      
    rename (reg_* b_* se_*) (`v'_=)  
}

Notes:

I explained how to get year in #5. Nothing stops you using a monthly date variable instead, but your Date variable is not fit for purpose.

You may not want all the results. Just drop whatever you don't want.

As mentioned in #5 rangestat is from SSC and must be installed before you can use it.

 ssc install rangestat

[Sofia Frascalo]

Thank you much.
I think it worked.
I changed my Date variable to fit the regression with the following commands:

tostring Date, replace
gen newdate = date(Date,"YM")
format newdate %tm
g mydate=date(Date, "YM")
format mydate %td
g dm=mofd(mydate)
format dm %tm

then I used the following commands in the loop by replacing "v" with "y" and "year" with "dm"

local Y Oil Util Telcm PerSv BusSv Hardw Chips LabEq Boxes Trans Whlsl Rtail Meals Banks Insur RlEst Fin Other

foreach y of local Y {
rangestat (reg) `y' MktRF, int(dm -36 0)
gen `y'_residual = `y' - b_cons - b_MktRF * MktRF
rename (reg_* b_* se_*) (`y'_=)
}

so I changed int(dm -36 0), since it is a rolling window regression using data from the last 36 months.

Do you think this is correct?

[Nick Cox]

Looks OK. On the date conversion side: I suggested a method in #5 (but there is a bad typo in the code). Let's check on that method (corrected), your method and yet another method.

* data like this 
clear 
input Date 
195201 
195412
195501 
195712
end 

* for later use 
clonevar Date2 = Date 

* what I suggested in #5 but with a bad typo fixed 

gen mdate = ym(floor(Date/100), mod(Date, 100))
format mdate %tm 

* what you did in #8 

tostring Date, replace 
gen newdate = date(Date,"YM") 
format newdate %tm
g mydate=date(Date, "YM")
format mydate %td
g dm=mofd(mydate)
format dm %tm

* another way 

gen another = mofd(daily(string(Date2), "YM")) 
format another %tm 

* are the results the same? 

list  Date dm mdate another 

     +--------------------------------------+
     |   Date        dm     mdate   another |
     |--------------------------------------|
  1. | 195201    1952m1    1952m1    1952m1 |
  2. | 195412   1954m12   1954m12   1954m12 |
  3. | 195501    1955m1    1955m1    1955m1 |
  4. | 195712   1957m12   1957m12   1957m12 |
     +--------------------------------------+

So, what you did was correct. There are shorter ways to do it.

Pointers for the future: Please always

* give data examples

* use CODE delimiters