BookmarkSubscribeRSS Feed
yanshuai
Quartz | Level 8

Hello,

 

I have a dataset like this.

data temp.database ;
input
YEAR ID SALE;
datalines;
2000 001 23
2001 001 25
2002 001 28
2003 001 30
2004 001 32
2005 001 40
2000 002 23
2001 002 25
2002 002 28
2003 002 30
2004 002 32
2005 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:(

3 REPLIES 3
Astounding
PROC Star

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.

ballardw
Super User

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?

yanshuai
Quartz | Level 8

Thank you for the reply!

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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