I have the following dataset where:
ID=participant ID
Visno=visit number
Visdate=visit date
Drug A, Drug B=indicators for whether participant was taking Drug A or Drug B
How can I get a count of how many participants who startoff on Drug A (Drug A =1 at visno=00) but then stop taking Drug A at some point after visno 00 without either switching to Drug B or re-starting Drug A at a later time point? For example, in this sample dataset I’m providing, this happens for IDs#001, 003 and 007 where they start on Drug A at baseline (visno 00) but then at some post-baseline time point, they stop Drug A without getting on Drug B or re-starting Drug A later on.
Thanks for any suggestions/help!
data have;
input ID$ visno$ Visdate:mmddyy. DrugA DrugB;
format Visdate mmddyy10.;
datalines;
001 00 2/24/2001 1 0
001 01 6/15/2001 0 0
002 00 10/23/2008 1 0
002 01 4/10/2009 1 0
002 02 11/25/2009 1 0
002 03 5/18/2010 1 0
003 00 9/22/2001 1 0
003 01 3/25/2002 0 0
003 02 7/30/2002 0 0
004 00 4/26/2001 1 0
004 02 3/19/2002 0 1
005 00 9/19/2002 0 1
005 01 2/20/2003 0 1
005 02 9/24/2003 0 1
005 03 2/6/2004 0 1
005 04 11/1/2004 1 0
005 05 1/14/2005 1 0
005 06 9/23/2005 0 0
006 00 7/28/2000 0 1
006 02 12/11/2000 0 1
006 03 6/26/2001 0 1
007 00 3/7/2002 1 0
007 01 8/14/2002 1 0
007 02 2/6/2003 1 0
007 03 8/27/2003 0 0
007 04 3/31/2004 0 0
007 05 9/15/2004 0 0
008 00 12/25/2002 1 0
008 01 5/29/2003 1 0
008 02 11/5/2003 0 0
008 03 4/23/2004 1 0
008 04 10/6/2004 0 0
009 00 4/24/2017 1 0
009 01 10/9/2017 0 0
009 02 5/21/2018 1 1
009 03 9/24/2018 0 1
010 00 2/27/2007 1 0
010 03 10/16/2007 1 0
010 05 3/25/2008 0 0
010 06 8/5/2008 1 1
010 07 6/2/2009 0 0
;
run;
proc print data=have;
run;
There might be more elegant ways but below should work.
data want;
set have;
by id visdate;
retain _ind 0;
if first.id then
do;
if drugA=1 then _ind=1;
else _ind=-1;
end;
if _ind=1 and drugA=0 then _ind=0;
if _ind=0 and (drugA=1 or drugB=1) then _ind=2;
if last.id then
do;
if _ind=0 then output;
_ind=0;
end;
run;
proc print data=want;
run;
There might be more elegant ways but below should work.
data want;
set have;
by id visdate;
retain _ind 0;
if first.id then
do;
if drugA=1 then _ind=1;
else _ind=-1;
end;
if _ind=1 and drugA=0 then _ind=0;
if _ind=0 and (drugA=1 or drugB=1) then _ind=2;
if last.id then
do;
if _ind=0 then output;
_ind=0;
end;
run;
proc print data=want;
run;
Thanks Patrick! This works beautifully!
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.