Summing overlapping dates and doses

[serena13lee]

I have the following data set that I would like to melt and reshape by aggregating 1) the total number of days on drugs where it was taken >=7 consecutive days 2) the drug amount they took.

Source data:

Patient     Drug      StartDate        EndDate  amountperday   unit                                 
A             E      2016-02-20     2016-02-28       1         mg                                                        
A             G      2016-03-20     2016-03-27       2         mg
A             F      2016-02-20     2016-02-21       7         mg  
A             E      2016-02-20                      4         mg 
B             E      2016-01-05     2016-01-24       3         mg
B             G      2016-01-07     2016-01-14       5         mg 

Result:

Patient     Drug     totamount unit   Days  StartDate   EndDate                               
A           EG       22        mg      15    2016-02-20  2016-03-27                                                
B           EG       95        mg      27    2016-01-05  2016-01-24

For A the 22 was calculated 1(8days) + 2(7days).
For A the 15 was calculated as 8 days + 7 days.
For B the 95 was calculated as 3(20days) + 5(7days).
For B the 27 was calculated as 20 days + 14 days - 7 overlapping days.

My attempt at this would be the following: 

data calculation; 

set mydata; 

days=datdif(startdate, enddate, 'act/act');

[I would have to mark the overlapping days but I'm not sure how I would do that and then add a by statement]

if days >=7 then dose = amountperday*days;

else = . ; 

run; 

Please let me know how I can get my desired outcome. It does not have to use my attempt. 

[ScottBass · Accepted solution]

I don't have SAS in front of me over the weekend, so I can only describe this approach in words and untested code.

1) Filter out those records where StartDate to EndDate is < 7 days.

2) Use a data step to transpose the data, with one record per day.  Example code (untested):

data transposed;
   set have;
   where datdif(StartDate,EndDate,'act/act') >= 7;
   do date=StartDate to EndDate;
      output;
   end;
   drop i;
   format date date9.;
run;
   

3) Use first. to keep only one record with a given date:

proc sort data=transposed out=sorted;
   by patient date drug;
run;

data no_overlaps;
   set sorted;
   by patient date drug;
   if first.date;
run;

* then count the number of rows to get the number of days minus overlapping days ;

[Ksharp]

Honestly, I can't understand your question completely .

data have;
input Patient   $  Drug   $   StartDate : yymmdd10.       EndDate : yymmdd10. amountperday   unit  $;
format StartDate  EndDate : yymmdd10.;
cards; 
A             E      2016-02-20     2016-02-28       1         mg                                                        
A             G      2016-03-20     2016-03-27       2         mg
A             F      2016-02-20     2016-02-21       7         mg  
A             E      2016-02-20        .              4         mg 
B             E      2016-01-05     2016-01-24       3         mg
B             G      2016-01-07     2016-01-14       5         mg 
;
run;
data temp;
 set have;
 if enddate-startdate>=7;
run;
data want;
   length new_drug $ 100;
   totalamount=0;
   days=0;
 do until(last.patient);
   set temp;
   by patient;
   if first.patient then new_startdate=startdate;
   new_drug=cats(new_drug,drug);
   totalamount+	(enddate-startdate)*amountperday;
   days+(enddate-startdate);
 end;
 new_enddate=enddate;
 format new_startdate new_enddate yymmdd10.;
 keep new_: patient unit totalamount days;
 run;