Maximum Value between observations

[RandyStan]

Dear All

I want to choose the max of variable p between observations.  So the data set is as follows.  The file is sorted by ID


p        tno            ID          Second
5         4657       1144         548
5         4658       1144          548
5.5      4662        1144         551

5.45    4663        1144         551

6.25     4668       1144         554

6.25    4669         1144        554
6         3567        1145         425
6.25     3570        1145        427


What I need is the maximum p between seconds 549 and 551 and Seconds 552 and 553 for ID 1144 and between seconds 425 and 427

The p file can be as follows

p       second
6        425
6.25    426
6.25    427
5        548
5        548
5.4     549
5.45    550
5.5     550

5.75   551

5.85   551

6.10   552

6.15   552

6.2     553

6.25   553
And the file I need is

p        tno            ID          Second   Maxp
5         4657       1144         548
5         4658       1144          548
5.5      4662        1144         551      5.5

5.45    4663        1144         551      5.5

6.25     4668       1144         554      6.25

6.25    4669         1144        554      6.25

6         3567        1145         425
6.25     3570        1145        427      6.25

Thanx in Advance

Randy

[Astounding]

There is much about your post that is either incomplete or wrong. So here is a guess that should be close to what you want:

data want;
set have;
by id;
retain maxval;
drop maxval;
if first.id then maxval=p;
else do;
maxval = max(maxval, p) ;
maxp = maxval;
end;
run;

Try it and see how close it comes to what you want.

[RandyStan]

Dear Astounding:

  If you need any clarification about the question, please do ask.

  What I need is that the MAX p from the second file when the ID is inactive (and then merged back into the original file).

  Thanx a lot.

    Randy

---

@Astounding wrote:
There is much about your post that is either incomplete or wrong. So here is a guess that should be close to what you want:

data want;
set have;
by id;
retain maxval;
drop maxval;
if first.id then maxval=p;
else do;
maxval = max(maxval, p) ;
maxp = maxval;
end;
run;

Try it and see how close it comes to what you want.

---

[Shmuel]

Clarification needed:

1) What do you mean by inactive ?

     Do you mean absent seconds per ID in sequence between minimum and maximum of existing seconds ?

    If positive, for ID=1144, why did you mentioned in first post: "...between seconds 549 and 551 ..." 

    ass 551 exist for ID 1144 ?

2) How about next code (not tested), does it fit your request ?

proc sort data=table2; by second p; run;

data max_p;
  set table2;
   by second;
        if last.second;  /* max p per second */
run;

proc sql;
   create table want as
   select a.*, b.*
   from table1 as a
   left join max_p as b
   on min(a.second) < b.second <max(a.second) and
   b.second not in (select a.second from table1)
   group by a.ID;
quit;