Hi all, thanks in advance for your advice on this. This is my sample dataset. The variables I WANT to create are 'Episode' and 'Total'
Episode - (Pt 1) - All test dates within 10 days are considered to be one Episode. Therefore, the first two observations are one episode, the third observation is one episode, and the fourth and fifth observations are one episode.
Total - Total episodes for each patient - Patient 1 has 3 episodes total, and patient 2 has 3 episodes total.
Please advise on how to create the 'Episode' variable by grouping test dates within a 10 day period?
PtID TestLevel TestDt Episode Total
1 10 01/01/1990 1 3
1 22 01/02/1990 1 3
1 30 01/22/1990 2 3
1 25 09/01/1992 3 3
1 35 09/08/1992 3 3
2 54 01/10/1995 1 3
2 54 01/11/1995 1 3
2 54 01/12/1995 1 3
2 87 12/01/1998 2 3
2 66 12/22/1998 3 3
data have;
infile cards;
input PtID TestLevel TestDt : mmddyy10. Episode Total;
cards;
1 10 01/01/1990 1 3
1 22 01/02/1990 1 3
1 30 01/22/1990 2 3
1 25 09/01/1992 3 3
1 35 09/08/1992 3 3
2 54 01/10/1995 1 3
2 54 01/11/1995 1 3
2 54 01/12/1995 1 3
2 87 12/01/1998 2 3
2 66 12/22/1998 3 3
;
;
;;
run;
data part1;
set have;
by ptID;
retain index_date episode_counter;
if first.ptid then
do;
index_date=testdt;
episode_counter=1;
end;
if testdt - index_date > 10 then
do;
episode_counter + 1;
index_date=testdt;
end;
run;
proc sql;
create table want as select *, max(episode) as check_total from part1 group by
ptid order by 1, 3;
quit;
data have;
infile cards;
input PtID TestLevel TestDt : mmddyy10. Episode Total;
cards;
1 10 01/01/1990 1 3
1 22 01/02/1990 1 3
1 30 01/22/1990 2 3
1 25 09/01/1992 3 3
1 35 09/08/1992 3 3
2 54 01/10/1995 1 3
2 54 01/11/1995 1 3
2 54 01/12/1995 1 3
2 87 12/01/1998 2 3
2 66 12/22/1998 3 3
;
;
;;
run;
data part1;
set have;
by ptID;
retain index_date episode_counter;
if first.ptid then
do;
index_date=testdt;
episode_counter=1;
end;
if testdt - index_date > 10 then
do;
episode_counter + 1;
index_date=testdt;
end;
run;
proc sql;
create table want as select *, max(episode) as check_total from part1 group by
ptid order by 1, 3;
quit;
PtID TestLevel TestDt Episode Total
1 10 01/01/1990 1 3
1 22 01/02/1990 1 3
1 30 01/22/1990 2 3
1 25 09/01/1992 3 3
1 35 09/08/1992 3 3
2 54 01/10/1995 4 3
2 54 01/11/1995 4 3
2 54 01/12/1995 4 3
2 87 12/01/1998 5 3
2 66 12/22/1998 6 3
Thank you, Reeza. One more question about the first part of coding for the 'Episode'. How would it change to get episode numbers like this? Eg- The episode numbers are consecutive.
Change this:
if first.ptid then
do;
index_date=testdt;
episode_counter=1;
end;
to:
if first.ptid then
do;
index_date=testdt;
episode_counter+1;
end;
You may need to set the episode_counter to 0 on the retain statement as well:
retain episode_counter 0;
Just for your consideration: what if the dates are all exactly 3 days apart and there are 20 dates for the same Id? The third date would be 9 days from the first so in the 10 days for that as an episode. Now the fourth date is 3 days from the last of the first episode but more than 10 from the first date of episode 1. So is that a new episode or not?
So where are the episode breaks in:
5 07/01/2022 5 07/04/2022 5 07/07/2022 5 07/10/2022 5 07/13/2022 5 07/16/2022 5 07/19/2022 5 07/22/2022 5 07/25/2022 5 07/28/2022 5 07/31/2022 5 08/03/2022 5 08/06/2022 5 08/09/2022 5 08/12/2022 5 08/15/2022 5 08/18/2022 5 08/21/2022 5 08/24/2022 5 08/27/2022 5 08/30/2022
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.