Compute lag mean sale with conditions

[trungcva112]

Hi everyone,

I have a panel dataset below.

Now I have to compute a variable named "Lag mean sales". For example, the "lag mean sale" of firm 1 in the year 2002 = mean of sales over 4 quarters in the year 2001.

The tricky part is that I have to remove rows with missing sale data and rows with growth of sales is >75% or <-75%.

For example, the "lag mean sales" of firm 1 in the year 2002 = (10+20+21)/3 = 17

                      the "lag mean sales" of firm 1 in the year 2003 =  (12+18)/2 = 15

firms	Year	Sales	Growth
1	QI/2001	10	.
1	QII/2001	17.5	75%
1	QIII/2001	20	14%
1	QIV/2001	21	5%
1	QI/2002	12	-43%
1	QII/2002	14	17%
1	QIII/2002	.	.
1	QIV/2002	.	.
1	QI/2003	11	.
1	QII/2003	8	-27%
1	QIII/2003	12	50%
1	QIV/2003	15	25%

The output data is like this:

firms	Year	Sales	Growth	Lag mean sale
1	QI/2001	10	.
1	QII/2001	17.5	75%
1	QIII/2001	20	14%
1	QIV/2001	21	5%
1	QI/2002	12	-43%	17
1	QII/2002	14	17%	17
1	QIII/2002	16	14%	17
1	QIV/2002	.	.	17
1	QI/2003	11	.	14
1	QII/2003	8	-27%	14
1	QIII/2003	12	50%	14
1	QIV/2003	15	25%	14

Could someone show me how to do it, please? Any idea is much appreciated.

[RW9]

You would use two retained variables, perhaps something like:

data want;
  set have;
  retain lag_mean_sale no_elem;
  by year;
  if first.year then do;
    lag_mean_sale=sales;
    no_elem=1;
  end;
  else do;
    if sales ne . then do;
      lag_mean_sale=sum(lag_mean_sale);
      no_elem=sum(no_elem,1);
    end;
  end;
run;