INTNX calendar slicing / monthly anniversary date and calendar date

[bebess]

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

 …

[Astounding]

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;

[bebess]

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

[Astounding]

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.)

[Shmuel]

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;
        
                 

:

[bebess]

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 .

[Shmuel · Accepted solution]

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.

[Ksharp]

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;