Create groups by amount of time between patient visits

[Abishekaa]

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

[Reeza · Accepted solution]

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;

[Abishekaa]

Hi Reeza, thanks so much for the answer! Could you please explain how the sql step works? Specially the 'part1' and 'ptid order by 1,3' ?

[Reeza]

Group by 1, 3 is short hand notation to sort by columns 1/3.

Part1 is the data set from the previous step.

All it's doing is getting the maximum per group and adding it in. However, SQL doesn't maintain default order so I sort to ensure you get the same order back.
There are multiple ways to accomplish this step, with SQL being the easiest.

This post covers the average, but in your case it's the maximum.
https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

[Abishekaa]

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.

[Reeza]

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;

[ballardw]

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