topic Re: Identifying the next date observation in data step, help in SAS Programming

Identifying the next date observation in data step, help

suncawy — Fri, 05 Dec 2014 17:38:01 GMT

Hello,

I can't think of a solution. I'm a HEDIS measure analysis, where we have to track when a patient has been on 2 or more medications during the year. I've been able to identify

the first sequence where they were on the meds, but I'm having problems getting the subsequent dates when they were on it. I just can't seem to think of a way to get to it (having "writers block").

So, the data looks like this:

PatID Med Disp dt Run Out date

A abilify 1/7/2014 4/17/2014

A latuda 1/22/2014 7/5/2014

A abilify 5/16/2014 6/17/2014

A latuda 5/20/2014 10/17/2014

A risp 8/1/2014 9/2/2014

B clan 2/20/2014 5/2/2014

B zipra 2/24/2014 4/24/2014

B risp 5/6/2014 6/7/2014

B clan 6/10/201 7/15/2014

The final data should have these data in it:

PatID Med Disp dt Run Out date

A abilify 1/7/2014 4/17/2014

A latuda 1/22/2014 7/5/2014

A abilify 5/16/2014 6/17/2014

A latuda 5/20/2014 10/17/2014

B clan 2/20/2014 5/2/2014

B zipra 2/24/2014 4/24/2014

The patients where they had 2 or more meds during the year and excluding where they didn't.

Any ideas ? Thank you in advance.

Re: Identifying the next date observation in data step, help

Reeza — Fri, 05 Dec 2014 18:10:15 GMT

1. Create your subset table of where you have the duplicate drugs per ID

2. Join back to original table

You can do it in a single sql, but I'll leave that step to you.

proc sql;

create table multiple_drugs as

select patid, med

from have

group by patid, med

having count(med)>=2;

quit;

proc sql;

create table detail_table as

select a.*

from have a

join multiple_drugs b

on a.patid=b.patid

and a.med=b.med;

quit;

Re: Identifying the next date observation in data step, help

PGStats — Fri, 05 Dec 2014 18:38:09 GMT

Are you looking for periods where patients were on two medications at the same time or for years where patients have taken two or more medications? - PG

Re: Identifying the next date observation in data step, help

suncawy — Fri, 05 Dec 2014 18:53:20 GMT

It would be periods where patients were on two medications at the same time. For example, patient A is on 2 medications from about January through April. Then there could be break and the patient starts on 2 meds in May. I can get the first instance where this occurs, but I can't seem to think of a way to get the subsequent periods.

Re: Identifying the next date observation in data step, help

PGStats — Fri, 05 Dec 2014 19:39:15 GMT

You could start with something like:

proc sql;

create table drugCombinations as

select a.PatID,

catx(" - ", a.Med, b.Med) as drugCombination,

max(a.DispDT, b.DispDT) as startDT format=yymmdd.,

min(a.RODate, b.RODate) as endDT format=yymmdd.,

intck("DAY", calculated startDT, calculated endDT) as nbDays

from

have as a inner join

have as b

on a.PatID=b.PatID and a.Med ne b.Med and

a.DispDT <= b.DispDT and a.RODate >= b.DispDT;

quit;

Re: Identifying the next date observation in data step, help

Reeza — Fri, 05 Dec 2014 20:09:33 GMT

"at the same time" changes the question.

One common approach to this is to create a "medicine cabinet" on a daily basis for each patient.

This is data intensive, but allows you to identify patients on multiple medications as well as the exact overlap dates of interest.

To create the medicine cabinet, create a record for each day of the medication.

data med_cabinet;

set have;

do date=disp_dt to run_out_date;

output;

end;

run;

Re: Identifying the next date observation in data step, help

PGStats — Sat, 06 Dec 2014 04:50:56 GMT

You can get away with caring only about the change dates (DispDT and RODate) and keeping track of the medication:

data have;

length PatID Med $16;

format DispDT RODate yymmdd.;

input PatID $ Med DispDT : mmddyy. RODate : mmddyy.;

datalines;

A abilify 1/7/2014 4/17/2014

A latuda 1/22/2014 5/7/2014

A abilify 5/16/2014 6/17/2014

A latuda 5/20/2014 10/17/2014

A risp 8/1/2014 9/2/2014

B clan 2/20/2014 5/2/2014

B zipra 2/24/2014 4/24/2014

B risp 5/6/2014 6/7/2014

B clan 6/10/2014 7/15/2014

;

data changes;

set have;

format chDT yymmdd10.;

ch = 1; chDT = DispDT; output;

ch = -1; chDT = intnx("DAY", RODate, 1); output;

keep PatID Med ch chDT;

run;

proc sort data=changes; by PatID chDT descending ch; run;

data history;

length Meds $100;

array m{20} $16;

format hStart hEnd yymmdd10.;

do until(last.PatID);

set changes; by PatID;

if medCount > 0 then do;

hEnd = intnx("DAY", chDT, -1);

days = intck("DAY", hStart, hEnd) + 1;

output;

end;

hStart = chDT;

if ch=1 then m{1} = Med;

else m{whichc(Med, of m{*})} = "";

call sortc(of m{*});

meds = catx(" ", of m{*});

MedCount = sum(MedCount, ch);

end;

if medCount > 0 then do;

call missing(hEnd, days);

output;

end;

keep PatID Meds hStart hEnd MedCount days;

run;

proc print dta=history noobs; var PatID hStart hEnd days MedCount Meds; run;

Pat med

ID hStart hEnd days Count Meds

A 2014-01-07 2014-01-21 15 1 abilify

A 2014-01-22 2014-04-17 86 2 abilify latuda

A 2014-04-18 2014-05-07 20 1 latuda

A 2014-05-16 2014-05-19 4 1 abilify

A 2014-05-20 2014-06-17 29 2 abilify latuda

A 2014-06-18 2014-07-31 44 1 latuda

A 2014-08-01 2014-09-02 33 2 latuda risp

A 2014-09-03 2014-10-17 45 1 latuda

B 2014-02-20 2014-02-23 4 1 clan

B 2014-02-24 2014-04-24 60 2 clan zipra

B 2014-04-25 2014-05-02 8 1 clan

B 2014-05-06 2014-06-07 33 1 risp

B 2014-06-10 2014-07-15 36 1 clan

Note: I changed your data slightly to remove the overlap in the dates for the drug latuda for patient A. If such date overlaps exist, I wouldn't know how to handle them.

Message was edited by: PG Removed the _TEMPORARY_ option in the array declaration as this implies that the array is retained, which would cause errors in certain circumstances.

Re: Identifying the next date observation in data step, help

art297 — Sat, 06 Dec 2014 15:10:57 GMT

Expanding on 's idea, the following appears to produce the file you wanted. However, you didn't select risp for PatID A, although it did overlap with latuda.

data have;

input (PatID Med) ($) (Disp_dt Run_Out_date) (: mmddyy10.);

format Disp_dt Run_Out_date mmddyy10.;

cards;

A abilify 1/7/2014 4/17/2014

A latuda 1/22/2014 7/5/2014

A abilify 5/16/2014 6/17/2014

A latuda 5/20/2014 10/17/2014

A risp 8/1/2014 9/2/2014

B clan 2/20/2014 5/2/2014

B zipra 2/24/2014 4/24/2014

B risp 5/6/2014 6/7/2014

B clan 6/10/2014 7/15/2014

;

data med_cabinet;

set have;

do date=disp_dt to run_out_date;

output;

end;

run;

proc sort data=med_cabinet nodupkey;

by PatID date Med;

run;

proc sql;

create table want as

select PatID,Med,Disp_dt,Run_Out_date

from med_cabinet

group by patID,date

having count(distinct med) ge 2

;

quit;

proc sort data=want nodupkey;

by PatID Med Disp_dt Run_Out_date;

run;

Re: Identifying the next date observation in data step, help

suncawy — Sat, 06 Dec 2014 15:14:48 GMT

I'm going have to give this a try. I think what you have is what I'm looking for. This is very helpful and its something, I don't think I could have come up with.

Re: Identifying the next date observation in data step, help

jakarman — Sat, 06 Dec 2014 17:16:37 GMT

If you are working in sas-datasets you can work using different pointers locations. Indexes on reference datasets is an option.

Within smaller environments often not worth the effort.

Re: Identifying the next date observation in data step, help

suncawy — Fri, 12 Dec 2014 16:32:54 GMT

Everyone thanks for the help. Now, there's got to be a way to identify it when the dates are overlapping like this, with multiple dispensed dates on the same drug:

Patient Med Disp_dt Run_Out_date

A abilify 1/23/2014 3/20/2014

A abilify 2/19/2014 4/19/2014

A abilify 3/21/2014 5/19/2014

A abilify 4/20/2014 6/26/2014

A Risp 1/2/2014 2/22/2014

A Risp 1/24/2014 4/1/2014

A Risp 3/3/2014 4/23/2014

A Risp 3/25/2014 5/26/2014

The next task would be to identify when the patient was on 2 meds at the same time for 90 days or more and if it was less than 90 days, then when was the next time the patient was on 2 days. So, it seems like I should do some sort of looping, but not sure how.

Re: Identifying the next date observation in data step, help

PGStats — Fri, 12 Dec 2014 17:01:26 GMT

What does it mean exactly when the dates overlap for a given drug and patient. Are those simply cases where the patient renewed his/her prescription before being out of the previous supply? - PG

Re: Identifying the next date observation in data step, help

suncawy — Fri, 12 Dec 2014 17:25:18 GMT

Actually, after reviewing the data, the solution you provided earlier with tracking the change dates is the right solution. I looked at the data more closely and appears to solve the issue. What I did was take the max and min disp_dt by each drug id and applied your solution. It actually covered all the prescriptions. Yes, your right. The patient could have multiple dates where they refilled the same medication, then was started on another medication at the same time. Then, the patient was taken off the or stopped the med, then started again at a later date. The solution you provided actually worked once I was able to get the dataset into the max and min disp_dt as I mentioned. It covers all the medications for the patient.