extract multiple rows conditional the value of an observation

[antor82]

Hi all!

I have a dataset like this

Group   A   B   C   X

    1       5    3   1   12

    1       6    4   1   9

    1       7    6   3   10

    2       4    2   8   7

    2       3    7   5   8

    2       5    2   4   13

    3       2    10  5   9

    3       4    9   3  15

    3       7    6   7   14

For each group, I need to extract only one row per group at a specific value of X (namely the max value of X in each group)

My final dataset should be like this

Group   A   B   C   X

    1       5    3   1   12

    2       5    2   4   13

    3       4    9   3  15

Tks in advance

[novinosrin]

proc sql noprint;
create table want as
select *
from have
group by group
having x=max(x);
quit;

Now tell us what if there are ties in X values?

[tsap · Accepted solution]

DATA WORK.HAVE;
FORMAT 	 Group $1. A 8. B 8. C 8. X 8.;
INFORMAT Group $1. A 8. B 8. C 8. X 8.;
INPUT    Group 	   A 	B 	 C 	  X;
INFILE DATALINES DLM='|' DSD;
DATALINES;
1|5|3|1|12
1|6|4|1|9
1|7|6|3|10
2|4|2|8|7
2|3|7|5|8
2|5|2|4|13
3|2|10|5|9
3|4|9|3|15
3|7|6|7|14
;

PROC SORT DATA=WORK.HAVE; BY GROUP X; RUN;

DATA WORK.WANT;
	SET WORK.HAVE;
	BY GROUP X;
	IF LAST.GROUP AND LAST.X;
RUN;

By sorting the data by 'Group' and 'X', in the following datastep we can utilize the 'last.variable' feature. So the last iteration within each unique group id with the highest 'X' value within that unique group id.

Output:

Group	A	B	C	X
1	5	3	1	12
2	5	2	4	13
3	4	9	3	15

[antor82]

tsk so much!

works perfectly

[tsap]

You're welcome. Glad I could help.