Splitting date ranges into multiple rows each a month duration

[Abimal_Zippi]

Hi There,

I wanted to split sas date ranges into multiple row each a month duration. For each ID and visit number, I want to split those observations that had more than a month exposure (in this context 30 days) in to a new row with the remaining days of exposure. It is not a problem if the duration is less or equal to 30 days. I want each row not to have more than 30 days supply. This requires to amend the amount when splitted into the next row of 30 days or fewer. E.g. if the amount is 120 for 60 days, each day will have 2 amounts, that means for each row total amount will be 60. Another example, the amount is 120 for 40 days, then each date will have 3 amounts, which means the first row will have 90 amounts followed by the second row of 30 amounts. Every time, the next row will start from the next date in the previous row...

See the "have" and "want" data set below:

data have;
input ID visit $ start_date :date9. amount duration_days $;
format start_date date9.;
cards;
1 0 12May88 60  30
1 0 04AUG88 120 60
1 1 05SEP88 120 60
2 0 15May89 60  30
2 0 15JUL88 120 40
2 1 20oct88 120 60
;



data want;
input ID visit $ start_date :date9. amount duration_days $;
format start_date date9.;
cards;
1 0 12May88 60  30
1 0 04AUG88 60  30
1 0 04SEP88 60  30
1 1 05SEP88 60  30
1 1 06oct88 60  30
2 0 15May89 60  30
2 0 15JUL88 90  30
2 0 15AUG88 30  10
2 1 20oct88 60  30
2 1 20nov88 60  30
;

[s_lassen]

Here is a solution:

data have;
input ID visit $ start_date :date9. amount duration_days ;
format start_date date9.;
cards;
1 0 12May88 60  30
1 0 04AUG88 120 60
1 1 05SEP88 120 60
2 0 15May89 60  30
2 0 15JUL88 120 40
2 1 20oct88 120 60
;run;

data want;
  set have;
  _remainder=duration_days;
  do while(_remainder>30);
    duration_days=30;
    _remainder=_remainder-30;
    output;
    start_date=start_date+30;
    end;
  duration_days=_remainder;
  output;
  drop _remainder;
run;

- except that the dates are different (30 days after 04AUG is 03SEP, not 04SEP, and 30 days after 05SEP is 05OCT, not 06OCT), are you sure that the dates should be advanced 31 days and not 30?

[Abimal_Zippi]

You are right, it should be 30 days after start date not 31 days. 

[Abimal_Zippi]

what about the amount?

[PhilC]

Looks familiar to : Solved: Re: Filling in missing dose and month medication d... - SAS Support Communities

In this problem are you sensitive to the number of total days changing each month?  February may have 28 or 29 days depending, etc...

[Abimal_Zippi]

Thanks @PhilC  for the link but in this specific case the amount/dose was not changing - only the duration is split by a month duration. I want to split the amount (as it is the whole amount for the total duration) between the rows. 

Also, in my case,  the number of total days changing each month are not sensitive -  max of 30 days . 

[Kurt_Bremser · Accepted solution]

data want;
set have;
_remain = duration_days;
_dd = duration_days;
_amt = amount;
do while (_remain > 0);
  duration_days = min(30,_remain);
  amount = _amt * (duration_days / _dd);
  output;
  _remain = _remain - duration_days;
  start_date = start_date + 30;
end;
run;