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 |
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;
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;
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;
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.