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
;
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;
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;
Thanks, much appreciated.
Anytime, glad to help 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.