DATA Step, Macro, Functions and more

INTNX calendar slicing / monthly anniversary date and calendar date

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

INTNX calendar slicing / monthly anniversary date and calendar date

Hi everyone , any help on this topic ?

 

the goal is to have a monthly calendar slicing with 2 column start and end date

 

here the code/example :

 

                     

data input;

input start_date: ddmmyy10. end_date: ddmmyy10.;

format start_date end_date ddmmyy10.;

datalines;

 

15/06/2008 31/12/2009

;

run;

data expected;

set input;

format date_breaks ddmmyy10.;

max_dur=intck('month', start_date ,end_date,'continuous');

do i=0 to max_dur;

date_breaks=intnx('month', start_date,i,'s');

output;

end;

drop i ;

run;

 

 

the result is :

 

15JUN2008

15JUL2008

15AUG2008

15SEP2008

15OCT2008

15NOV2008

15DEC2008

 

But the goal is to have :

 

15JUN2008 30JUN2008

30JUN2008  15JUL2008

15JUL2008 31JUL2008

31JUL2008  15AUG2008

 …


Accepted Solutions
Solution
Tuesday
Trusted Advisor
Posts: 1,826

Re: INTNX calendar slicing / monthly anniversary date and calendar date

I'm not sure I understand your example.

 

As day can be any day in a month, slight change need:

data want;
  set input;  /* geting the start_date and the end_date of calendar */
       retain  dayx;
       dayx = day(start_date);
       break_date = start_date;

  do while (break_date < end_date);
       if day(break_date) = dayx then 
                end_date = intnx('month', break_date, 0 ,'e');
        else end_date = break_date + 15;
        output;
        break_date = end_date;
  end;
run;
     

run the code and in case of issue please post output example and the required result.

View solution in original post


All Replies
Super User
Posts: 6,541

Re: INTNX calendar slicing / monthly anniversary date and calendar date

[ Edited ]

So where you now have one variable (DATE_BREAKS), you actually need two variables.  Let's call them DATE_BREAK1 and DATE_BREAK2.  You can patch the result using what you have done so far:

 

data want;

set expected;

if mod(_n_, 2) then do;

   date_break1 = date_breaks;

   date_break2 = intnx('month', date_breaks, 0, 'end');

end;

else do;

   date_break2 = date_breaks;

   date_break1 = intnx('month', date_breaks, 0) - 1;

end;

drop date_breaks;

run;

New Contributor
Posts: 3

Re: INTNX calendar slicing / monthly anniversary date and calendar date

Posted in reply to Astounding

thanks for you support , i have tested what i really want using you suggestion but i don't have the result expected ...

 

i have a second anniversary date corresponding to the birthday ( month slicing) ...

 

data input;

input birth_date: ddmmyy10. start_date: ddmmyy10. end_date: ddmmyy10.;

format birth_date start_date end_date ddmmyy10.;

datalines;

 

06/07/2008 15/06/2008 31/12/2010

;

run;

data expected;

set input;

format date_breaks ddmmyy10.;

max_dur=intck('month', start_date ,end_date,'continuous');

do i=0 to max_dur;

date_breaks=intnx('month', start_date,i,'s');

output;

date_breaks=intnx('month', birth_date,i,'s');

output;

end;

drop i ;

run;

data want;

set expected;

format date_break1 date_break2 ddmmyy10.;

if mod(_n_, 2) then do;

 

date_break1 = date_breaks;

date_break2 = intnx('month', date_breaks, 0, 'end');

end;

else do;

 

date_break2 = date_breaks;

date_break1 = intnx('month', date_breaks, 0) - 1;

end;

 

/*drop date_breaks;*/

run;

 

the result i have :

 

15/06/2008 30/06/2008
30/06/2008 06/07/2008
15/07/2008 31/07/2008
31/07/2008 06/08/2008

..

 

but i want :

 

15/06/2008 30/06/2008
30/06/2008 06/07/2008

06/07/2008 15/07/2008
15/07/2008 31/07/2008

31/07/2008   06/08/2008

...

 

could you explain me this part of the code :

 

if mod(_n_, 2) then do;

 

Many thanks

Super User
Posts: 6,541

Re: INTNX calendar slicing / monthly anniversary date and calendar date

[ Edited ]

The MOD function divides the first number by the second number, and returns the remainder.  So this DO statement executes on every other observation, starting with the first one.  (The ELSE statement executes for the even-numbered observations.)

Trusted Advisor
Posts: 1,826

Re: INTNX calendar slicing / monthly anniversary date and calendar date

The rules are not very clear -

  -  can start date be on and day in month, not only the 15th?

  -  if positive with day will be the next break   start_day +15   or always end of month ?

 

In the specific case you can try

data want;
  set input;  /* geting the start_date and the end_date of calendar */

       break_date = start_date;

  do while (break_date < end_date);
       if day(break_date) = 15 then 
                end_date = intnx('month', break_date, 0 ,'e');
        else end_date = break_date + 15;
        output;
        break_date = end_date;
  end;
run;
        
                 

:

 

New Contributor
Posts: 3

Re: INTNX calendar slicing / monthly anniversary date and calendar date

strat day can be any date
the goal is to split by any anniversary dates the month and always have end of month.

for example 15/month/year and 20/month/year
i always want :
start date / end date from the begining of each month until the 15 then 15 to 20 and finally 20 to the end of month .
Solution
Tuesday
Trusted Advisor
Posts: 1,826

Re: INTNX calendar slicing / monthly anniversary date and calendar date

I'm not sure I understand your example.

 

As day can be any day in a month, slight change need:

data want;
  set input;  /* geting the start_date and the end_date of calendar */
       retain  dayx;
       dayx = day(start_date);
       break_date = start_date;

  do while (break_date < end_date);
       if day(break_date) = dayx then 
                end_date = intnx('month', break_date, 0 ,'e');
        else end_date = break_date + 15;
        output;
        break_date = end_date;
  end;
run;
     

run the code and in case of issue please post output example and the required result.

Super User
Posts: 10,615

Re: INTNX calendar slicing / monthly anniversary date and calendar date

data input;
input start_date: ddmmyy10. end_date: ddmmyy10.;
format start_date end_date ddmmyy10.;
datalines;
15/06/2008 31/12/2009
;
run;
data temp;
 set input;
 n+1;
 do date=start_date to end_date;
   month=month(date);output;
 end;
 format date ddmmyy10.;
 drop start_date end_date;
run;
proc sql;
create table want as
 select n,month,min(date) as start format=date9.,max(date) as end format=date9.
  from temp
   group by n,month;
quit;
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 112 views
  • 0 likes
  • 4 in conversation