DATA Step, Macro, Functions and more

SAS code to gnerate interval dataset

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

SAS code to gnerate interval dataset

[ Edited ]

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.

 

 

 

lagperiodlagperiodBeglagperiodEndperiodperiodBegperiodEnd
Feb-1801/12/201731/12/2017Mar-1801/03/201831/03/2018
Jan-1801/12/201731/12/2017Feb-1801/02/201828/02/2018
Dec-1701/12/201601/12/2016Jan-1801/01/201831/01/2018
Nov-1701/12/201631/12/2016Dec-1701/12/201731/12/2017
Oct-1701/12/201631/12/2016Nov-1701/11/201730/11/2017
Sep-1701/12/201631/12/2016Oct-1701/10/201731/10/2017
Aug-1701/12/201631/12/2016Sep-1701/09/201730/09/2017
Jul-1701/12/201631/12/2016Aug-1701/08/201731/08/2017
Jun-1701/12/201631/12/2016Jul-1701/07/201731/07/2017
May-1701/12/201631/12/2016Jun-1701/06/201730/06/2017
Apr-1701/12/201631/12/2016May-1701/05/201731/05/2017
Mar-1701/12/201631/12/2016Apr-1701/04/201730/04/2017
Feb-1701/12/201631/12/2016Mar-1701/03/201731/03/2017
Jan-1701/12/201631/12/2016Feb-1701/02/201728/02/2017
Dec-1601/12/201501/12/2015Jan-1701/01/201731/01/2017
Nov-1601/12/201531/12/2015Dec-1601/12/201631/12/2016
Oct-1601/12/201531/12/2015Nov-1601/11/201630/11/2016
Sep-1601/12/201531/12/2015Oct-1601/10/201631/10/2016
Aug-1601/12/201531/12/2015Sep-1601/09/201630/09/2016
Jul-1601/12/201531/12/2015Aug-1601/08/201631/08/2016
Jun-1601/12/201531/12/2015Jul-1601/07/201631/07/2016
May-1601/12/201531/12/2015Jun-1601/06/201630/06/2016
Apr-1601/12/201531/12/2015May-1601/05/201631/05/2016
Mar-1601/12/201531/12/2015Apr-1601/04/201630/04/2016
Feb-1601/12/201531/12/2015Mar-1601/03/201631/03/2016
Jan-1601/12/201531/12/2015Feb-1601/02/201629/02/2016
Dec-1501/12/201401/12/2014Jan-1601/01/201631/01/2016
Nov-1501/12/201431/12/2014Dec-1501/12/201531/12/2015
......
......
......
Dec-0901/12/200931/12/2009Dec-1001/12/201031/12/2010

Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 10,686

Re: SAS code to gnerate interval dataset

Posted in reply to emrancaan
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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,402

Re: SAS code to gnerate interval dataset

Posted in reply to emrancaan

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.

Contributor
Posts: 36

Re: SAS code to gnerate interval dataset

Each month data will be compared against previous year's December data. By creating lagperiods I can join this table with actual data table and run comparison against required intervals. hope that helps
Super User
Super User
Posts: 9,402

Re: SAS code to gnerate interval dataset

Posted in reply to emrancaan

Post test data in the form of a datastep of what you have.

Solution
4 weeks ago
Super User
Posts: 10,686

Re: SAS code to gnerate interval dataset

Posted in reply to emrancaan
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;
Contributor
Posts: 36

Re: SAS code to gnerate interval dataset

Thank you All
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 117 views
  • 1 like
  • 3 in conversation