BookmarkSubscribeRSS Feed
RandyStan
Fluorite | Level 6

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

3 REPLIES 3
Astounding
PROC Star
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
Fluorite | Level 6

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
Garnet | Level 18

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;

 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1157 views
  • 0 likes
  • 3 in conversation