Finding maximum value in a column within a subset/group

[neq1337]

Hello,

I have a data similar to the one below, but without the max3_value column. This is the one I would like to find, but I do not know how.

max3_value takes the last 3 values of the value column (within a group of person) and finds the maximum value of them. Of course, if it is the first or the second observation, it outputs a missing value.

For example: max3_value=10 in period 3 for person A is calculated as a max(10,5,3).

I thought of transposing the data with lagged values, but still I do not know how calculate it within groups of person.

I would kindly appreciate any help.

person	period	value	max3_value
A	1	10	.
A	2	5	.
A	3	3	10
A	4	5	5
A	5	2	5
A	6	3	5
A	7	4	4
A	8	14	14
B	1	6	.
B	2	5	.
B	3	1	6
B	4	1	4
B	5	4	4

[Tom · Accepted solution]

One way is populate variables with the previous 2 values and keep count of how many obs you have seen for this person.

data want;

  set have;

  by person ;

  lag1=lag1(value);

  lag2=lag2(value);

  if first.person then cnt=0;

  cnt + 1;

  if cnt < 3 then max3=.;

  else max3 = max(lag1,lag2,value);

run;

[neq1337]

Thank you for prompt response, it works perfectly.

[art297]

Why is it obvious that the first two values will be missing?  Do you always want them to be?

Also, why is the next to the last value for person A assigned the value 4?  Was that supposed to be 14?

[neq1337]

You are right, this is not obvious, but that is the way I want them to be (it makes sense in the original dataset).

max3_value=4 is OK, as it is a max(2,3,4).

[art297]

Tom's suggested code appears to accomplish what you want.

[Tom]

Actually it should be 5 as the max of 5,1,1 is 5.

[art297]

: You are correct about the line you referenced, but I was asking about an earlier line.  I had seen the last three assignments and figured that the OP was simply looking at groups of three.

Regardless, your code apparently does precisely what the OP was looking for.