count number of times an event occurs 3 times within a two week period and capture the date

[Informatician]

I have provided a deidentified screenshot with dummy values to demonstrate my data. I can't figure out how to identify 3 dates that fall within a 14 day period of time for each patient for a specific medication. Please note that some patients have more than 100 occurrences for a given medication (i.e. >100 dates). 

For example, starting with patient 34444862 obs=3-7 (table long) and/or obs=6 (table wide). I would like to capture 27APR2018 (obs=7) as the only time Bupropion occurred three times within a 14 day period.

For patient 34443625 obs=12-26 (table long) and/or obs=11 (table wide). I would like to capture 03APR2018 as the first time 3 Sertralines occurred within 14 days. I also need to capture 06APR2018, 10APR2018, 13APR2018, and 16APR2018 as the next dates.

I have tried using arrays (table wide) and variations of the dif() function (table long) and I am banging my head against a wall with a looming deadline. Any help you can provide is greatly appreciated! Thank you in advance.

I am using SAS 9.4 and I have attached the dummy data.

[AMSAS]

Hi,

This can be achieved using first. & last. processing in the datastep

I've provided an example using a subset of your long file below

Andy

data long ;
		format date date. ;
		infile cards ;
		input 
			patientId 
			treatment : $32.
			date : date. ;
cards; 
34444486	FLUOXETINE	4-Apr-18
34444486	FLUOXETINE	14-Apr-18
34444862	BUPROPION	1-Apr-18
34444862	BUPROPION	4-Apr-18
34444862	BUPROPION	20-Apr-18
34444862	BUPROPION	27-Apr-18
34444862	BUPROPION	27-Apr-18
34443348	OLANZAPINE	9-Apr-18
34443348	OLANZAPINE	10-Apr-18
34443348	OLANZAPINE	29-Apr-18
34443399	TRAZODONE	12-Apr-18
34443625	SERTRALINE	1-Apr-18
34443625	SERTRALINE	2-Apr-18
34443625	SERTRALINE	3-Apr-18
34443625	SERTRALINE	4-Apr-18
34443625	SERTRALINE	5-Apr-18
34443625	SERTRALINE	6-Apr-18
34443625	SERTRALINE	7-Apr-18
34443625	SERTRALINE	8-Apr-18
34443625	SERTRALINE	10-Apr-18
34443625	SERTRALINE	11-Apr-18
34443625	SERTRALINE	12-Apr-18
34443625	SERTRALINE	13-Apr-18
34443625	SERTRALINE	14-Apr-18
34443625	SERTRALINE	15-Apr-18
34443625	SERTRALINE	16-Apr-18
34443874	FLUOXETINE	7-Apr-18
34443874	OLANZAPINE	7-Apr-18
34443232	BUPROPION	6-Apr-18
34443232	BUPROPION	8-Apr-18
34443232	BUPROPION	10-Apr-18
34443232	BUPROPION	11-Apr-18
34443232	BUPROPION	12-Apr-18
34443232	BUPROPION	14-Apr-18
34443232	BUPROPION	15-Apr-18
34443232	BUPROPION	15-Apr-18
34443232	BUPROPION	16-Apr-18
34443232	BUPROPION	17-Apr-18
34443232	BUPROPION	21-Apr-18
34443232	BUPROPION	22-Apr-18
34443232	BUPROPION	29-Apr-18
34443232	BUPROPION	29-Apr-18
;
run ;

proc sort ;
	by patientId treatment date ;
run  ;

data alerts ;
	retain 
		treatmentCount 
		firstDate ;
	set long;
	by patientId treatment date ;
	/* first.treatment is set to 1 when it's the first occurance of a new treatment or first occurance of a new patientID */
	if first.treatment or treatmentCount=3 then do ;
		treatmentCount=1 ;
		firstDate=date ;
		put treatmentCount= patientId= treatment= firstDate= date. date= ;

	end ;
	/* If it is not the first occurance of a treatment or patientID */
	else do ;
		put treatmentCount= patientId= treatment= firstDate= date. date= ;
		/* Is the current treatment date within 14 days of the first treatment date */
		if date-firstDate<14 then do ;
			/* increase treatmentCounter */
			treatmentCount=treatmentCount+1 ;
			/* If is the 3rd treatment within 14 days */
			if treatmentCount=3 then do ;
				put ">>>" treatmentCount= patientId= treatment= firstDate= date. date= ;
				output ;
			end ;
		end ;
		else
			treatmentCount=1 ;
			firstDate=date ;
	end ;


			
run ;

[AMSAS]

Sorry, just realized there's a situation that this doesn't work for.

[AMSAS · Accepted solution]

Hi,

Here's updated code, that I believe will solve the problem. I'm making use of BY group processing (Last./First.) referenced in the earlier post and the LAG function 

In the code below I added a check variable which I manually set to 1 for records that meet your required output, as I understand it.

Test the code and please confirm if it solves your question

data long ;
		format date date. ;
		infile cards ;
		input 
			patientId 
			treatment : $32.
			date : date. 
			check $ ;
cards; 
00000001    TEST        1-Apr-18  0
00000001    TEST        5-Apr-18  0
00000001    TEST        15-Apr-18 0
00000001    TEST        16-Apr-18 1
00000001    TEST        30-Apr-18 0
00000001    TEST        05-May-18 0 
00000001    TEST        15-May-18 0
00000001    TEST        18-May-18 1
34444486	FLUOXETINE	4-Apr-18  0
34444486	FLUOXETINE	14-Apr-18 0
34444862	BUPROPION	1-Apr-18  0
34444862	BUPROPION	4-Apr-18  0
34444862	BUPROPION	20-Apr-18 0
34444862	BUPROPION	27-Apr-18 0
34444862	BUPROPION	27-Apr-18 1
34443348	OLANZAPINE	9-Apr-18  0
34443348	OLANZAPINE	10-Apr-18 0
34443348	OLANZAPINE	29-Apr-18 0
34443399	TRAZODONE	12-Apr-18 0
34443625	SERTRALINE	1-Apr-18  0
34443625	SERTRALINE	2-Apr-18  0
34443625	SERTRALINE	3-Apr-18  1
34443625	SERTRALINE	4-Apr-18  0  
34443625	SERTRALINE	5-Apr-18  0
34443625	SERTRALINE	6-Apr-18  1
34443625	SERTRALINE	7-Apr-18  0
34443625	SERTRALINE	8-Apr-18  0
34443625	SERTRALINE	10-Apr-18 1
34443625	SERTRALINE	11-Apr-18 0
34443625	SERTRALINE	12-Apr-18 0 
34443625	SERTRALINE	13-Apr-18 1
34443625	SERTRALINE	14-Apr-18 0 
34443625	SERTRALINE	15-Apr-18 0
34443625	SERTRALINE	16-Apr-18 1
34443874	FLUOXETINE	7-Apr-18  0
34443874	OLANZAPINE	7-Apr-18  0
34443232	BUPROPION	6-Apr-18  0
34443232	BUPROPION	8-Apr-18  0
34443232	BUPROPION	10-Apr-18 1
34443232	BUPROPION	11-Apr-18 0
34443232	BUPROPION	12-Apr-18 0 
34443232	BUPROPION	14-Apr-18 1
34443232	BUPROPION	15-Apr-18 0
34443232	BUPROPION	15-Apr-18 0
34443232	BUPROPION	16-Apr-18 1
34443232	BUPROPION	17-Apr-18 0
34443232	BUPROPION	21-Apr-18 0
34443232	BUPROPION	22-Apr-18 1
34443232	BUPROPION	29-Apr-18 0 
34443232	BUPROPION	29-Apr-18 0
;
run ;

proc sort ;
	by patientId treatment date ;
run  ;

data alerts ;
	retain 
		treatmentCount 
		firstDate ;
	set long ;
	*put "S : " treatmentCount= patientId= treatment= firstDate= date. date= ;
	patientIdL2=lag2(patientID) ;
	treatmentL2=lag2(treatment) ;
	dateL2     =lag2(date) ;
	patientIdL1=lag1(patientID) ;
	treatmentL1=lag1(treatment) ;
	dateL1     =lag1(date) ;
	by patientId treatment date ;
	/* first.treatment is set to 1 when it's the first occurance of a new treatment or first occurance of a new patientID */
	if first.treatment or treatmentCount=3 then do ;
		treatmentCount=1 ;
		firstDate=date ;

	end ;
	/* If it is not the first occurance of a treatment or patientID */
	else do ;
		/* Is the current treatment date within 14 days of the first treatment date */
		if date-firstDate<14 then do ;
			*put "<14" date 8. firstdate 8. ;
			/* increase treatmentCounter */
			treatmentCount=treatmentCount+1 ;
			/* If it is the 3rd treatment within 14 days */
			if treatmentCount=3 then do ;
				*put ">>>" treatmentCount= patientId= treatment= firstDate= date. date= ;
				output ;
			end ;
		end ;
		if date-firstDate>=14 then do ;
			*put ">=14" date 8. firstdate 8. ;
			*put dateL1= date. dateL2= date. ;
			if patientIdL2=patientId and 
				treatmentL2=treatment and 
				date-dateL2<14 then do ;
				treatmentCount=2 ;
				firstDate=dateL2 ;
			end ;
			else if patientIdL1=patientId and 
				treatmentL1=treatment and 
				date-dateL1<14 then do ;
				treatmentCount=2 ;
				firstDate=dateL1 ;
			end ;
		end ;
	end ;
	*put "E : " treatmentCount= patientId= treatment= firstDate= date. date= ;


			
run ;

[Informatician]

Thank you!