Drug combination and switching from treatment start and end dates

[goyalrk]

Can someone please help with the following problem? 

What I have in the data:

ID	Drug	Start DT	End DT
1	Drug A	3-Aug-2006	28-Dec-2006
1	Drug B	3-Aug-2006	18-Nov-2007
1	Drug C	29-Dec-2006	15-Dec-2008

What I am trying to obtain:

ID	Drug	Start DT New	End DT New
1	Drug A + Drug B	3-Aug-2006	28-Dec-2006
1	Drug B + Drug C	29-Dec-2006	18-Nov-2007
1	Drug C	19-Nov-2007	15-Dec-2008

Thanks a lot!

[PGStats]

This problem has been solved many times on this forum, e.g.

https://communities.sas.com/t5/SAS-Programming/Identifying-unique-drug-regimens-in-overlapping-timel...

[goyalrk]

Thank you for your response. I have tried solutions to previously posted similar problems, and for one reason or the other, they do not seem to apply for the specific data example/scenario that I have (to get the drug combinations and dates as expected). I'd very much appreciate any further assistance with this particular query. 

[andreas_lds]

Please post the code you have tried, along with the log so that we can help to fix the problems.

[Ksharp · Accepted solution]

data have;
infile cards expandtabs;
input ID	Drug $	(Start 	End) (: date11.);
format start end date11.;
cards;
1	DrugA	3-Aug-2006	28-Dec-2006
1	DrugB	3-Aug-2006	18-Nov-2007
1	DrugC	29-Dec-2006	15-Dec-2008
2	DrugA	3-Aug-2006	28-Dec-2006
2	DrugB	3-Aug-2006	18-Nov-2007
2	DrugC	29-Dec-2006	15-Dec-2008
;

data temp;
 set have;
 do date=start to end;
  output;
 end;
 format date date11.;
 drop start end;
run;
proc sort data=temp nodupkey;
by id date drug;
run;
data temp1;
do until(last.date);
 set temp;
 by id date;
 length drugs $ 200;
 drugs=catx('+',drugs,drug);
end;
drop drug;
run;
data temp2;
 set temp1;
 by id;
 if first.id or dif(date) ne 1 or drugs ne lag(drugs) then group+1;
run;
proc sql;
create table want as
select max(id) as id,max(drugs) as drug,min(date) as start format=date11.,max(date) as end format=date11.
 from temp2
  group by group;
quit;