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!!!
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.
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.
Thank you very much! It helps me a lot.
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!
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!
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.
Sure, please. Thanks a lot for your help.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: