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
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.
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.