BookmarkSubscribeRSS Feed
Bassil
Calcite | Level 5

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'

 

statIDdateDeliq Hist
C17/31/2007CCCCCCCCCCCC
C18/31/2007CCCCCCCCCCCC
C19/30/2007CCCCCCCCCCCC
3110/31/2007CCCCCCCCCCC3
6111/30/2007CCCCCCCCCC36
9112/31/2007CCCCCCCCC369
F11/31/2008CCCCCCCC369F
F12/29/2008CCCCCCC369FF
C13/31/2008CCCCCC369FFC
C14/30/2008CCCCC369FFCC
C15/31/2008CCCC369FFCCC
C16/30/2008CCC369FFCCCC
C17/31/2008CC369FFCCCCC
C18/31/2008C369FFCCCCCC
C19/30/2008369FFCCCCCCC
C27/31/2007CCCCCCCCCCCC
C28/31/2007CCCCCCCCCCCC
C29/30/2007CCCCCCCCCCCC
C210/31/2007CCCCCCCCCCCC
F211/30/2007CCCCCCCCCCCF
9212/31/2007CCCCCCCCCCF9
4 REPLIES 4
Haikuo
Onyx | Level 15

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
Opal | Level 21

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;
PG
PGStats
Opal | Level 21

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;
PG
Astounding
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 825 views
  • 0 likes
  • 4 in conversation