Hi Sas experts,
I am missing something in my code that enables me to write a do loop to iterate by month & year.
I have two dates stored in two different variables "Start_Date" and "End_Date" and I want to expand these dates by Month & Year as opposed to expanding every day in between.
The issue is my start date in the second do loop where it starts fine but doesn't start the next year correctly. But I am unsure how to modify this to make it work.
Any help would be greatly appreciated.
This is what I have in terms of code:
data want;
set have;
do year=year(Start_Date) to year(End_Date);
do month= month(Start_Date) to 12;
date =mdy(month,1,year);
format date Date9.;
output;
end;
end;
run;
Output is:
Var A | Start_Date | End_Date | Var B | year | month | date |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 7 | 1-Jul-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 8 | 1-Aug-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 9 | 1-Sep-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 10 | 1-Oct-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 11 | 1-Nov-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 12 | 1-Dec-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 7 | 1-Jul-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 8 | 1-Aug-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 9 | 1-Sep-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 10 | 1-Oct-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 11 | 1-Nov-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 12 | 1-Dec-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2016 | 7 | 1-Jul-16 |
…. | ||||||
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 2 | 1-Feb-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 3 | 1-Mar-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 4 | 1-Apr-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 5 | 1-May-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 6 | 1-Jun-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 7 | 1-Jul-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 8 | 1-Aug-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 9 | 1-Sep-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 10 | 1-Oct-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 11 | 1-Nov-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 12 | 1-Dec-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2016 | 2 | 1-Feb-16 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2016 | 3 | 1-Mar-16 |
Result Should be:
Var A | Start_Date | End_Date | Var B | year | month | date |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 7 | 1-Jul-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 8 | 1-Aug-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 9 | 1-Sep-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 10 | 1-Oct-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 11 | 1-Nov-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2014 | 12 | 1-Dec-14 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 1 | 1-Jan-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 2 | 1-Feb-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 3 | 1-Mar-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 4 | 1-Apr-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 5 | 1-May-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2015 | 6 | 1-Jun-15 |
101 | 7/01/2014 | 1/01/2030 | 50003 | 2016 | 7 | 1-Jul-15 |
…. | ||||||
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 2 | 1-Feb-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 3 | 1-Mar-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 4 | 1-Apr-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 5 | 1-May-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 6 | 1-Jun-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 7 | 1-Jul-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 8 | 1-Aug-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 9 | 1-Sep-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 10 | 1-Oct-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 11 | 1-Nov-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2015 | 12 | 1-Dec-15 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2016 | 1 | 1-Jan-16 |
1643 | 2/01/2015 | 1/01/2030 | 20083 | 2016 | 2 | 1-Feb-16 |
Use functions INTNX and INTCK for date manipulations:
data want;
set have;
date = Start_Date;
do until(date > End_Date);
month = month(date);
output;
date = intnx("MONTH", date, 1, "SAME");
end;
format date Date9.;
run;
Use functions INTNX and INTCK for date manipulations:
data want;
set have;
date = Start_Date;
do until(date > End_Date);
month = month(date);
output;
date = intnx("MONTH", date, 1, "SAME");
end;
format date Date9.;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.