Below is what I'd do, but you'll probably get better solutions from others. I'm assuming that ending up with 45 days in April201 is an error, but the basic approach is to expand the observations to cover the necessary months and then transpose. You could probably do something with arrays, but I think this will be more dynamic. Depending on the real set of data, you might need to tweak it a bit, but it should get you started. data have;
input task $ start_date :date9. number_of_days;
format start_date date9.;
datalines;
Task1 01Feb2017 48
Task2 15Mar2017 60
;
data want;
set have;
/* Calculate the end date based on the number of days. */
end_date = start_date + number_of_days;
/* Iterate over how many months the span covers. */
do i = 1 to (intck('month', start_date, end_date) + 1);
/* For every month, determine the first and last days. */
fdom = intnx('month', start_date, i - 1, 'B');
ldom = intnx('month', start_date, i - 1, 'E');
/* Use these to calculate different date spans. */
full_month_days = ldom - fdom + 1;
part_month_days = min(full_month_days, end_date - fdom, ldom - start_date + 1);
/* Determine the number of days in the final segment. */
days = min(full_month_days, part_month_days);
/* Give the observation a name for transposition. */
name = substr(put(fdom, date9.), 3);
output;
end;
format end_date fdom ldom date9.;
run;
proc transpose data = want out = want2;
by task start_date number_of_days;
var days;
id name;
run;
... View more
