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,
(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;
Hi,
data want (keep=employee_id month target);
set have;
do I=start_date to end_date;
month=I;
end;
format month date9.;
run;
(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;
Excellent. Thanks.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.