topic Re: Maximum Value between observations in SAS Programming

Maximum Value between observations

RandyStan — Thu, 25 Apr 2019 04:46:24 GMT

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

Re: Maximum Value between observations

Astounding — Thu, 25 Apr 2019 05:37:03 GMT

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.

Re: Maximum Value between observations

RandyStan — Thu, 25 Apr 2019 16:25:27 GMT

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.

Re: Maximum Value between observations

Shmuel — Thu, 25 Apr 2019 20:31:11 GMT

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;