Find the next record with specific criteria

[Sanflo]

Hello all,

I am hoping to show each time a case has went through a specific status and the next status/date it moves to the other specific status.

I've included in my HAVE and WANT data to show what the data looks like / desired outcome. The criteria is each time an ID goes to the A status until the next time they reach the B status. If I can provide anymore detail then just let me know.

Many thanks,

Sandy

data work.HAVE;
	input ID :1. STATUS :$1. DATE :date9.;
	format date date9.;
	infile datalines dlm=',';
	datalines;
1,A,01MAY2019
1,B,02MAY2019
2,A,01MAY2019
2,C,02MAY2019
2,B,03MAY2019
3,A,01MAY2019
3,C,02MAY2019
3,A,03MAY2019
3,B,04MAY2019
4,A,01MAY2019
4,B,02MAY2019
4,A,03MAY2019
4,B,04MAY2019
;

data work.WANT;
	input ID :1. STATUS_1 :$1. DATE_1 :date9. STATUS_2 :$1. DATE_2 :date9.;
	format date_1 date_2 date9.;
	infile datalines dlm=',';
	datalines;
1,A,01MAY2019,B,02MAY2019
2,A,01MAY2019,B,03MAY2019
3,A,01MAY2019,B,04MAY2019
3,A,03MAY2019,B,04MAY2019
4,A,01MAY2019,B,02MAY2019
4,A,03MAY2019,B,04MAY2019
;

[PeterClemmensen · Accepted solution]

Do something like this

data want;
   do until (status='B' | (last.id));
      set have;
      by id;
      if status='A' then do;
         status1='A';
         date1=date;
      end;
   end;

   if status='B';
   
   rename date=date2 status=status2;
   format date: date9.;
run;

[Sanflo]

Thanks, much appreciated.

[PeterClemmensen]

Anytime, glad to help 🙂