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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.