How to average the last n observation in a data set using SAS

Question

Please, how can I get the average (mean) of the last 6 observations by group in a data set: the first column is the group i.e. Class and the second column is the observed variable i.e. Height.

Class Height
1 12.5
1 14.5
1 15.8
1 16.1
1 18.9
1 21.2
1 23.4
1 25.7
2 13.1
2 15.0
2 15.8
2 16.3
2 17.4
2 18.6
2 22.6
2 24.1
2 25.6
3 11.5
3 12.2
3 13.9
3 14.7
3 18.9
3 20.5
3 21.6
3 22.6
3 24.1
3 25.8

Answer 1

This example requires the input data to be sorted by class and each class to have at least 6 observations.

data input;
input class height;
cards;
1 12.5
1 14.5
1 15.8
1 16.1
1 18.9
1 21.2
1 23.4
1 25.7
2 13.1
2 15.0
2 15.8
2 16.3
2 17.4
2 18.6
2 22.6
2 24.1
2 25.6
3 11.5
3 12.2
3 13.9
3 14.7
3 18.9
3 20.5
3 21.6
3 22.6
3 24.1
3 25.8
;
run;

data output;
set input;
by class;
average = mean(height, lag1(height), lag2(height), lag3(height), lag4(height), lag5(height));
if last.class;
drop height;
run;

If the input is not sorted in ascending/descending order but is grouped by class (all records from each group are stored "together", e.g. sequence 1,1,3,3,2,2,2), NOTSORTED option will do the trick.

Answer 2

This is a little bit rough, but it should get the job done. Basically, we read in the data and then sort by the row number descending. We can then run through the data again and flag the first six observations from each 'class'. Please note that this only works if you have pre-sorted the observations on 'class'.

* This will read in your data and get a row number;
data one;
input class height;
row_number = _n_;
cards;
1 12.5
1 14.5
1 15.8
1 16.1
1 18.9
1 21.2
1 23.4
1 25.7
2 13.1
2 15.0
2 15.8
2 16.3
2 17.4
2 18.6
2 22.6
2 24.1
2 25.6
3 11.5
3 12.2
3 13.9
3 14.7
3 18.9
3 20.5
3 21.6
3 22.6
3 24.1
3 25.8
;
run;

* Now we sort by row number in descending order;
proc sort data = one out = two;
by descending row_number;
run;

* Now we run through the data again to make a flag for the last
six observations for each class;
data three;
set two;

* This sets up the counter;
retain counter 0;

* This resets the counter to zero at the first instance of each new class;
if class ne lag(class) then counter = 0;

counter = counter + 1;

* This makes a flag (1/0) on whether we want to keep the
observation for analysis;
keep_it = (counter le 6);

run;

* Now we get the means;
proc means data = three mean;
where keep_it gt 0;
class class;
var height;
run;