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;
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;
What is wrong with the code you show?
Please provide example data as working SAS data step code (Instructions)
I tried to run my code however, looks like it is in an infinite loop. Keeps running without completion.
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.
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;
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;
Can you show us a portion of the input data, and the output from this program?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: