turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Count number of days in each week of a month...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-29-2013 03:07 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aj34321

09-29-2013 06:36 PM

%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?

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aj34321

09-29-2013 03:24 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

09-29-2013 03:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aj34321

09-29-2013 04:44 PM

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 ;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SteveNZ

09-29-2013 05:12 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aj34321

09-29-2013 05:43 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aj34321

09-29-2013 05:47 PM

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 ;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SteveNZ

09-30-2013 06:21 AM

Hey Steve,

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

Rgds, Anil

Solution

09-29-2013
06:36 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aj34321

09-29-2013 06:36 PM

%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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

09-30-2013 06:20 AM

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

Thanks a ton.

Rgds, Anil

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aj34321

09-29-2013 08:39 PM

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