Count number of days in each week of a month...

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Count number of days in each week of a month...

Dear All,

I want to find out days in each week and i'm not sure on how can it be done.

Output expected in a dataset.

Input parameter will be today's date and based on today's date below output will be calculated.

First Day of month - 01-Sep-2013

Last Day of month - 30-Sep-2013

WK1_Start = 01-Sep-2013

WK1_End  = 06-Sep-2013

WK1_Days = 6

WK2_Start = 07-Sep-2013

WK2_End  = 13-Sep-2013

WK2_Days = 7

WK3_Start = 14-Sep-2013

WK3_End  = 20-Sep-2013

WK3_Days = 7

WK4_Start = 21-Sep-2013

WK4_End  = 27-Sep-2013

WK4_Days = 7

WK4_Start = 28-Sep-2013

WK4_End  = 30-Sep-2013

WK4_Days = 3

Request if somebody can help me on this.

Rgds, Anil


Accepted Solutions
Solution
‎09-29-2013 06:36 PM
Super User
Posts: 19,783

Re: Count number of days in each week of a month...

%let start=01feb2014;

data want;

array wk_start(5) wk_start1-wk_start5;

array wk_end(5) wk_end1-wk_end5;

array wk_dur(5) wk_dur1-wk_dur5;

wk_start1=intnx('month', "&start"d, 0, 'b');

wk_end1=intnx('week', wk_start1, 1, 'b')-2;

wk_dur1=wk_end1-wk_start1+1;

month_end=intnx('month', "&start"d, 0, 'e');

format wk_start: wk_end: month_end date9.;

do i=2 to 5;

  if wk_end(i-1)+1 <= month_end then

  wk_start(i)=wk_end(i-1)+1;

  if wk_start(i)+7 <=month_end then

  wk_end(i)=wk_start(i)+6;

  else if wk_start(i) ne . then wk_end(i)=month_end;

  wk_dur(i)=wk_end(i)-wk_start(i)+1;

    Can add a in if condition here to handle wk_dur that are 1, but your rules need to be defined.

end;

total_days=sum(of wk_dur(*));

run;


What happens in February 2014?

What about weeks that have a duration of 1 at the end?

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,864

Re: Count number of days in each week of a month...

Hi:

  I don't understand your logic. Why does the first week start on Sunday (1Sep), but the second week start on Saturday (7Sep)? Is that the pattern for every month? How about October? October 1 is on  a Tuesday. When does this week in October end?

cynthia

Frequent Contributor
Posts: 76

Re: Count number of days in each week of a month...

Posted in reply to Cynthia_sas

Ok.. Let me explain you.. In my case, Friday is always a last day of the week and Saturday is the first day of the week.

So for Sep 2013, first day of month of Sunday, hence whatever days until Friday will be WK1 in my case.

Hmmm, let me give you 3 months example which will clear your doubt.

For Sep 2013.

WK1 starts from Sun 1st Sep until Fri 6th Sep which makes 6 days

WK2 starts form Sat 7th Sep until Fri 13th Sep which makes 7 days

WK3 starts form Sat 14th Sep until Fri 20th Sep which makes 7 days

WK4 starts form Sat 21st Sep until Fri 27th Sep which makes 7 days and

WK5 starts form Sat 28th Sep until last day of month Mon 30th Sep which makes 3 days.

All above is total 30 days.

For Oct 2013.

WK1 starts from Tue 1st Oct until Fri 4th Oct which makes 4 days

WK2 starts form Sat 5th Oct until Fri 11th Oct which makes 7 days

WK3 starts form Sat 12th Oct until Fri 18th Oct which makes 7 days

WK4 starts form Sat 19th Oct until Fri 25th Oct which makes 7 days and

WK5 starts form Sat 26th Oct until last day of month Thu 31st Oct which makes 6 days.

All above is total 31 days.

Now a slight different calculation for Nov 2013 but with the same approach.

WK1 starts from Fri 1st Nov until Fri 1st Nov which makes 1 day only

WK2 starts form Sat 2th Nov until Fri 8th Nov which makes 7 days

WK3 starts form Sat 9th Nov until Fri 15th Nov which makes 7 days

WK4 starts form Sat 16th Nov until Fri 22th Nov which makes 7 days and

WK5 starts form Sat 23th Nov until Fri 29th Nov which makes 7 days.

WK6 will be just one day.

All above is total 30 days.

For all months where the week day count is just a single day should get merged with the next and prior week, meaning for such months instead of above Nov output, below should be generated.

WK1 starts form Sat 1st Nov until Fri 8th Nov which makes 8 days

WK2 starts form Sat 9th Nov until Fri 15th Nov which makes 7 days

WK3 starts form Sat 16th Nov until Fri 22th Nov which makes 7 days and

WK4 starts form Sat 23th Nov until Sat 30th Nov which makes 8 days.

All above is total 30 days.

Hope you understood my query... I actually tried all my best using array intck and what not.. but each time i ended up in some confusion and no success.

Thanks a lot for helping me on this query... Really appreciate.

Kind Regards,

Anil

Frequent Contributor
Posts: 87

Re: Count number of days in each week of a month...

You might need to adjust the conditional days number but this should work:

data test ;

    format date week_start week_end ddmmyy10. ;

    date = '09sep2013'd ;

        if day(date) < 7 then week_start = intnx('month',date,0) ;

            else week_start = intnx('week.7',date,0,'b') ;

        week_end = intnx('week.7',date,1,'b')-1 ;

        days = sum(week_end,-week_start)+1 ;

run ;

Frequent Contributor
Posts: 76

Re: Count number of days in each week of a month...

Thanks Steve for your reply...

I will check this code tomorrow in office but i doubt if this code will return me total of below 15 variables

WK1_StartDate, WK1_EndDate, WK1_Days, 

WK2_StartDate, WK2_EndDate, WK2_Days,

WK3_StartDate, WK3_EndDate, WK3_Days,

WK4_StartDate, WK4_EndDate, WK4_Days,

WK5_StartDate, WK5_EndDate, WK5_Days

Rgds, Anil

Super User
Posts: 19,783

Re: Count number of days in each week of a month...

It's probably best if you specify what your data looks like and what you want the output to look like. 

Steve's code does solve the problem you asked, with what you've provided.

Frequent Contributor
Posts: 87

Re: Count number of days in each week of a month...

Pretty ugly but appears to work:

data test (keep = week_start week_end days) ;

   format date week_start week_end tmp_week_start tmp_week_end ddmmyy10. ;

   date = '02nov2013'd ;

   days_in_month = day(intnx('month',date,1)-1) ;
   month = month(date) ;
   year = year(date) ;

   month_start = intnx('month',date,0) ;
   month_end = intnx('month',date,1)-1 ;

   do i = 1 to days_in_month ;
   calc_date = mdy(month,i,year) ;

   tmp_week_start = intnx('week.7',calc_date,0,'b') ;
   tmp_week_end = intnx('week.7',calc_date,1,'b')-1 ;

   week_start = max(month_start,tmp_week_start) ;

   if month(tmp_week_end) ne month then week_end = month_end ;
   else week_end = tmp_week_end ;

   days = sum(week_end,-week_start)+1 ;
   output ;
   end ;

    run ;

    proc sort data = test nodupkey ;

   by week_start week_end days ;

    run ;

Frequent Contributor
Posts: 76

Re: Count number of days in each week of a month...

Hey Steve,

Thanks a lot.. This worked as well for me...

Rgds, Anil

Solution
‎09-29-2013 06:36 PM
Super User
Posts: 19,783

Re: Count number of days in each week of a month...

%let start=01feb2014;

data want;

array wk_start(5) wk_start1-wk_start5;

array wk_end(5) wk_end1-wk_end5;

array wk_dur(5) wk_dur1-wk_dur5;

wk_start1=intnx('month', "&start"d, 0, 'b');

wk_end1=intnx('week', wk_start1, 1, 'b')-2;

wk_dur1=wk_end1-wk_start1+1;

month_end=intnx('month', "&start"d, 0, 'e');

format wk_start: wk_end: month_end date9.;

do i=2 to 5;

  if wk_end(i-1)+1 <= month_end then

  wk_start(i)=wk_end(i-1)+1;

  if wk_start(i)+7 <=month_end then

  wk_end(i)=wk_start(i)+6;

  else if wk_start(i) ne . then wk_end(i)=month_end;

  wk_dur(i)=wk_end(i)-wk_start(i)+1;

    Can add a in if condition here to handle wk_dur that are 1, but your rules need to be defined.

end;

total_days=sum(of wk_dur(*));

run;


What happens in February 2014?

What about weeks that have a duration of 1 at the end?

Frequent Contributor
Posts: 76

Re: Count number of days in each week of a month...

Thanks Reeza, the output is exactly what i was looking for....

Thanks a ton.

Rgds, Anil

Respected Advisor
Posts: 3,156

Re: Count number of days in each week of a month...

You need first to layout the starts/ends of both weeks and month, then it becomes plain and simple:

%let date='01nov2013'd;

data _null_;

w_start=intnx('week.7',&date,0,'begin');

w_end=intnx('week.7',&date,0,'end');

m_start=intnx('month',&date,0,'begin');

m_end=intnx('month',&date,0,'end');

Days=7-(w_end-m_end>0)*(w_end-m_end)-(m_start-w_start>0)*(m_start-w_start);

put days=;

run;

Haikuo

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 725 views
  • 4 likes
  • 5 in conversation