topic Re: INTNX calendar slicing / monthly anniversary date and calendar date in SAS Programming

INTNX calendar slicing / monthly anniversary date and calendar date

bebess — Wed, 11 Apr 2018 15:10:18 GMT

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

…

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

Astounding — Wed, 11 Apr 2018 15:23:55 GMT

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;

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

Shmuel — Wed, 11 Apr 2018 15:45:09 GMT

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;

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

bebess — Wed, 11 Apr 2018 15:55:00 GMT

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

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

bebess — Wed, 11 Apr 2018 15:59:53 GMT

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 .

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

Shmuel — Wed, 11 Apr 2018 16:09:25 GMT

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.

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

Astounding — Wed, 11 Apr 2018 16:10:20 GMT

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

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

Ksharp — Thu, 12 Apr 2018 13:36:05 GMT

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;