Contributor
Posts: 50

# How to regression by groups and variable in one table and put the coefficient back to the table

[ Edited ]

Hello,

I have a dataset like this.

``````data temp.database ;
input
YEAR ID SALE;
datalines;
2000 001 232001 001 252002 001 282003 001 302004 001 322005 001 402000 002 232001 002 252002 002 282003 002 302004 002 322005 002 40;run;``````

I would like to have the regression coefficient of regressing previous 3 year SALE under the same ID on YEAR variable.

thus,

For example,

for ID 001 in 2003, I want to add a new variable coefficient BETA from: regressing the SALE (in YEAR 2000,2001,2002) on YEAR value (2000, 2001, 2002).

Then the similar for ID 001 in 2004, but the value in regression will be SALE in 2001, 2002, 2003 and 2001, 2002, 2003

How can I do this?

My result want is like this

``````data temp.database ;
input
YEAR ID SALE BETA;
datalines;
2000 001 23     /* here it is empty as there are no value for previous year */
2001 001 25
2002 001 28
2003 001 30 0.3 /*the value I just make up, but it should be the coefficient from regressing previous 3 year SALE on the YEAR value*/
2004 001 32 0.4
2005 001 40 0.5
2000 002 23
2001 002 25
2002 002 28
2003 002 30 0.2
2004 002 32 0.3
2005 002 40 0.4
;
run;``````

How to do this guys?

Really stuck here

Super User
Posts: 6,622

## Re: How to regression by groups and variable in one table and put the coefficient back to the table

There's a formula for the coefficient, but I don't know what it is.  If you do, supply it and we can program it.  It's based on a combination of some of these:  sum of x, sum of y, sum of x-squared, sum of y-squared, sum of x*y.

Super User
Posts: 13,283

## Re: How to regression by groups and variable in one table and put the coefficient back to the table

What type of regression and which coefficient? If you are thinking of a linear regression there are multiple coefficients, one for intercept and one for each independent variable in the model plus such things as coefficient of variation.

Note that running a regression for 3 years on your example data generates some warnings:

```WARNING: The range of variable YEAR is so small relative to its mean that there may be loss of
accuracy in the computations. You may need to rescale the variable to have a larger
value of RANGE/abs(MEAN), for example, by using PROC STANDARD M=0;
```

And how will you use that coefficient later?

Contributor
Posts: 50

## Re: How to regression by groups and variable in one table and put the coefficient back to the table

So the regression model I want is very simple, just the OLS estimator. The model is like this

SALE in previous 3 year = BETA*previous 3 YEAR + residual. In a matrix way, it is like this

SALE in 2000                     2000

SALE in 2001   =    BETA*  2001     +    residual

SALE in 2002                     2002

I only want the BETA. And in literature in my area, it can capture the SALE growth.

If the range is too small, I can extend to 4 years in stead of 3 years.

Is there a way to do this?

Thank you very much!

Discussion stats
• 3 replies
• 114 views
• 0 likes
• 3 in conversation