topic Re: How to exclude or identify patients who used two drugs on same day? in SAS Programming

How to exclude or identify patients who used two drugs on same day?

James_an — Fri, 04 Mar 2022 02:29:33 GMT

Hi, i am new to sas and handling the patients data right now, but having trouble excluding patients

if i can use some of your help, i would be so grateful!

here is the dataset i have

ID/converted_date(yymmdd10. format)/drug_name

1/2011-01-01/A

1/2011-01-01/B

1/2011-05-01/A

2/2012-03-07/A

2/2012-06-25/A

3/2013-01-12/A

3/2013-01-12/B

4/2014-01-12/B

4/2015-06-02/B

In this example i need to extract or get ID of patients #1, #3 and

#2,#4 will be included in the table.

the dataset i have is about 50GB, so it is really heavy!!, it would be great if the code is simple and easy.

thank you!

Re: How to exclude or identify patients who used two drugs on same day?

japelin — Fri, 04 Mar 2022 03:28:44 GMT

If it's the same drug on the same day, would you leave it?
I don't know if this two ways are correct since the results you want are not shown, but you can try.

/* one step */
proc sql;
  create table want1 as
    select a.* from have as a 
    left join have as b 
    on a.id=b.id and 
       a.converted_date=b.converted_date and
       a.drug_name^=b.drug_name
    where b.drug_name=''
;
quit;

/* two steps: sort and sql */
proc sort data=have  out=dup UNIQUEOUT=uniq NOUNIQUEKEY;
  by id converted_date;
run;
proc sql;
  create table want2 as
    select * from uniq 
  union all 
    select a.* from dup as a 
    left join dup as b 
    on a.id=b.id and 
       a.converted_date=b.converted_date and
       a.drug_name^=b.drug_name
    where b.drug_name=''
;
quit;

Re: How to exclude or identify patients who used two drugs on same day?

Ksharp — Fri, 04 Mar 2022 12:35:31 GMT

data have;
infile cards dlm='/' truncover;
input id date :yymmdd10. drug $;
format date yymmdd10.;
cards;
1/2011-01-01/A
1/2011-01-01/B
1/2011-05-01/A
2/2012-03-07/A
2/2012-06-25/A
3/2013-01-12/A
3/2013-01-12/B
4/2014-01-12/B
4/2015-06-02/B
;

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

Re: How to exclude or identify patients who used two drugs on same day?

tarheel13 — Fri, 04 Mar 2022 13:07:32 GMT

Why are you including #4 if it is not on the same day?

Re: How to exclude or identify patients who used two drugs on same day?

andreas_lds — Fri, 04 Mar 2022 14:32:09 GMT

So, what exactly to you want? Is the data sorted by patient and date?

proc freq data=have noprint;
   table id*date / out=work.count(drop= percent where=(count > 1));
run;

proc sort data=work.count out=work.sorted nodupkey;
   by id;
run;

proc print;run;

data work.want;
   set work.have;
   
   if _n_ = 1 then do;
      declare hash h(dataset: 'work.sorted');
      h.defineKey('id');
      h.defineDone();
   end;
   
   if h.check() = 0 then delete; /* patient who used two drugs on same day are removed */   
run;

Re: How to exclude or identify patients who used two drugs on same day?

mkeintz — Sat, 05 Mar 2022 05:10:37 GMT

The data are sorted by ID/DATE
No drug is issued twice in the same day for a given ID.

then this program works;

data want;
  set have;
  by id date;
  if not (first.date=1 and last.date=1);
run;

The excludes all instances in which there is only one obs per date. All the others must satisfy your condition.

Re: How to exclude or identify patients who used two drugs on same day?

James_an — Sat, 05 Mar 2022 11:06:44 GMT

because In case of #4, other drug was not prescribed on the same day! it will be remained!