BookmarkSubscribeRSS Feed
Allegra
Calcite | Level 5

 

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.

 

 

 

 

 

 

 

 

 

10 REPLIES 10
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller
novinosrin
Tourmaline | Level 20

Sir @PaigeMiller  Is this OPs expected sample1 output correct?

 

3

591261205

1

4

3

591261205

1

3

 

@Allegra   Can you clarify plz

Allegra
Calcite | Level 5

@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
Tourmaline | Level 20

@Allegra  Thank you. I appreciate it

novinosrin
Tourmaline | Level 20

@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
Calcite | Level 5

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
Calcite | Level 5

@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
Diamond | Level 26

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

--
Paige Miller
Allegra
Calcite | Level 5

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
Diamond | Level 26

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 1703 views
  • 0 likes
  • 3 in conversation