Hi @SarahW13
You can try this code:
data have;
input id Med $ Start_date End_date;
informat Start_date End_date MMDDYY8.;
format Start_date End_date MMDDYY8.;
datalines;
1 A 1/10/15 4/10/15
1 B 2/10/15 3/10/15
2 A 7/8/18 10/8/18
2 C 9/8/18 11/8/18
;
run;
/* Expand dataset: create one row per day for each drug */
data have_exp;
set have;
format day MMDDYY8.;
do day=Start_date to End_date;
output;
end;
run;
proc sort data=have_exp;
by id day;
run;
/* Transpose drugs to create combination */
proc transpose data=have_exp out=have_tr (drop=_name_);
var med;
by id day;
run;
data have_combi;
set have_tr;
combi = catx(" + ",of col:);
keep id day combi;
run;
/* Creation of the variable counter, which is incremented each time a new combination is given */
data have_combi2;
set have_combi;
by id combi notsorted;
if first.id then counter=0;
if first.combi then counter + 1;
run;
/* Creation of the final table */
proc sql;
create table want as
select id,
combi as Med,
min(day) as Start_date format=MMDDYY8.,
max(day) as End_date format=MMDDYY8.
from have_combi2
group by id, combi, counter
order by id, Start_date ;
quit;
... View more
