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
…
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.
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;
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
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.)
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;
:
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.