topic Re: Summing consecutive events by patient id and date in SAS Programming

Summing consecutive events by patient id and date

Allegra — Wed, 01 Apr 2020 23:20:33 GMT

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.

Re: Summing consecutive events by patient id and date

PaigeMiller — Mon, 09 Sep 2019 20:13:06 GMT

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.

Re: Summing consecutive events by patient id and date

novinosrin — Mon, 09 Sep 2019 20:35:18 GMT

Sir @PaigeMiller Is this OPs expected sample1 output correct?

3	591261205	1	4
3	591261205	1	3

@Allegra Can you clarify plz

Re: Summing consecutive events by patient id and date

Allegra — Mon, 09 Sep 2019 21:12:50 GMT

@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

Re: Summing consecutive events by patient id and date

novinosrin — Mon, 09 Sep 2019 21:10:39 GMT

@Allegra Thank you. I appreciate it

Re: Summing consecutive events by patient id and date

Allegra — Mon, 09 Sep 2019 21:18:40 GMT

@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

Re: Summing consecutive events by patient id and date

novinosrin — Tue, 10 Sep 2019 00:19:21 GMT

@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

Re: Summing consecutive events by patient id and date

PaigeMiller — Tue, 10 Sep 2019 12:01:12 GMT

@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?

Re: Summing consecutive events by patient id and date

Allegra — Tue, 10 Sep 2019 15:10:54 GMT

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.

Re: Summing consecutive events by patient id and date

Allegra — Tue, 10 Sep 2019 15:24:53 GMT

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:)

Re: Summing consecutive events by patient id and date

PaigeMiller — Tue, 10 Sep 2019 15:29:56 GMT

@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;