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
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;
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;
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.