How do i find days betten first and second and second and third episode date on a single cliernt?

[Dhana18]

Hi I have a data like this

data Test;

input ClientID IncidentID Episodedate mmddyy10.;

cards;

4750 3119 03/05/2018

1021 3110 11/06/2018

1021 3111 01/07/2019

4765 3576 03/28/2019

4767 3821 10/25/2019

4770 3332 10/08/2018

4770 3631 05/23/2019

4770 3798 10/09/2019

run;

I want

ClientID IncidentID Episode Date Days between first and second Episode Days between second and third Episode

4750 3119 03/05/2018                  0

1021 3110 11/06/2018

1021 3111 01/07/2019                 52

4765 3576 03/28/2019                 0

4767 3821 10/25/2019                 0

4770 3332 10/08/2018

4770 3631 05/23/2019                227

4770 3798 10/09/2019                                                                              180

Please help me.  It needs to be grouped by client id.  Some client id has more than three episodes in two year period. 

I tried this, did not work.

proc sql;
create table repeat as
select
      clientid,
     min(Episodedate) as Episode1,
     max(Episodedate) as Episode2,
	 max(Episodedate) - min(Episodedate) as repeat_inf_day
from
have
group by  Clientid;
quit;

[Shmuel]

Change one line:

instead 

 max(Episodedate) - min(Episodedate) as repeat_inf_day

try:

(calculated episode2 - calculated episode1) as repeat_inf_day

[Dhana18]

Thank you for your response. I greatly appreciate it. It gave me days between first and second episode, but there are clients with more than two episode dates three or sometimes four episode date in two year period. How do I modify the code to get days difference between second and third episode and third and fourth? Please!!

I tried this but did not work.
proc sql;
create table repeat as
select
clientid,
min(Episodedate) as Episode1,
max(Episodedate) as Episode2,
min(Episodedate) as Episode3,
max(Episodedate) as Episode4,
(calculated episode2 - calculated episode1) as repeat_inf_day1
(calculated episode3 - calculated episode2) as repeat_inf_day1
(calculated episode4 - calculated episode3) as repeat_inf_day3
from New_1
group by Clientid;

quit;

[Shmuel]

As much as I know:

    min(episodate) with group by clientID will result in first episod date.

    max(episodate) with group by clientID will result in latest episod date.

There is no Eisod3, Episod4 ... etc. - no matter how many episodes has a client.

[r_behata]

One Way :

data Test;
format Episodedate mmddyy10.;
input ClientID IncidentID Episodedate mmddyy10.;
cards;
4750 3119 03/05/2018
1021 3110 11/06/2018
1021 3111 01/07/2019
4765 3576 03/28/2019
4767 3821 10/25/2019
4770 3332 10/08/2018
4770 3631 05/23/2019
4770 3798 10/09/2019
run;

data want;
	array edt[99] _temporary_;

	do i=1 by 1 until(last.ClientID);
		set Test;
		by ClientID notsorted;
		edt[i]=Episodedate;
	end;

	do j=1 by 1 until(last.ClientID);
		set Test;
		by ClientID notsorted;		
		if j = 2 then 	repeat_inf_day=Episodedate-edt[j-1];
		if j = 3 then 	repeat_inf_day2=Episodedate-edt[j-1];
		if j = 4 then 	repeat_inf_day3=Episodedate-edt[j-1];
		output;
		call missing(of repeat_inf_day:);
	end;
	
	drop i j;
	
run;

bs	Episodedate	ClientID	IncidentID	repeat_inf_day	repeat_inf_day2	repeat_inf_day3
1	03/05/2018	4750	3119	.	.	.
2	11/06/2018	1021	3110	.	.	.
3	01/07/2019	1021	3111	62	.	.
4	03/28/2019	4765	3576	.	.	.
5	10/25/2019	4767	3821	.	.	.
6	10/08/2018	4770	3332	.	.	.
7	05/23/2019	4770	3631	227	.	.
8	10/09/2019	4770	3798	.	139	.

[Ksharp · Accepted solution]

data Test;
format Episodedate mmddyy10.;
input ClientID IncidentID Episodedate mmddyy10.;
format Episodedate mmddyy10.;
cards;
4750 3119 03/05/2018
1021 3110 11/06/2018
1021 3111 01/07/2019
4765 3576 03/28/2019
4767 3821 10/25/2019
4770 3332 10/08/2018
4770 3631 05/23/2019
4770 3798 10/09/2019
;
run;

data want;
 set test;
 by ClientID notsorted;
 x=dif(Episodedate);
 if first.ClientID then do;n=0;call missing(x);end;
 n+1;
 if n=2 then repeat_inf_day2=x;
  else if n=3 then repeat_inf_day3=x;
drop x n;
run;

[Dhana18]

Thank you so much you made my day!!!