topic Re: extract multiple rows conditional the value of an observation in New SAS User

extract multiple rows conditional the value of an observation

antor82 — Tue, 23 Jul 2019 15:15:00 GMT

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

Re: extract multiple rows conditional the value of an observation

novinosrin — Tue, 23 Jul 2019 15:38:07 GMT

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?

Re: extract multiple rows conditional the value of an observation

tsap — Tue, 23 Jul 2019 15:40:33 GMT

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

Re: extract multiple rows conditional the value of an observation

antor82 — Tue, 23 Jul 2019 16:36:16 GMT

tsk so much!

works perfectly

Re: extract multiple rows conditional the value of an observation

tsap — Tue, 23 Jul 2019 17:00:08 GMT

You're welcome. Glad I could help.