Contributor
Posts: 42

# Compute lag mean sale with conditions

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.

Super User
Posts: 9,441

## Re: Compute lag mean sale with conditions

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;```
Discussion stats