BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
emrancaan
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

emrancaan
Obsidian | Level 7
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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Ksharp
Super User
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;
emrancaan
Obsidian | Level 7
Thank you All

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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