row group within row group / define combined unit

[Cruise]

Hi guys,

I have Medicaid drug claim data from which I'm trying to identify combined pills. FDA doesn't assign unique National Drug Code (NDC) to combo drugs such as FOLFOX. In order providers are paid for they put in claims for ingredient drugs (leucovorin, platin and fluoracil) in the combo (FOLFOX). So, if  these three specific ingredient drugs claimed in the same day then I define it as was FOLFOX.

Few rules and notes:

- Data has unique patient IDs and each patient has multiple drugs ordered in different time periods. So my grouping is double. First patients and then ser_date which is the day when drugs are claimed by hospitals to Medicaid.

- Define as combined pill if drugs are ordered in the same day (ser_date) . In other words, if individual content drugs in FOLFOX is ordered in same day (ser-date) then take this order as a combined pill.

- Combined pill contents overlap, therefore, define more diverse ingredient drug, ie., FOLFOX (3 ingredients) first but not 5FULV (2 ingredients included in FOLFOX); by the same token, define FOLFIRI first but not TRIAL2000;

-If multiple combinations are identified, ie., 5FULV and CAPIRI then define both.

I'll highly appreciate any input of yours.

Thanks in advance.

proc sql;
create table comb_defined as 
select distinct_diplay_id,
case when (if z_leuco=1 and z_pl=1 and z_fluor=1 then 1 else 0 end) as _FOLFOX,
case when (if z_leuco=1 and z_tecan=1 and z_fluor=1 then 1 else 0 end) as _FOLFIRI,
case when (if z_tab=1 and z_pl=1 then 1 else 0 end) as _CAPEOX, 
case when (if z_leuco=1 and z_fluor=1 and z_tecan=1 and z_pl=1 then 1 else 0 end)as _FOLFOXIRI, 
case when (if z_tecan=1 and z_pl=1 then 1 else 0 end) as _IROX=1, 
case when (if z_leuco=1 and z_fluor=1 then 1 else 0 end) as _5FULV,
case when (if z_tab=1 and z_tecan=1 then 1 else 0 end) as _CAPIRI,
case when (if z_leuco=1 and z_tecan=1 then 1 else 0 end) as _TRIAL2000
from comb
group by display_id, ser_date;
quit;

data comb; length CHEMOCAT2 $30;
input patient_id ser_date anydtdte12. CHEMOCAT2 $ z_pl z_tab z_leuco z_tecan z_fluor count;
cards;
2	12-Feb-13	DEXAMETHASONE	0	0	0	0	0	7
2	12-Feb-13	FLUOROURACIL	0	0	0	0	1	7
2	12-Feb-13	LEUCOVORIN		0	0	1	0	0	7
2	12-Feb-13	OXALIPLATIN		1	0	0	0	0	7
2	14-Feb-13	BEVACIZUMAB		0	0	0	0	0	7
2	13-Dec-13	CETUXIMAB		0	0	0	0	0	7
2	13-Dec-13	IRINOTECAN		0	0	0	1	0	7
29	11-Apr-05	CAPECITABINE	0	1	0	0	0	5
29	27-Apr-05	DEXAMETHASONE	0	0	0	0	0	5
29	11-Jul-05	ALLOPURINOL		0	0	0	0	0	5
29	15-Feb-06	IRINOTECAN		0	0	0	1	0	5
29	5-Nov-06	MEGESTROL		0	0	0	0	0	5
30	23-May-05	CAPECITABINE	0	1	0	0	0	5
30	27-Jul-06	FLUOROURACIL	0	0	0	0	1	5
30	27-Jul-06	IRINOTECAN		0	0	0	1	0	5
30	27-Jul-06	LEUCOVORIN		0	0	1	0	0	5
30	31-Aug-06	DEXAMETHASONE	0	0	0	0	0	5
56	6-Apr-05	FLUOROURACIL	0	0	0	0	1	5
56	7-Apr-05	IRINOTECAN		0	0	0	1	0	5
56	7-Apr-05	LEUCOVORIN		0	0	1	0	0	5
56	10-Jan-06	CAPECITABINE	0	1	0	0	0	5
56	8-Aug-08	DEXAMETHASONE	0	0	0	0	0	5
75	11-May-05	DEXAMETHASONE	0	0	0	0	0	6
75	6-Dec-05	FLUOROURACIL	0	0	0	0	1	6
75	6-Dec-05	IRINOTECAN		0	0	0	1	0	6
75	6-Dec-05	LEUCOVORIN		0	0	1	0	0	6
75	20-Sep-06	MEGESTROL		0	0	0	0	0	6
75	4-Sep-07	CAPECITABINE	0	1	0	0	0	6
117	13-May-09	MEGESTROL		0	0	0	0	0	5
117	29-Jul-09	FLUOROURACIL	0	0	0	0	1	5
117	29-Jul-09	LEUCOVORIN		0	0	1	0	0	5
117	10-Sep-09	DEXAMETHASONE	0	0	0	0	0	5
117	12-Oct-09	IRINOTECAN		0	0	0	1	0	5
118	14-Apr-05	OCTREOTIDE		0	0	0	0	0	5
118	6-May-05	CAPECITABINE	0	1	0	0	0	5
118	10-Oct-05	CYCLOPHOSPHAMIDE 0	0	0	0	0	5
118	18-Nov-05	ETOPOSIDE	0	0	0	0	0	5
118	28-Nov-05	MEGESTROL	0	0	0	0	0	5
148	13-Apr-05	MEGESTROL	0	0	0	0	0	5
148	16-Nov-05	DEXAMETHASONE	0	0	0	0	0	5
148	16-Nov-05	FLUOROURACIL	0	0	0	0	1	5
148	16-Nov-05	IRINOTECAN	0	0	0	1	0	5
148	16-Nov-05	LEUCOVORIN	0	0	1	0	0	5
175	13-Apr-05	FLUOROURACIL	0	0	0	0	1	5
175	13-Apr-05	IRINOTECAN	0	0	0	1	0	5
175	13-Apr-05	LEUCOVORIN	0	0	1	0	0	5
175	15-Apr-05	DEXAMETHASONE	0	0	0	0	0	5
175	2-Jun-06	CAPECITABINE	0	1	0	0	0	5
191	12-Apr-05	DEXAMETHASONE	0	0	0	0	0	6
191	12-Apr-05	IRINOTECAN	0	0	0	1	0	6
191	19-May-05	FLUOROURACIL	0	0	0	0	1	6
191	19-May-05	LEUCOVORIN	0	0	1	0	0	6
191	29-Sep-05	CAPECITABINE	0	1	0	0	0	6
191	25-May-06	MEGESTROL	0	0	0	0	0	6
200	14-Apr-05	DEXAMETHASONE	0	0	0	0	0	5
200	14-Apr-05	FLUOROURACIL	0	0	0	0	1	5
200	14-Apr-05	IRINOTECAN	0	0	0	1	0	5
200	14-Apr-05	LEUCOVORIN	0	0	1	0	0	5
200	3-Jul-06	CAPECITABINE	0	1	0	0	0	5
209	5-Apr-05	DEXAMETHASONE	0	0	0	0	0	8
209	5-Apr-05	FLUOROURACIL	0	0	0	0	1	8
209	5-Apr-05	IRINOTECAN	0	0	0	1	0	8
209	12-May-05	BEVACIZUMAB	0	0	0	0	0	8
209	12-May-05	LEUCOVORIN	0	0	1	0	0	8
209	11-Jul-05	OXALIPLATIN	1	0	0	0	0	8
209	25-Nov-05	CETUXIMAB	0	0	0	0	0	8
209	19-Jun-06	CAPECITABINE	0	1	0	0	0	8
236	6-May-05	CAPECITABINE	0	1	0	0	0	5
236	6-Dec-05	DEXAMETHASONE	0	0	0	0	0	5
236	6-Dec-05	IRINOTECAN	0	0	0	1	0	5
236	6-Dec-05	LEUCOVORIN	0	0	1	0	0	5
236	13-Dec-05	FLUOROURACIL	0	0	0	0	1	5
241	8-Sep-05	DEXAMETHASONE	0	0	0	0	0	6
241	8-Sep-05	FLUOROURACIL	0	0	0	0	1	6
241	8-Sep-05	IRINOTECAN	0	0	0	1	0	6
241	8-Sep-05	LEUCOVORIN	0	0	1	0	0	6
241	27-Feb-06	CAPECITABINE	0	1	0	0	0	6
241	12-Jun-06	MEGESTROL	0	0	0	0	0	6
309	16-May-05	FLUOROURACIL	0	0	0	0	1	6
309	17-May-05	LEUCOVORIN	0	0	1	0	0	6
309	31-May-06	TAMOXIFEN	0	0	0	0	0	6
309	6-Jun-06	IRINOTECAN	0	0	0	1	0	6
309	17-Aug-06	ANASTROZOLE	0	0	0	0	0	6
309	9-Nov-06	CAPECITABINE	0	1	0	0	0	6
348	1-Apr-05	LEUCOVORIN	0	0	1	0	0	5
348	7-Apr-05	FLUOROURACIL	0	0	0	0	1	5
348	18-Apr-05	ALLOPURINOL	0	0	0	0	0	5
348	6-May-05	IRINOTECAN	0	0	0	1	0	5
348	11-May-05	DEXAMETHASONE	0	0	0	0	0	5
349	8-Apr-05	BEVACIZUMAB	0	0	0	0	0	5
349	8-Apr-05	DEXAMETHASONE	0	0	0	0	0	5
349	8-Apr-05	FLUOROURACIL	0	0	0	0	1	5
349	8-Apr-05	LEUCOVORIN	0	0	1	0	0	5
349	8-Apr-05	OXALIPLATIN	1	0	0	0	0	5
364	19-May-05	DEXAMETHASONE	0	0	0	0	0	6
364	19-May-05	FLUOROURACIL	0	0	0	0	1	6
364	19-May-05	LEUCOVORIN	0	0	1	0	0	6
364	9-Jan-06	CAPECITABINE	0	1	0	0	0	6
364	22-May-06	IRINOTECAN	0	0	0	1	0	6
364	1-Nov-06	MEGESTROL	0	0	0	0	0	6
366	6-Apr-05	CAPECITABINE	0	1	0	0	0	5
366	22-Apr-05	MEGESTROL	0	0	0	0	0	5
366	11-Aug-05	DEXAMETHASONE	0	0	0	0	0	5
366	17-Aug-05	FLUOROURACIL	0	0	0	0	1	5
366	18-Aug-05	CISPLATIN	1	0	0	0	0	5
411	31-May-05	DEXAMETHASONE	0	0	0	0	0	5
411	31-May-05	FLUOROURACIL	0	0	0	0	1	5
411	31-May-05	IRINOTECAN	0	0	0	1	0	5
411	31-May-05	LEUCOVORIN	0	0	1	0	0	5
411	24-Feb-06	MEGESTROL	0	0	0	0	0	5
494	12-Nov-05	DEXAMETHASONE	0	0	0	0	0	5
494	12-Nov-05	LEUCOVORIN	0	0	1	0	0	5
494	21-Nov-05	FLUOROURACIL	0	0	0	0	1	5
494	30-Jan-06	CAPECITABINE	0	1	0	0	0	5
494	1-Feb-06	IRINOTECAN	0	0	0	1	0	5
506	4-Aug-05	DEXAMETHASONE	0	0	0	0	0	5
506	4-Aug-05	FLUOROURACIL	0	0	0	0	1	5
506	4-Aug-05	LEUCOVORIN	0	0	1	0	0	5
506	27-Oct-05	IRINOTECAN	0	0	0	1	0	5
506	6-Jan-07	CAPECITABINE	0	1	0	0	0	5
526	1-Jul-05	FLUOROURACIL	0	0	0	0	1	5
526	1-Jul-05	LEUCOVORIN	0	0	1	0	0	5
526	7-Jul-05	IRINOTECAN	0	0	0	1	0	5
526	9-Aug-05	DEXAMETHASONE	0	0	0	0	0	5
526	6-Sep-06	MEGESTROL	0	0	0	0	0	5
569	15-Jul-05	CAPECITABINE	0	1	0	0	0	5
569	7-Nov-06	DEXAMETHASONE	0	0	0	0	0	5
569	7-Nov-06	FLUOROURACIL	0	0	0	0	1	5
569	7-Nov-06	LEUCOVORIN	0	0	1	0	0	5
569	9-Nov-06	IRINOTECAN	0	0	0	1	0	5
654	5-Feb-07	BEVACIZUMAB	0	0	0	0	0	8
654	5-Feb-07	DEXAMETHASONE	0	0	0	0	0	8
654	5-Feb-07	IRINOTECAN	0	0	0	1	0	8
654	22-Feb-07	CAPECITABINE	0	1	0	0	0	8
654	24-Apr-07	FLUOROURACIL	0	0	0	0	1	8
654	24-Apr-07	LEUCOVORIN	0	0	1	0	0	8
654	24-Apr-07	OXALIPLATIN	1	0	0	0	0	8
654	10-Dec-07	CETUXIMAB	0	0	0	0	0	8
665	20-Oct-05	BEVACIZUMAB	0	0	0	0	0	7
665	20-Oct-05	FLUOROURACIL	0	0	0	0	1	7
665	20-Oct-05	LEUCOVORIN	0	0	1	0	0	7
665	20-Oct-05	OXALIPLATIN	1	0	0	0	0	7
665	27-Oct-05	CAPECITABINE	0	1	0	0	0	7
665	8-May-06	IRINOTECAN	0	0	0	1	0	7
665	20-Sep-06	RALOXIFENE	0	0	0	0	0	7
703	26-Apr-06	DEXAMETHASONE	0	0	0	0	0	7
703	26-Apr-06	FLUOROURACIL	0	0	0	0	1	7
703	26-Apr-06	LEUCOVORIN	0	0	1	0	0	7
703	26-Apr-06	OXALIPLATIN	1	0	0	0	0	7
703	24-Oct-06	BEVACIZUMAB	0	0	0	0	0	7
703	10-Oct-07	CAPECITABINE	0	1	0	0	0	7
703	17-Oct-07	IRINOTECAN	0	0	0	1	0	7
756	30-May-07	DEXAMETHASONE	0	0	0	0	0	6
756	30-May-07	FLUOROURACIL	0	0	0	0	1	6
756	30-May-07	LEUCOVORIN	0	0	1	0	0	6
756	30-May-07	OXALIPLATIN	1	0	0	0	0	6
756	19-Jul-07	BEVACIZUMAB	0	0	0	0	0	6
756	15-Nov-07	IRINOTECAN	0	0	0	1	0	6
759	6-Jun-06	CAPECITABINE	0	1	0	0	0	5
759	23-Oct-06	MEGESTROL	0	0	0	0	0	5
759	11-Apr-07	IRINOTECAN	0	0	0	1	0	5
759	12-Apr-07	FLUOROURACIL	0	0	0	0	1	5
759	12-Apr-07	LEUCOVORIN	0	0	1	0	0	5
875	19-Mar-08	DEXAMETHASONE	0	0	0	0	0	5
875	19-Mar-08	FLUOROURACIL	0	0	0	0	1	5
875	19-Mar-08	LEUCOVORIN	0	0	1	0	0	5
875	16-Jul-08	IRINOTECAN	0	0	0	1	0	5
875	24-Sep-08	OCTREOTIDE	0	0	0	0	0	5
879	12-Apr-06	FLUOROURACIL	0	0	0	0	1	5
879	20-Apr-06	BEVACIZUMAB	0	0	0	0	0	5
879	20-Apr-06	DEXAMETHASONE	0	0	0	0	0	5
879	20-Apr-06	IRINOTECAN	0	0	0	1	0	5
879	20-Apr-06	LEUCOVORIN	0	0	1	0	0	5
909	21-Dec-11	BEVACIZUMAB	0	0	0	0	0	5
909	21-Dec-11	DEXAMETHASONE	0	0	0	0	0	5
909	21-Dec-11	FLUOROURACIL	0	0	0	0	1	5
909	21-Dec-11	LEUCOVORIN	0	0	1	0	0	5
909	21-Dec-11	OXALIPLATIN	1	0	0	0	0	5
925	21-Sep-07	CAPECITABINE	0	1	0	0	0	8
925	2-Jan-08	BEVACIZUMAB	0	0	0	0	0	8
925	2-Jan-08	DEXAMETHASONE	0	0	0	0	0	8
925	2-Jan-08	FLUOROURACIL	0	0	0	0	1	8
925	2-Jan-08	LEUCOVORIN	0	0	1	0	0	8
925	2-Jan-08	OXALIPLATIN	1	0	0	0	0	8
925	4-Apr-08	CETUXIMAB	0	0	0	0	0	8
925	4-Apr-08	IRINOTECAN	0	0	0	1	0	8
947	10-Oct-06	FLUOROURACIL	0	0	0	0	1	5
947	10-Oct-06	LEUCOVORIN	0	0	1	0	0	5
947	19-Oct-06	OXALIPLATIN	1	0	0	0	0	5
947	20-Aug-07	IRINOTECAN	0	0	0	1	0	5
947	2-Jul-08	CAPECITABINE	0	1	0	0	0	5
986	18-Jul-06	MEGESTROL	0	0	0	0	0	5
986	19-Jul-06	CARBOPLATIN	1	0	0	0	0	5
986	24-Aug-06	CISPLATIN	1	0	0	0	0	5
986	24-Aug-06	DEXAMETHASONE	0	0	0	0	0	5
986	24-Aug-06	ETOPOSIDE	0	0	0	0	0	5
987	5-Oct-06	MEDROXYPROGESTERONE	0	0	0	0	0	5
987	13-Nov-06	FLUOROURACIL	0	0	0	0	1	5
987	13-Nov-06	LEUCOVORIN	0	0	1	0	0	5
987	28-Jan-08	CAPECITABINE	0	1	0	0	0	5
;
 

[ballardw]

I think since there are only 5 drugs in the example I would be tempted to created a string variable such as

catt(z_pl,z_tab,z_leuco,z_tecan,z_fluor) as drugstring

 create a custom format to assign the desired value for the combination of all 5 such as:

proc format;
value $drgstr
'10101'='FOLFOX'
'10111'='FoOLFOXIRI'
/* after all of the desired*/
other  ='None of the above'
;
run;

Note that the ORDER of the values depends on the ORDER the drugs appear in the CATT statement.

This uses all 5 drugs simultaneously and the combinations that occur with 2 of the 3 in another desired approach would be looking at all of the 0's as well. Then you just associate the format with the variable Drugstring (or whatever you decide to call it). 

[Cruise]

@ballardw

Thanks, below is my earlier attempt but the same logic you suggested. How to then impose the rule: in order for drugs defined as combined they need to have same claim date which is ser_date in this case. Currently, my and your code doesn't account for date of drug claim. NDC doesn't assign unique drug codes for combo drugs so my rule to define combo drugs is to have ingredient drugs (leucovoron, platin and fluoracil) of the combo (FOLFOX for example) claimed on the same day.

if z_leuco=1 and z_fluor=1 and z_tecan=1 and z_pl=1 then c_FOLFOXIRI=1; else c_FOLFOXIRI=0;

if z_leuco=1 and z_tecan=1 and z_fluor=1 then c_FOLFIRI=1; else c_FOLFIRI=0;

if z_leuco=1 and z_pl=1 and z_fluor=1 then c_FOLFOX=1; else c_FOLFOX=0;

if z_tab=1 and +z_pl=1 then c_CAPEOX=1; else c_CAPEOX=0;

if z_tecan=1 and z_pl=1 then c_IROX=1; else c_IROX=0;

count_c=sum(of c_:);

/*if c_folfox=1 or c_folfiri=1 or c_folfoxiri=1 or c_irox=1 or c_capeox=1 then output; */

[ballardw · Accepted solution]

---

@Cruise wrote:

@ballardw

Thanks, below is my earlier attempt but the same logic you suggested. How to then impose the rule: in order for drugs defined as combined they need to have same claim date which is ser_date in this case. Currently, my and your code doesn't account for date of drug claim. NDC doesn't assign unique drug codes for combo drugs so my rule to define combo drugs is to have ingredient drugs (leucovoron, platin and fluoracil) of the combo (FOLFOX for example) claimed on the same day.

if z_leuco=1 and z_fluor=1 and z_tecan=1 and z_pl=1 then c_FOLFOXIRI=1; else c_FOLFOXIRI=0;

if z_leuco=1 and z_tecan=1 and z_fluor=1 then c_FOLFIRI=1; else c_FOLFIRI=0;

if z_leuco=1 and z_pl=1 and z_fluor=1 then c_FOLFOX=1; else c_FOLFOX=0;

if z_tab=1 and +z_pl=1 then c_CAPEOX=1; else c_CAPEOX=0;

if z_tecan=1 and z_pl=1 then c_IROX=1; else c_IROX=0;

count_c=sum(of c_:);

/*if c_folfox=1 or c_folfiri=1 or c_folfoxiri=1 or c_irox=1 or c_capeox=1 then output; */

---

To get summary by patient and date:

Proc summary data=have nway;

   class patient date;

   var z_ : ;

   output out=temp max=;

run;

Use the temp data set to assign the combinations.

[Cruise]

@ballardw

Hi ballardw, hope I got your approach right. it worked out. let me know if any comments. Thanks a lot.

proc summary data=comb nway;
   class patient_id ser_date;
   var z_ : ;
   output out=temp max=;
run;

data temp1; set temp;
length combo $5; 
combo=catt(z_pl,z_tab,z_leuco,z_tecan,z_fluor); 
run;

data temp2; set temp1; 
if combo='10111' then c_FOLFOXIRI=1; else c_FOLFOXIRI=0;
if combo='00111' then c_FOLFIRI  =1; else c_FOLFIRI  =0;
if combo='10101' then c_FOLFOX   =1; else c_FOLFOX   =0;
if combo='11000' then c_CAPEOX   =1; else c_CAPEOX   =0;
if combo='10010' then c_IROX     =1; else c_IROX     =0;
run;

proc freq data=temp2;
tables c_:;
run;