Regress across rows to mimic Excel "LINEST" command?

[Thomas Craig]

Hello,
I am trying to find a STATA egen or other command that mimics the function of Excel "line estimate" or LINEST function.
This would provide the slope of the linear regression equation for data in the row. In the example below, slope 5.9 is
the result of the Excel command =LINEST(B2:F2). Is there a STATA egen command to generate the slope of a row of data?

Thank you for your help!

[Thomas Craig]

my apologies for the huge graph - it changed from the original size when I posted.

[Mike Lacy]

To see if there was some helpful -egen- function, I'd suggest you try -help egen- and search on "regress." (Stata's help is often, though not always, straightforwardly helpful.) I tried that and found no mention of "regress," and ruled out that possibility.

There are various do-it-yourself approaches to what you want. The one that occurred to me, perhaps not the shortest, is to temporarily put the data in long format, use -statsby- to estimate and save the slope for each subject, and then merge those slopes back onto the original data.

// Simulate data for illustration
clear
set obs 100
gen int subject = _n
gen year1 = runiform()
forval i = 2/10 {
   gen year`i' = year`=`i'-1' + 2 + rnormal(0,5)
}  
// Temporarily put the two variables into long format and obtain slope for each subject
preserve
    keep year* subject
    reshape long year, i(subject) j(num )
    tempfile slopedata
    statsby slope =_b[num], by(subject) saving(`slopedata'): regress year num
restore // back to origian wide file
//
merge 1:1 subject using `slopedata'
browse subject slope year*

[Nick Cox]

The task is programmable as an egen function, but as Mike Lacy indicates the Stataish way to proceed is to reshape long first. I would go further than Mike and recommend that you don't reshape back, or equivalently don't go back to the original data layout.

Please follow https://www.statalist.org/forums/help all the way to #18.

[Thomas Craig]

Thank you Mike and Nick for the quick feedback. Converting to long format, running getting the slope, and then converting back was a consideration but I was hoping there was an obscure egen command that was eluding my search through the blogs and documentation. I will run it as you suggest, Mike, and consider working with it while still in long format as Nick advises. I appreciate your time and help in this particular issue and with the STATALIST forum in general; it is invaluable. Have a wonderful day.

[Thomas Craig]

Nick, I have read the help topics all the way to 18. Duly noted. Thanks.