topic Re: Obtain observation Date where an indicator is first observed in SAS Programming

Obtain observation Date where an indicator is first observed

Bassil — Fri, 09 Oct 2015 01:07:10 GMT

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

Re: Obtain observation Date where an indicator is first observed

Haikuo — Fri, 09 Oct 2015 02:03:30 GMT

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;

Re: Obtain observation Date where an indicator is first observed

PGStats — Fri, 09 Oct 2015 02:08:46 GMT

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;

Re: Obtain observation Date where an indicator is first observed

PGStats — Fri, 09 Oct 2015 02:19:10 GMT

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;

Re: Obtain observation Date where an indicator is first observed

Astounding — Fri, 09 Oct 2015 02:46:24 GMT

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.