topic Re: Create a datset that contains an observation for each month in a range held on existing dataset in SAS Programming

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

cxkev — Wed, 26 Nov 2014 12:54:10 GMT

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,

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

RW9 — Wed, 26 Nov 2014 13:01:35 GMT

Hi,

data want (keep=employee_id month target);

set have;

do I=start_date to end_date;

month=I;

end;

format month date9.;

run;

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

Kurt_Bremser — Wed, 26 Nov 2014 13:05:20 GMT

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

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

cxkev — Wed, 26 Nov 2014 13:13:45 GMT

Excellent. Thanks.