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 🙂
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.