Solved
Contributor
Posts: 28

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

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,

Accepted Solutions
Solution
‎11-26-2014 08:05 AM
Super User
Posts: 10,217

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Super User
Posts: 9,599

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

Hi,

data want (keep=employee_id month target);

set have;

do I=start_date to end_date;

month=I;

end;

format month date9.;

run;

Solution
‎11-26-2014 08:05 AM
Super User
Posts: 10,217

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 28