Quartz | Level 8

## Summing overlapping dates and doses

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

## Re: Summing overlapping dates and doses

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 ;``````

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
2 REPLIES 2
Rhodochrosite | Level 12

## Re: Summing overlapping dates and doses

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 ;``````

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Super User

## Re: Summing overlapping dates and doses

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;``````
Discussion stats
• 2 replies
• 1504 views
• 0 likes
• 3 in conversation