Excluding those with multiple drug class claims on same index date

[derbygun]

Hi All, 

I am working on an electronic health record database where I have selected individuals using some specific drugs. I want to use the last medication received to categorize each patient into a particular drug class. However, some received multiple drugs on the same date and I would like to completely remove them from my data. See the example table below; 

PersonID	Visit ID	Medication description	Start date	Drug class A	Drug class B	Drug class C
2	389965	Drug class A	28th Nov 2015	1	0	0
2	389965	Drug class A	1st Feb 2016	1	0	0
2	614578	Drug class A	19thJune 2019	1	0	0
2	1456893	Drug class A	10th Oct 2020	1	0	0
4	604822	Drug class B	13 May 2019	0	1	0
4	965534	Drug class B	19 Aug 2019	0	1	0
4	453398	Drug class B	01 April 2020	0	1	0
4	212234	Drug class B	13 May 2020	0	1	0
4	212234	Drug class A	13 May 2020	1	0	0

For patient 2, based on their last date they are certainly on drug class A, but for patient 4 based on their last date, he was on a multiple drug combination. How do I delete patient 4 and all similar patients from my database using just the data from their more recent records? 

Thank you all. 

[ballardw]

Please show what you expect the output to look like.

Are your dates actual SAS date values or random character values that allow things like 28th? This may become a very important question because of your "last medication" requirement. Determining last may not go very well with character values such as you show. 

[derbygun]

@ballardw Yes, the dates are actual SAS value dates (numeric). For each person _id, I did a proc sort by date to see the dates in ascending order. I have the patients' medication names, so I categorized them into the three possible different classes. When I did that, I realized some patients received multiple medications on the same date, so I can not categorically classify them into a particular drug class. My main aim is to delete patients that received multiple medications on the same date. I am attaching another data set below.

PersonID	Visit ID	Names of Medications	Start date	Drug class A	Drug class B	Drug class C
2	3866897	dula	28-Nov-18	0	1	0
2	6139545	dula	19-Jun-19	0	1	0
2	14110036	dula	7-Oct-20	0	1	0
4	3866996	dapa-met	12-Nov-18	1	0	0
4	3866996	Insu-lixi	12-Nov-18	0	1	0
4	6048002	dula	8-Jun-20	0	1	0
4	60410643	dula	5-Oct-20	0	1	0
6	1453918	Sita	22-Mar-17	0	0	1
6	2470214	dula	21-May-18	0	1	0
6	3866906	dula	17-Dec-18	0	1	0

I would like to delete patient 4 because he received multiple medications (dapa-met, Insu-lixi )on the same date

[tarheel13]

you could do this with sql. you could count distinct medication names by person ID date and delete those having count >=2.

[Ksharp]

data have;
input ( PersonID
VisitID
NamesofMedications
Startdate
DrugclassA
DrugclassB
DrugclassC
) (: $20.);
cards;
2
3866897
dula
28-Nov-18
0
1
0
2
6139545
dula
19-Jun-19
0
1
0
2
14110036
dula
7-Oct-20
0
1
0
4            
3866996
dapa-met
12-Nov-18          
1
0
0
4
3866996
Insu-lixi
12-Nov-18
0
1
0
4
6048002
dula
8-Jun-20
0
1
0
4
60410643
dula
5-Oct-20
0
1
0
6
1453918
Sita
22-Mar-17
0
0
1
6
2470214
dula
21-May-18
0
1
0
6
3866906
dula
17-Dec-18
0
1
0
;

proc sql;
create table want as
select * from have 
 where PersonId not in 
 (select distinct PersonID from have
    group by PersonID,StartDate
      having count(distinct NamesofMedications) ne 1);
quit;