Summing consecutive events by patient id and date

[Allegra]

Hello,

Please, how do I

First step: sum up consecutive flag variable by id, date, and NDC code? 

Second step: Collapse by id and flag. For patients that have both '0' and '1' flag, I want to keep only their '1' and then sum up all consecutive '1's as a variable called duration.

[PaigeMiller]

proc sort data=have;
	by id ndc date;
run;
data want;
	set have;
	by id ndc;
	if first.ndc then duration=0;
	duration+flag;
	if duration=0 or last.ndc then output;
run;
proc summary data=want nway;
    class id flag;
	var duration;
	output out=want2 sum=;
run;

Want2 may have a few unwanted records with FLAG=0, but you can get rid of those.

[novinosrin]

Sir @PaigeMiller  Is this OPs expected sample1 output correct?

3	591261205	1	4
3	591261205	1	3

@Allegra   Can you clarify plz

[Allegra]

@novinosrin yes sample 1 expected output is correct because the dates are not consecutive even though the NDC is the same. ie there's a break from 12dec2013 to 19june2014. 

@PaigeMiller code collapses those two dates, so it is not correct for that ID

[novinosrin]

@Allegra  Thank you. I appreciate it

[novinosrin]

@Allegra This original sample is still confusing

3 09DEC2013 591261205 1
3 10DEC2013 591261205 1
3 11DEC2013 591261205 1
3 12DEC2013 591261205 1
3 19JUN2014 591320205 1

 591261205 4 makes sense 

591261205 3

however on 19 the NDC is 591320205 which different, so i'm wondering where you got the 3 from

[Allegra]

The '3' is not added to the previous one even though they have the same NDC code because of the date variable. The patient had a prescription  in 12DEC2013 and the next one was on 19JUN2014 THROUGH 21JUNE2014 (3 DAYS). Because there is almost a 6months lapse between Dec and June, the dates are not consecutive. 

[Allegra]

@PaigeMiller Thank you for your swift response. For ID, it summed up the non-consecutive events with same ndc into 5 is there a way to make that a distinction into 3 & 2?

Thank you for your help

[PaigeMiller]

---

@Allegra wrote:

Hello,

Please, how do I

First step: sum up consecutive flag variable by id, date, and NDC code? 

---

What do you mean by "consecutive"? Consecutive days, or consecutive records in the file, or something else?

[Allegra]

I meant consecutive days. Taking Patient 3 as an example:

1st prescription: 31AUG2013: NO FLAG

2nd prescription to 5th prescription: (NDC: 591261205) 09DEC2013 TO 12DEC2013 ie no break-in days which equates to 4 days.

6th prescription to 8th:  (NDC: 591261205) 19JUNE2014 TO 21STJUNE2014 ie no break in days which equates to 3 days

Question: why is duration not  7days because of same NDC?

Explanation: 5th prescription was taken 12DEC2013 while 6th prescription was on 19JUNE2014 (a gap of almost 6 months), thus I want to keep them separate.

If patient 3, however, had 6th to 8th prescription from 13DEC2013 TO 15DEC2013 for that same NDC, Duration would have been added to 7days.

Sorry, English is not my first language, so I am trying to explain as best as I can.

Again, thank you for your time:)

[PaigeMiller]

---

@Allegra wrote:

I meant consecutive days. Taking Patient 3 as an example:

1st prescription: 31AUG2013: NO FLAG

2nd prescription to 5th prescription: (NDC: 591261205) 09DEC2013 TO 12DEC2013 ie no break-in days which equates to 4 days.

6th prescription to 8th:  (NDC: 591261205) 19JUNE2014 TO 21STJUNE2014 ie no break in days which equates to 3 days

Question: why is duration not  7days because of same NDC?

Explanation: 5th prescription was taken 12DEC2013 while 6th prescription was on 19JUNE2014 (a gap of almost 6 months), thus I want to keep them separate.

If patient 3, however, had 6th to 8th prescription from 13DEC2013 TO 15DEC2013 for that same NDC, Duration would have been added to 7days.

Sorry, English is not my first language, so I am trying to explain as best as I can.

Again, thank you for your time:)

---

proc sort data=have;
	by id ndc date;
run;
data want;
	set have;
	by id ndc;
        prev_date=lag(date);
	if first.ndc or date-prev_date>1 then duration=0;
	duration+flag;
	if duration=0 or last.ndc or date-prev_date>1 then output;
        drop prev_date;
run;