BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sanflo
Fluorite | Level 6

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
;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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
Fluorite | Level 6

Thanks, much appreciated.

PeterClemmensen
Tourmaline | Level 20

Anytime, glad to help 🙂

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1031 views
  • 1 like
  • 2 in conversation