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

[aalluru]

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.

[sbxkoenk · Accepted solution]

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

[aalluru]

This seems to be working fine. Thank you!

[Ksharp]

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;