BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
neq1337
Calcite | Level 5

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.

personperiodvaluemax3_value
A110.
A25.
A3310
A455
A525
A635
A744
A81414
B16.
B25.
B316
B414
B544
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

7 REPLIES 7
Tom
Super User Tom
Super User

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
Calcite | Level 5

Thank you for prompt response, it works perfectly. Smiley Happy

art297
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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

Tom
Super User Tom
Super User

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

art297
Opal | Level 21

: 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.

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
  • 7 replies
  • 1940 views
  • 0 likes
  • 3 in conversation