Help! How do I capture the complicated concomitant medication pattern

[deleted_user]

Hello, all talented SAS colleagues out there:

I need your help! I'm currently doing project that evaluates the impact of polypharmacy--taking several meds concurrently to treat one condition. My data format is like the following:

ptid rxname rxdate day-supply
1 A 1/1/2008 30
1 A 2/5/2008 60
1 B 2/11/2008 30
1 C 3/1/2008 90
1 B 3/09/2008 30
1 D 4/1/2008 30
1 E 4/1/23008 30

I have been toiling my brain so hard to write the codes so that I could capture that the 2nd A rx days (rxdate+day-supply) overlapped with B+C+D+E, so between 2/05/2008 and 4/4/2008, this pt had 5 rxs concurrently.

my brain is fried, and it's writing out a big SOS.

any help/advice will be greatly appreciated!

dd08

[Cynthia_sas]

Hi:
I always find it useful, in these instances to ask what the descriptive statistic procedures will give me...for example, Proc Freq has the NLEVELS option that shows exactly what you want. For example, the Proc Freq output for PTID=1 using NLEVELS and ODS OUTPUT would be this table: WORK.RXLEV (based on the dummy data created below):
[pre]
Work.RXLEV created with ODS

Table
Obs ptid Var NLevels

1 1 rxname 5
2 2 rxname 2

[/pre]

In the above instance, the NLEVELS values shows you the number of unique prescriptions for PTID=1, which the cumulative frequency of 7 shows the number of observations for PTID=1.

There are other things that you can do, such as run an SQL query to get the distinct RXNAME values for each PTID. You can then run PROC FREQ on the table from PROC SQL or PROC TABULATE on the table from PROC SQL.

The output from the program below is too lengthy to post. However, the program does create data for 2 PTID values, so you can see what happens if you have multiple patients in your data.

If you run the program below, it gives you a variety of different ways to get either datasets or reports with the analysis you wanted. It is a place to start.

cynthia

[pre]
data rxdata;
infile datalines;
input ptid rxname $
rxdate : mmddyy10. day_supply;
return;
datalines;
1 A 1/1/2008 30
1 A 2/5/2008 60
1 B 2/11/2008 30
1 C 3/1/2008 90
1 B 3/09/2008 30
1 D 4/1/2008 30
1 E 4/1/2008 30
2 A 2/1/2008 30
2 A 3/5/2008 30
2 B 3/11/2008 30
2 B 4/1/2008 30
;
run;

ods noptitle;
options nocenter formchar='|-+-+++++-+';
proc print data=rxdata;
format rxdate mmddyy10.;
title 'what the data looks like';
run;

ods output nlevels=work.rxlev;
proc freq data=rxdata nlevels;
title 'Proc Freq with BY';
title2 'Mult Rows for PTID/RXNAME Combo';
by ptid;
tables rxname;
run;

proc print data=work.rxlev;
title 'Work.RXLEV created with ODS';
run;

proc sql;
create table work.cntrx as
select distinct rxname, ptid
from work.rxdata
order by ptid;
quit;

proc freq data=cntrx nlevels;
title 'Proc Freq after SQL';
title2 'only 1 row for rxname/ptid unique combo';
by ptid;
tables rxname;
run;

proc tabulate data=cntrx f=comma8.;
title 'Proc Tabulate after SQL';
class rxname ptid;
tables ptid*(rxname all),n;
run;
[/pre]

[deleted_user]

thank you Cythia for your prompt reply. I love what nlevel can do.
Unfortunately, just knowing the # of type of rxs for each patient is not enough for me. What 's more important for me is to know the timing relationship among all the rxs for each patient and determine which rxs actually overlapped. the example that I gave in the first post probably did not illustrate what I was looking for accurately. Here's a better example:

ptid rxname rxdate day_supply
1 A 1/1/2008 30
1 B 2/5/2008 30
1 C 3/1/2008 30
1 D 3/1/2008 30
1 E 3/15/2008 30

this patient had 5 types(levels) of rxs, but if we look closely, A did not overlap with B-E, instead, B overlapped with C and D, and then C and D overlapped with E.

so, in a dataset then included up to 10 different rxs, and the rxdates spans for at least 6 months, and day-supply ranges from 1 to 90, I don't know how to capture the overlapping patterns as illustrated above.

thanks again

diane

[Cynthia_sas]

Hi, Diane:
I understood where you were going. It is a problem that requires some advanced programming skills -- because you are not only concerned on the effect of A with the other RX ...but in B with all the others ...and C with all the others, etc.
So you need to first find out the start of RX and runout date of RX for each RX and PTID.

But, let's say that you could pin down a where clause that would help you find the information -- given your original data, for example....this WHERE clause would show what you want, assuming you knew (or could program a way for SAS to tell you the start and stop RX dates for PT 1, RX A, PT2, RX A, etc:
[pre]
proc freq data=rxdata nlevels;
title 'Proc Freq for dates of PTID/RXNAME Combo';
title2 "ptid = 1 for rxname = A, the";
title3 "start/runout dates were: 01Jan2008 and 05Apr2008";
where ptid = 1 and
rxdate between "01Jan2008 "d and "05Apr2008"d;
tables rxname;
run;

[/pre]

Now, this PROC FREQ and the NLEVELS will show you for THIS patient, for RX=A start and end dates, the number of interactions.

I'd end up using a big macro program, probably with a macro do loop to solve this. But, let me attempt to show this for 1 patient and 1 RX, how I'd approach the problem.

First, you have to "collapse" the data so you have the start date and runout date of each RX for each patient. I'd use a DATA Step program:
[pre]
** Make some data and calculate the ending date;
** or date that someone will run out of their RX;
data rxdata;
infile datalines;
input ptid rxname $
rxdate : mmddyy10. day_supply;
runout = rxdate + day_supply;
return;
datalines;
1 A 1/1/2008 30
1 A 2/5/2008 60
1 B 2/11/2008 30
1 C 3/1/2008 90
1 B 3/09/2008 30
1 D 4/1/2008 30
1 E 4/1/2008 30
2 A 2/1/2008 30
2 A 3/5/2008 30
2 B 3/11/2008 30
2 B 4/1/2008 30
;
run;

** Sort the data;
proc sort data=rxdata;
by ptid rxname rxdate runout;
run;

** Print the data;
ods listing;
ods noptitle;
options nocenter formchar='|-+-+++++-+';
proc print data=rxdata;
format rxdate mmddyy10. runout mmddyy10.;
title 'what the data looks like';
run;

** Now, capture the first_rxdate and the last_rxdate for each;
** patient and rxname. First_rxdate comes from the rxdate value;
** and Last_rxdate comes from the RUNOUT var that was calculated;
** above. This program will create one obs for every;
** PTID/RXNAME combo.;
data newrxdata (keep=ptid rxname first_rxdate last_rxdate);
set rxdata;
retain first_rxdate last_rxdate;
by ptid rxname;
retain first_rxdate last_rxdate;
if first.rxname then first_rxdate = rxdate;
if last.rxname then do;
last_rxdate = runout;
output;
first_rxdate = .;
last_rxdate = .;
end;
run;

** Print the new data to test;
proc print data=newrxdata;
format first_rxdate mmddyy10. last_rxdate mmddyy10.;
title 'Capture start and end dates for each RX';
run;
[/pre]

And, the output from the first PROC PRINT would be:
[pre]
what the data looks like

day_
Obs ptid rxname rxdate supply runout

1 1 A 01/01/2008 30 01/31/2008
2 1 A 02/05/2008 60 04/05/2008
3 1 B 02/11/2008 30 03/12/2008
4 1 B 03/09/2008 30 04/08/2008
5 1 C 03/01/2008 90 05/30/2008
6 1 D 04/01/2008 30 05/01/2008
7 1 E 04/01/2008 30 05/01/2008
8 2 A 02/01/2008 30 03/02/2008
9 2 A 03/05/2008 30 04/04/2008
10 2 B 03/11/2008 30 04/10/2008
11 2 B 04/01/2008 30 05/01/2008


[/pre]

and the output from the second proc print would be:

[pre]
Capture start and end dates for each RX 16:06 Monday, June 16, 2008 22

first_ last_
Obs ptid rxname rxdate rxdate

1 1 A 01/01/2008 04/05/2008
2 1 B 02/11/2008 04/08/2008
3 1 C 03/01/2008 05/30/2008
4 1 D 04/01/2008 05/01/2008
5 1 E 04/01/2008 05/01/2008
6 2 A 02/01/2008 04/04/2008
7 2 B 03/11/2008 05/01/2008

[/pre]

Now, I want to make 2 macro variables...one for my PTID of interest and a second one for my RX of interest:
[pre]
** Now, pick a patient ID and an RX;
** that you want to test.;
%let wantpt = 1;
%let wantrx = A;
[/pre]

Next, I want to build a query to go in and find the first_rxdate and the last_rxdate for that patient/RX combo. The result of that query will be 2 new macro variables: &rxstart and &rxstop ...but formatted as DATE9. so I can use them in a subsequent WHERE statement:
[pre]
** Get the starting and ending dates for;
** that single patient and RX;
proc sql;
select first_rxdate format=date9., last_rxdate format=date9.
into :rxstart, :rxend
from newrxdata
where ptid = &wantpt and
rxname = "&wantrx";
quit;

%put For pt &wantpt and RX= &wantrx;
%put The relevant dates are: &rxstart and &rxend;

[/pre]
And the results of the %PUT are:
[pre]
1089
1090 %put For pt &wantpt and RX= &wantrx;
For pt 1 and RX= A
1091 %put The relevant dates are: &rxstart and &rxend;
The relevant dates are: 01JAN2008 and 05APR2008

[/pre]

Now, I can use &rxstart and &rxstop and my &wantpt variables in a PROC FREQ. In this example, I create a work table for the information...but I also just do the PROC FREQ as a report, too:
[pre]

** Now, run the same PROC FREQ for just that Patient and RX;
** that you picked with the macro variables.;
** Using ODS Output still allows you to create a unique;
** dataset for that patient/RXname interaction.;
** Note that we are using information derived from the original;
** file for the macro variables and that the PROC FREQ information;
** is based on the original file.;
ods output nlevels=work.rxlev_PT_&wantpt._RX_&wantrx;
proc freq data=rxdata nlevels;
title 'Proc Freq for dates of PTID/RXNAME Combo';
title2 "ptid = &wantpt and rxname = &wantrx and";
title3 "all rxdate values between &rxstart and &rxend";
where ptid = &wantpt and
rxdate between "&rxstart"d and "&rxend"d;
tables rxname;
run;

** You may or may not want the data for a single;
** patient in a SAS dataset.;
proc print data=work.rxlev_PT_&wantpt._RX_&wantrx;
title "Work.rxlev_PT_&wantpt._RX_&wantrx created with ODS";
title2 "ptid = &wantpt and rxname = &wantrx and";
title3 "rxdate between &rxstart and &rxend";
run;

[/pre]

And the output from that final PROC PRINT is:
[pre]
Work.rxlev_PT_1_RX_A created with ODS
ptid = 1 and rxname = A and
rxdate between 01JAN2008 and 05APR2008

Table
Obs Var NLevels

1 rxname 5

[/pre]

So, if I go back and change the &WANTPT and the &WANTRX:
[pre]
%let wantpt = 2;
%let wantrx = A;
[/pre]
and run the program from the PROC SQL step forward, then I get different results for the PROC PRINT:
[pre]
Work.rxlev_PT_2_RX_A created with ODS
ptid = 2 and rxname = A and
rxdate between 01FEB2008 and 04APR2008

Table
Obs Var NLevels

1 rxname 2

[/pre]

Once you get this far and get this working for hard-coding the patient ID and RX, then you can look at further macroizing the solution so that you make a macro program that takes patient ID and RX for ALL the patients. At that point, I'd probably put all the output from PROC FREQ into 1 big dataset (because I assume that you do NOT want to make a bunch of 1 obs datasets).

But, figuring out how to macroize this into a re-callable macro program is another, more advanced topic.

cynthia

[jmsaluki]

Cynthia,

Thank you for this information. I have a current situation in which I need to find the out when a patient has filled their last prescription without a gap of 45 days or more. However, if that patient also has several refills prior to that gap (gap = date_dispensed - date_dispensed [prior] + days_supply [prior]) that were filled early, there is an excess supply problem. In other words if that patient had a gap of 46 days but actually had refilled their last prescription 3 days early, they would have the amount supplied plus the 3 days. This would then move their gap from 46 days to 43 and then they would not be counted in the group of discontinued patients.

What I am trying to do is find some code that will allow me to move forward and look backward to adjust the runout date (i.e. date_prescribed + days_supply) before moving on to the next row. Additionally, it would need to account for it being the last row as well as if the person only had one or two prescriptions filled.

I have tried a number of different loop approaches as well as processing each claim through an array but I am still having the same issues.

Any thoughts?

Thanks in advance.

jmsaluki

[Cynthia_sas]

Hi:
I'm really not all that familiar with patient data or the structure that you describe. Offhand, without diving deeply into the structure, it sounds like a problem that you'd solve using arrays. I think that to be really effective, you'd have to get the data from multiple observations for each patient/rx combo into 1 observation for each patient/rx combo.

Perhaps this paper will be of some help:
http://www2.sas.com/proceedings/forum2007/043-2007.pdf

cynthia

[abdullala]

data concrx;
input ptid rxname $ rxdate anydtdte. day_supply;
format rxdate date9.;
cards;
1 A 1/1/2008 30
1 A 2/5/2008 60
1 B 2/11/2008 30
1 C 3/1/2008 90
1 B 3/09/2008 30
1 D 4/1/2008 30
1 E 4/1/2008 30
;
run;

proc sql noprint;
create table concrx2 as
select a.*
, min(rxdate) as startdt format=date9., max(rxdate) as enddt format=date9.
, calculated enddt - calculated startdt as dspan
, calculated enddt - rxdate as day_ramain
, case when day_supply>= calculated day_ramain then 1
else . end as coverflg
from concrx a
group by ptid
order by ptid, rxdate;
quit;

it's a little late and let me know if this is what expected.

[Pooja]

Hello dd08,

I was wondering if you found a solution to this problem. I have a similar issue and an looking for a solution.

Thanks,

Pooja

[Haikuo]

Hopefully this is close to what you are after:

/*raw exsiting data*/

data rxdata;

  infile datalines;

  input ptid rxname $

        rxdate : mmddyy10. day_supply;

datalines;

1 A 1/1/2008 30

1 A 2/5/2008 60

1 B 2/11/2008 30

1 C 3/1/2008 90

1 B 3/09/2008 30

1 D 4/1/2008 30

1 E 4/1/2008 30

2 A 2/1/2008 30

2 A 3/5/2008 30

2 B 3/11/2008 30

2 B 4/1/2008 30

;

run;

/*to get the rx period for ptid drilling down to date*/

data want1;

  do until (last.ptid);

     set rxdata;

     by ptid rxdate;

   if last.ptid then rxend=rxdate+day_supply;

  end;

  do until (last.ptid);

    set rxdata;

   by ptid rxdate;

      if first.ptid then

     do dt=rxdate by 1 to rxend;

   output;

  end;

  end;

  keep ptid dt;

  run;

  /*flag each date with the concurrent rx, the dimension of the temp array is equal to the number of types of rxname, this is the KEY step*/

  data want2;

     if _n_=1 then do;

     if 0 then set rxdata;

  declare hash h(dataset:'rxdata', multidata:'y');

  h.definekey('ptid');

  h.definedata(all:'y');

  h.definedone();

   end;

   set want1;

    length concrx $50.;

    array con(10) $1. _temporary_;

     do rc=h.find() by 0 while (rc=0);

    if rxdate <= dt <= rxdate+day_supply and RXNAME not in con then do;

      i+1;

             con(i)=rxname;

  end;

    rc=h.find_next();

  end;

  call sortc (of con(*));

  concrx=cats(of con(*));

  call missing (of con(*));

  i=0;

  format dt date9.;

  keep ptid dt concrx;

  run;

  /*report, you could also choose outputting to table, for further analysis*/

  proc freq data=want2;

  by ptid;

  table concrx;

  run;

Haikuo

[lillymaginta]

Hi Haikuo, I know this is avery old post but I was wondering for your code, you counted the same comb more than once in different ways such as abc, abcd, would it be possible to count the combination only once. So if ptid used abcd together, can you adjust the code to list this as only abcd? also could the frequency be adjusted to reflect that the patient had only 1 combination of these med?