topic Re: Flag a Member_ID with different Drug within 45 days of last Fill in SAS Programming

Flag a Member_ID with different Drug within 45 days of last Fill

PhatRam33 — Wed, 26 Oct 2022 14:57:16 GMT

Howdy all! I have a list of claims and I am trying to flag those claims where a member's next claim was within 45 days and for a different drug. Essentially, the ask is to look to see if a member has filled multiple drugs within 45 days.

This is the code I have built so far. Have commented out some of what I was testing:

Dummy sample (what is in bold needs to be flagged):

SRC_MEMBER_ID	FILL_DT	DRUG_NM	DRUG_CLASS	duration
11111111	17-Jan-22	DRUGA	MIGRAINE	.
11111111	8-Feb-22	DRUGA	MIGRAINE	22
11111111	9-Feb-22	DRUGB	MIGRAINE	1
22222222	10-Mar-22	DRUGC	MIGRAINE	29
22222222	25-Mar-22	DRUGC	MIGRAINE	15
22222222	19-Apr-22	DRUGD	MIGRAINE	25

proc sort data=work.tbl_dup_conseq_01b;

by src_member_id fill_dt drug_nm drug_class;

run;

data work.tbl_dup_conseq_02;

set work.tbl_dup_conseq_01b;

/*by src_member_id drug_nm drug_class;*/

duration = intck("day", lag(fill_dt), fill_dt);
do until(last.drug_nm);
if first.drug_nm and duration <= 45 then flag = "Y";
end;
/*drop duration*/;

run;

Re: Flag a Member_ID with different Drug within 45 days of last Fill

PaigeMiller — Wed, 26 Oct 2022 14:57:22 GMT

What is wrong with the code you show?

Please provide example data as working SAS data step code (Instructions)

Re: Flag a Member_ID with different Drug within 45 days of last Fill

PhatRam33 — Wed, 26 Oct 2022 14:58:54 GMT

I tried to run my code however, looks like it is in an infinite loop. Keeps running without completion.

Re: Flag a Member_ID with different Drug within 45 days of last Fill

Kurt_Bremser — Wed, 26 Oct 2022 15:13:55 GMT

do until(last.id);
if first.drug_nm and duration <= 45 then flag = "Y";
end;

Since nothing in the loop can change the value of last.id, this loop will never terminate when there is more than one observation for a drug_nm group. You must have the SET statement in such a DO loop for it to work.

Re: Flag a Member_ID with different Drug within 45 days of last Fill

PaigeMiller — Wed, 26 Oct 2022 15:16:18 GMT

Yes thats what happens when you comment out the BY statement, and then try to look for a value (last.drug_nm) that is produced via the BY statement.

Please in the future, when you know the symptoms of a problem, please don't withhold that information from us.

Re: Flag a Member_ID with different Drug within 45 days of last Fill

mkeintz — Wed, 26 Oct 2022 15:23:01 GMT

The apparent infinite loop is in the code:

do until(last.drug_nm);
  if first.drug_nm and duration <= 45 then flag = "Y";
end;

The loop will not stop until the last obs for a given drug has been encountered. But the loop does not actually proceed through the data, so the last.drug_nm condition will never occur.

Now this diagnosis is appropriate when the BY statement is uncommented. As it is (with the BY commented out), prior to you terminating the data step, you should get NOTEs on your log like

NOTE: Variable last.drug_nm is uninitialized.
NOTE: Variable first.drug_nm is uninitialized.

You can do without the loop entirely, with (untested code):

data work.tbl_dup_conseq_02;
  set work.tbl_dup_conseq_01b;
  by src_member_id drug_nm drug_class;

  duration = intck("day", lag(fill_dt), fill_dt);
  if first.drug_nm=1 and first.src_member_id=0 and duration<=45 then flag = "Y";
  drop duration;
run;

Editted addition: The BY statement should match the BY statement in the proc sort, i.e.

  by src_member_id fill_dt drug_nm drug_class;

Re: Flag a Member_ID with different Drug within 45 days of last Fill

PhatRam33 — Wed, 26 Oct 2022 16:16:43 GMT

Almost there. I tried the code below and it flags each line if duration is <= 45 regardless if member filled for a different drug or not. Trying to get it to flag where all the conditions are true. Same member, different drug, and <=45 days if that makes sense. Appreciate all the help.

data work.tbl_dup_conseq_02;
set work.tbl_dup_conseq_01b;
by src_member_id fill_dt drug_nm drug_class;

duration = intck("day", lag(fill_dt), fill_dt);
if first.drug_nm=1 and first.src_member_id=0 and duration<=45 then flag = "Y";
/*drop duration;*/
run;

Re: Flag a Member_ID with different Drug within 45 days of last Fill

PaigeMiller — Wed, 26 Oct 2022 16:57:41 GMT

Can you show us a portion of the input data, and the output from this program?

Re: Flag a Member_ID with different Drug within 45 days of last Fill

PhatRam33 — Wed, 26 Oct 2022 17:46:14 GMT

To add to my reply, I got the code to work by using the lag function for the drug_nm as well and did the trick to satisfy the required criteria.

data work.tbl_dup_conseq_02;
set work.tbl_dup_conseq_01b;
by src_member_id fill_dt drug_nm drug_class;

duration = intck("day", lag(fill_dt), fill_dt);
drug_nm2 = lag(drug_nm);
if drug_nm ^= drug_nm2 and first.src_member_id=0 and duration <= 45 and duration >=1 then flag = 'Y';
run;