FWIW, this appears to be closer to what you are looking for. However, it can give you a note in the log about more than one data set have repeats of the BY variable:
data want;
merge have have (where=(result > 50) in=delete_me);
by id;
if delete_me then delete;
run;
Sort by ID and descending result, so that the first record per ID s the largest. If that's greater than 50 then you need to drop that record otherwise you can keep it.
Create a list of all IDs with a result > 50. Then merge the two tables together, using the data set IN option to select only records that are not in the merged table.
DoW loops - fairly complex but allow you to summarize data ahead of time and then merge the results in. Use similar logic as the first approach
All of these essentially have two passes of the data in one form or another but SQL and the DoW loop let it "appear" to be one step.
@hjjijkkl wrote: is there another way to do it besides proc sql?
FWIW, this appears to be closer to what you are looking for. However, it can give you a note in the log about more than one data set have repeats of the BY variable:
data want;
merge have have (where=(result > 50) in=delete_me);
by id;
if delete_me then delete;
run;
Register Today!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.