topic Re: Delete rows if the subject take >=1 drugs in the same day in SAS Programming

Delete rows if the subject take >=1 drugs in the same day

lulu3 — Tue, 23 Jun 2020 14:12:39 GMT

Hi Everyone, I would like to delete the information if the patient take >=1 drugs in the same day. Bascially, if the patient take two or drugs in the same day, then delete all the information from this date.

orginal data:

ID	Date	Drug
101	10/1/2019	A
101	10/1/2019	B
101	10/1/2019	C
101	10/2/2019	A
101	10/3/2019	A
101	10/3/2019	B
101	10/3/2019	B

I want:

ID	Date	Drug
101	10/2/2019	A

Thanks for your help,

Sarah

Re: Delete rows if the subject take >=1 drugs in the same day

mklangley — Tue, 23 Jun 2020 14:32:46 GMT

Here's one way to do it:

data have;
    input ID Date: mmddyy10. Drug $;
    format Date mmddyy10.;
    datalines;
    101 10/1/2019 A
    101 10/1/2019 B
    101 10/1/2019 C
    101 10/2/2019 A
    101 10/3/2019 A
    101 10/3/2019 B
    101 10/3/2019 B 
    ;
run;

/* Find which dates have only one drug */
proc sql;
    create table date_count as
    select date, count(distinct drug) as count
    from have
    group by date
    having count(distinct drug) = 1;
quit;

/* Select only those dates from the original data */
proc sql;
    create table want as
    select a.id, a.date, a.drug
    from have a
    inner join date_count b 
        on a.date = b.date;
quit;

Re: Delete rows if the subject take >=1 drugs in the same day

Kurt_Bremser — Tue, 23 Jun 2020 14:35:29 GMT

Since SAS allows a remerge, you can do it in one select:

proc sql;
create table want as
  select *
  from have a
  group by id, date
  having count(distinct drug) = 1  
;
quit;

Re: Delete rows if the subject take >=1 drugs in the same day

Jagadishkatam — Tue, 23 Jun 2020 14:38:29 GMT

please try this untested code

proc sql;
create table want as select *, count(id) as cnt from have group by id, date having cnt<=1;
quit;

Re: Delete rows if the subject take >=1 drugs in the same day

novinosrin — Tue, 23 Jun 2020 14:57:27 GMT

Hi @lulu3 A sorted dataset (as your sample suggests) may benefit from a one pass


data have;
    input ID Date: mmddyy10. Drug $;
    format Date mmddyy10.;
    datalines;
    101 10/1/2019 A
    101 10/1/2019 B
    101 10/1/2019 C
    101 10/2/2019 A
    101 10/3/2019 A
    101 10/3/2019 B
    101 10/3/2019 B 
    ;
run;

data want;
 do _n_=1 by 1 until(last.date);
  do until(last.drug);
   set have;
   by id date drug;
  end;
 end;
 if _n_=1 then output;
run;

Re: Delete rows if the subject take >=1 drugs in the same day

s_lassen — Tue, 23 Jun 2020 18:49:39 GMT

Assuming your input is sorted by ID and DATE (if not, PROC SORT it):

data want;
  set have;
  by ID Date;
  if first.date and last.date;
run;