topic Find the next record with specific criteria in SAS Programming

Find the next record with specific criteria

Sanflo — Mon, 20 May 2019 09:17:30 GMT

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
;

Re: Find the next record with specific criteria

PeterClemmensen — Mon, 20 May 2019 09:32:56 GMT

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;

Re: Find the next record with specific criteria

Sanflo — Mon, 20 May 2019 13:04:01 GMT

Thanks, much appreciated.

Re: Find the next record with specific criteria

PeterClemmensen — Mon, 20 May 2019 13:12:00 GMT

Anytime, glad to help 🙂