Obtain observation Date where an indicator is first observed

[Bassil]

Hello,

I am trying to get for each ID, the date at which a 'status' column first becomes either '9','F', or 'R'.

For the example below, i would want ID 1 to show '12/31/2007' and ID 2 '11/30/2007'

stat	ID	date	Deliq Hist
C	1	7/31/2007	CCCCCCCCCCCC
C	1	8/31/2007	CCCCCCCCCCCC
C	1	9/30/2007	CCCCCCCCCCCC
3	1	10/31/2007	CCCCCCCCCCC3
6	1	11/30/2007	CCCCCCCCCC36
9	1	12/31/2007	CCCCCCCCC369
F	1	1/31/2008	CCCCCCCC369F
F	1	2/29/2008	CCCCCCC369FF
C	1	3/31/2008	CCCCCC369FFC
C	1	4/30/2008	CCCCC369FFCC
C	1	5/31/2008	CCCC369FFCCC
C	1	6/30/2008	CCC369FFCCCC
C	1	7/31/2008	CC369FFCCCCC
C	1	8/31/2008	C369FFCCCCCC
C	1	9/30/2008	369FFCCCCCCC

C	2	7/31/2007	CCCCCCCCCCCC
C	2	8/31/2007	CCCCCCCCCCCC
C	2	9/30/2007	CCCCCCCCCCCC
C	2	10/31/2007	CCCCCCCCCCCC
F	2	11/30/2007	CCCCCCCCCCCF
9	2	12/31/2007	CCCCCCCCCCF9

[Haikuo]

The previous one is buggy, 

data want;
set have;
by id stat notsorted;
if first.id then n=0;
if first.stat and stat in ('9', 'F', 'R') and id=lag(id) then n+1;
if n=1;
drop n;
run;

[PGStats]

Simple to do with SQL, taking advantage of auto-remerging

proc sql;
select * from have
where stat in ("9","F","R")
group by ID
having date = min(date);
quit;

[PGStats]

Or if you want to get also IDs that never reached 9 F or R status in your output:

proc sql;
select * from 
(select distinct ID from have) 
natural left join
(select * from have
where stat in ("9","F","R")
group by ID
having date = min(date));
quit;

[Astounding]

A handy DATA step technique is to go through the data twice:

data want;

   length first_date $ 10;

   do until (last.ID);

      set have;

      by id;

      if first_date=' ' and stat in ('9', 'F', 'R') then first_date=date;

   end;

   do until (last.ID);

      set have;

      by id;

      output;

   end;

run;

This version assumes DATE is character, but if it's numeric just change the LENGTH statement (and the check for first_date=' ').

Good luck.