I want to generate interval dataset from March 2018 to December 2010. Can you help me to produce this dataset. is there any function in sas like intnx() which can do the job.
output dataset should be like.
lagperiod | lagperiodBeg | lagperiodEnd | period | periodBeg | periodEnd |
Feb-18 | 01/12/2017 | 31/12/2017 | Mar-18 | 01/03/2018 | 31/03/2018 |
Jan-18 | 01/12/2017 | 31/12/2017 | Feb-18 | 01/02/2018 | 28/02/2018 |
Dec-17 | 01/12/2016 | 01/12/2016 | Jan-18 | 01/01/2018 | 31/01/2018 |
Nov-17 | 01/12/2016 | 31/12/2016 | Dec-17 | 01/12/2017 | 31/12/2017 |
Oct-17 | 01/12/2016 | 31/12/2016 | Nov-17 | 01/11/2017 | 30/11/2017 |
Sep-17 | 01/12/2016 | 31/12/2016 | Oct-17 | 01/10/2017 | 31/10/2017 |
Aug-17 | 01/12/2016 | 31/12/2016 | Sep-17 | 01/09/2017 | 30/09/2017 |
Jul-17 | 01/12/2016 | 31/12/2016 | Aug-17 | 01/08/2017 | 31/08/2017 |
Jun-17 | 01/12/2016 | 31/12/2016 | Jul-17 | 01/07/2017 | 31/07/2017 |
May-17 | 01/12/2016 | 31/12/2016 | Jun-17 | 01/06/2017 | 30/06/2017 |
Apr-17 | 01/12/2016 | 31/12/2016 | May-17 | 01/05/2017 | 31/05/2017 |
Mar-17 | 01/12/2016 | 31/12/2016 | Apr-17 | 01/04/2017 | 30/04/2017 |
Feb-17 | 01/12/2016 | 31/12/2016 | Mar-17 | 01/03/2017 | 31/03/2017 |
Jan-17 | 01/12/2016 | 31/12/2016 | Feb-17 | 01/02/2017 | 28/02/2017 |
Dec-16 | 01/12/2015 | 01/12/2015 | Jan-17 | 01/01/2017 | 31/01/2017 |
Nov-16 | 01/12/2015 | 31/12/2015 | Dec-16 | 01/12/2016 | 31/12/2016 |
Oct-16 | 01/12/2015 | 31/12/2015 | Nov-16 | 01/11/2016 | 30/11/2016 |
Sep-16 | 01/12/2015 | 31/12/2015 | Oct-16 | 01/10/2016 | 31/10/2016 |
Aug-16 | 01/12/2015 | 31/12/2015 | Sep-16 | 01/09/2016 | 30/09/2016 |
Jul-16 | 01/12/2015 | 31/12/2015 | Aug-16 | 01/08/2016 | 31/08/2016 |
Jun-16 | 01/12/2015 | 31/12/2015 | Jul-16 | 01/07/2016 | 31/07/2016 |
May-16 | 01/12/2015 | 31/12/2015 | Jun-16 | 01/06/2016 | 30/06/2016 |
Apr-16 | 01/12/2015 | 31/12/2015 | May-16 | 01/05/2016 | 31/05/2016 |
Mar-16 | 01/12/2015 | 31/12/2015 | Apr-16 | 01/04/2016 | 30/04/2016 |
Feb-16 | 01/12/2015 | 31/12/2015 | Mar-16 | 01/03/2016 | 31/03/2016 |
Jan-16 | 01/12/2015 | 31/12/2015 | Feb-16 | 01/02/2016 | 29/02/2016 |
Dec-15 | 01/12/2014 | 01/12/2014 | Jan-16 | 01/01/2016 | 31/01/2016 |
Nov-15 | 01/12/2014 | 31/12/2014 | Dec-15 | 01/12/2015 | 31/12/2015 |
. | . | . | . | . | . |
. | . | . | . | . | . |
. | . | . | . | . | . |
Dec-09 | 01/12/2009 | 31/12/2009 | Dec-10 | 01/12/2010 | 31/12/2010 |
data x;
do i=intck('month','01dec2010'd,'01mar2018'd) to 0 by -1;
period=intnx('month','01dec2010'd,i);
periodBeg=period;
periodEnd=intnx('month',period,0,'e');
lagperiod=intnx('month',period,-1);
year=year(intnx('year',lagperiod,-1));
lagperiodBeg=mdy(12,1,year);
lagperiodEnd=mdy(12,31,year);
output;
end;
format lagperiod period monyy7. periodBeg periodEnd lagperiodBeg lagperiodEnd ddmmyy10.;
drop i ;
run;
data want;
set x;
by year notsorted;
if first.year and month(lagperiod)=12 then lagperiodEnd=mdy(12,1,year);
drop year;
run;
That seems to be a simple do loop with a intnx. However I cannot understand that data you have posted at all. What does any of it mean, lag period, is 1 month before period I get that and it can be done with intnx() function, just put the correct actual date values into the function. But what are the dates, how do you get them? Why is lagperiodbeg 01/12 in the first row and not periodbeg-1 month?
Also, post as a datastep, test data to show what you have, so we can see that against the output required and have something to program against.
Post test data in the form of a datastep of what you have.
data x;
do i=intck('month','01dec2010'd,'01mar2018'd) to 0 by -1;
period=intnx('month','01dec2010'd,i);
periodBeg=period;
periodEnd=intnx('month',period,0,'e');
lagperiod=intnx('month',period,-1);
year=year(intnx('year',lagperiod,-1));
lagperiodBeg=mdy(12,1,year);
lagperiodEnd=mdy(12,31,year);
output;
end;
format lagperiod period monyy7. periodBeg periodEnd lagperiodBeg lagperiodEnd ddmmyy10.;
drop i ;
run;
data want;
set x;
by year notsorted;
if first.year and month(lagperiod)=12 then lagperiodEnd=mdy(12,1,year);
drop year;
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.