Select switch and nonswtich subject

[lulu3]

Hi Everyone,

I would like to distinguish the switch and nonswitch patients.

Drug switching cohorts: patients in naive drug will be followed until the first swtiching to the second drug other than index drug, the switching date will be designated as the index date.

For example:

data have;
input id indexdate : ddmmyy10. drug $;
format indexdate ddmmyy10.;
datalines;;
201 18/07/2013 A
201 27/12/2013 A
201 13/06/2013 A
201 19/08/2013 B
201 01/05/2014 B
201 14/10/2013 B
201 20/02/2014 B
201 27/03/2014 B
201 16/09/2013 B
201 18/12/2013 B
202 16/04/2014 A
202 16/01/2014 C
202 02/10/2013 C
202 15/07/2013 B
202 13/05/2013 B
202 11/03/2014 B
202 11/11/2013 B
202 23/09/2013 B
202 18/05/2014 A
202 16/08/2014 C
203 08/09/2015 A
203 15/07/2014 A
203 18/03/2013 A
203 11/03/2014 A
203 11/11/2013 A
203 27/11/2012 A
;
run;

Want output:

Id	Indexdate	drug	switch
201	19/08/2013	B	1
202	02/10/2013	C	1
203	27/11/2012	A	0

swtich: 1=switch 0=nonswitch

proc sort data=have out=have1;
by id indexdate;
run;

Thanks in advance!!!

[mkeintz · Accepted solution]

Editted as per request from @lulu3 

You can solve this by programming a loop that traverses all obs for a given ID, counting the number of drugs encountered.   Inside that loop is a subloop that traverses each drug within the id:

data want (drop=_:);
  do _ndrug=1 by 1 until (last.id);
    do until (last.drug);
      set have2;
      by id drug notsorted;
	  if _ndrug=1 and first.drug then _date=indexdate; /*edited addition*/
	  if _ndrug=2 and first.drug then do;
        switch=1;
        output;
	  end;
	end;
  end;
  switch=0;
  if _ndrug=1 then do;
    indexdate=_date;   /* edited addition */
    output;
  end;
run;

There are two (conditional) output statements.  The first one captures the start of drug number 2, if such an event occurs.  The second one captures the most recent earliest date when there are no switches.

[lulu3]

Thank you very much! It helps me a lot.

[lulu3]

Hi @mkeintz ,

Your answer was super helpful.  But I just found that when I select the subject 203 (nonswitch subject), the last drug date became the indexdate.  I would like to choose the first drug date as the indexdate. (27/11/2012 not 08/09/2015). I was wondering if you have any suggestions for this?

Thank you very much!

[lulu3]

Hi Everyone,

I would like to distinguish the switch and nonswitch patients.

Drug switching cohorts: patients in naive drug will be followed until the first swtiching to the second drug other than index drug, the switching date will be designated as the index date.

Drug Nonswitich cohorts: the first drug date will be designated as the index date.

For example:

data have;
input id indexdate : ddmmyy10. drug $;
format indexdate ddmmyy10.;
datalines;;
201 18/07/2013 A
201 27/12/2013 A
201 13/06/2013 A
201 19/08/2013 B
201 01/05/2014 B
201 14/10/2013 B
201 20/02/2014 B
201 27/03/2014 B
201 16/09/2013 B
201 18/12/2013 B
202 16/04/2014 A
202 16/01/2014 C
202 02/10/2013 C
202 15/07/2013 B
202 13/05/2013 B
202 11/03/2014 B
202 11/11/2013 B
202 23/09/2013 B
202 18/05/2014 A
202 16/08/2014 C
203 08/09/2015 A
203 15/07/2014 A
203 18/03/2013 A
203 11/03/2014 A
203 11/11/2013 A
203 27/11/2012 A
;
run;

Want output:

Id	Indexdate	drug	switch
201	19/08/2013	B	1
202	02/10/2013	C	1
203	27/11/2012	A	0

swtich: 1=switch 0=nonswitch

I accpeted the answer from @mkeintz 

proc sort out=have2; by id indexdate;run;

data want (drop=_:);
do _ndrug=1 by 1 until (last.id);
do until (last.drug);
set have2;
by id drug notsorted;
if _ndrug=2 and first.drug then do;
switch=1;
output;
end;
end;
end;
switch=0;
if _ndrug=1 then output;
run;

The output is:

Id	Indexdate	drug	switch
201	19/08/2013	B	1
202	02/10/2013	C	1
203	08/09/2015	A	0

The subject 203 uses the last drug date as the indexdate.  But I want the first drug date as the index date (27/11/2012).

Many thanks!

[mkeintz]

@lulu3 

I think you got a little impatient.  After your comment in that topic, I corrected the answer you had accepted, iwth notification.  If you agree, I will merge this topic with the first version - to reduce confusion.

[lulu3]

Sure, please. Thanks a lot for your help.