topic Re: How can I extract the first row where the last value in a certain group appears? in SAS Programming

How can I extract the first row where the last value in a certain group appears?

aalluru — Fri, 26 Nov 2021 20:38:49 GMT

Suppose I have the following dataset :

a b c

1 1 1

1 2 3

1 3 2

1 4 2

2 4 1

2 3 3

Now if I consider the a=1 group, the last record has c=2. I want to pull the record where c=2 appears in the a=1 group for the first time i.e. 1 3 2. Similarly for the a=2 group, the last record has c=3. Since that is the first time it appears in this group that would be the row to display in that group.

So the output should be

a b c

1 3 2

2 3 3

Can someone please help me out with this? I would really appreciate it.

Re: How can I extract the first row where the last value in a certain group appears?

sbxkoenk — Fri, 26 Nov 2021 20:58:39 GMT

data work.have;
input A  B  C;
rownum = _N_;
cards;
1   1   1
1   2   3
1   3   2
1   4   2
2   4   1
2   3   3
;
run;

data work.lastC_in_group(drop=B);
 set work.have;
 by A;
 if last.A then output;
run;

PROC SQL noprint;
 create table work.want as
 select t1.*
 from   work.have as t1
      , work.lastC_in_group as t2
 where t1.A = t2.A and t1.C = t2.C 
 order by t1.rownum ;
QUIT;

data work.want;
 set work.want;
 by A;
 if first.A then output;
run;
/* end of program */

Koen

Re: How can I extract the first row where the last value in a certain group appears?

aalluru — Fri, 26 Nov 2021 21:33:42 GMT

This seems to be working fine. Thank you!

Re: How can I extract the first row where the last value in a certain group appears?

Ksharp — Sat, 27 Nov 2021 10:06:39 GMT

data have;
input A  B  C;
cards;
1   1   1
1   2   3
1   3   2
1   4   2
2   4   1
2   3   3
;
run;

data want;
do until(last.c);
 set have;
 by a c notsorted;
 if last.a then found=1;
end;
do until(last.c);
 set have;
 by a c notsorted;
 if found and first.c then output;
end;
drop found;
run;