Create a datset that contains an observation for each month in a range held on existing dataset

[cxkev]

Hi,

I have a dataset where each observation has a start and end date. I would like a dataset that would create an observation for each month within that range. Please see below example:

Have:

Employee_ID     Start_Date     End_Date     Target

789                    01JAN14          28FEB14     0.85

456                    01JAN14          31MAR14     0.75

Want:

Employee_ID     Month          Target

789                    01JAN14     0.85

789                    01FEB14     0.85

456                    01JAN14     0.75

456                    01FEB14     0.75

456                    01MAR14     0.75

Thanks for any help,

[RW9]

Hi,

data want (keep=employee_id month target);

     set have;

     do I=start_date to end_date;

          month=I;

     end;

     format month date9.;

run;

[Kurt_Bremser · Accepted solution]

(assuming that start_date and end_date are SAS date values)

data want (keep=employee_id month target);

set have;

format month date7.;

month = intnx('month',start_date,0,'beginning');

do until (month > end_date);

  output;

  month = intnx('month',month,1);

end;

run;

[cxkev]

Excellent. Thanks.