Another approach to this task is to consider a series of dated events (with variable _DATE), where one type of event is the start of use for a drug (having _DATE=START_DATE), and the other type of event is the start of disuse for a drug (having _DATE=END_DATE+1).
From an intermediate dataset sorted by PATIENT/_DATE, every time a new _DATE is encountered, it will eventually become a START_DATE. But before setting the START_DATE, output the previously accumulated list of DRUGs, with a previously established START_DATE, and from the current record END_DATE=_DATE-1.
After the output statement, set START_DATE=_DATE (which will become the "previously established START_DATE for the upcoming data). And update the drug list as by adding or deleting as indicated. This sequence, applicable both for adding and removing drugs, is why I use END_DATE+1 (instead of END_DATE) in the intermediate data set.
data have;
input patient
start_date : date11. end_date : date9. drug :$8. ;
format start_date end_date date11.;
cards;
1 19-Mar-98 26-Jul-98 amox
1 19-Mar-98 18-Nov-98 cipro
1 19-Mar-98 1-Jun-05 zinc
1 19-Mar-98 15-Feb-09 flagyl
1 27-Jul-98 24-Aug-05 cef
2 19-Nov-98 9-Jul-03 amox
2 10-Jul-03 31-Jul-06 cipro
2 2-Jun-05 15-Feb-09 zinc
2 1-Aug-06 16-Jul-18 flagyl
3 16-Feb-09 16-Jul-18 cef
3 16-Feb-09 25-Aug-19 sec
run;
data vtemp (drop=start_date end_date) /view=vtemp ;
set have (rename=(drug=_drg));
_action=+1; _date=start_date; output;
_action=-1; _date=end_date+1; output;
format _date date9.;
run;
proc sort data=vtemp out=events;
by patient _date;
run;
data want (drop=_:);
if 0 then set have (drop=drug);
set events ;
by patient _date;
length drug $40;
retain drug ;
if first._date then do;
end_date=_date-1;
if drug^=' ' then output;
start_date=_date;
end;
array _drgs{20} $8 _temporary_;
if _action=1 then _drgs{1}=_drg;
else if _action=-1 then do _i=dim(_drgs) to 1 by -1 until(_drgs{_i}=' ');
if _drgs{_i}=_drg then _drgs{_i}=' ';
end;
call sortc(of _drgs{*});
drug=catx(',',of _drgs{*});
run;
I use the "if 0 then SET HAVE ..." statement to get all the formats (and labels) for variables in HAVE, even though the "obs=0" precludes actual reading of data from HAVE. And because you want DRUG to have a list (I set to length $40), I prevent that variable from preserving the shorter length it probably has in HAVE.
Edited note: The reason the "if _action=-1 ..." (for the "start of disuse condition") do loop starts at the end of the _drgs array and works backwards looking for a match to the newly-disused _drg is because the array would have been previously sorted, meaning all the blanks would be first, followed by the list of _drg values in lexicographic order. So all the potential matches for the _drg to be dropped from the list will be at the upper portion of the array.
Also be sure to make the _drgs array large enough to hold the longest possible list of drugs.
