DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

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: 7,863

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,996

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: 7,863

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
Contributor
Posts: 28

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

Posted in reply to KurtBremser

Excellent. Thanks.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 183 views
  • 3 likes
  • 3 in conversation