how to automatically find number of week in the month where date is first of every month

[sanjus]

Hi;

I have a data set where date is first of every month. I need to find the number of weeks in that month.

date is something like this : 

1/1/2016

2/1/2016

3/1/2016

4/1/2016

5/1/2016

6/1/2016

7/1/2016

8/1/2016

9/1/2016

10/1/2016

11/1/2016

12/1/2016

1/1/2017

2/1/2017

3/1/2017

4/1/2017

Please advise

solution should be 

1/1/2016

2/1/2016

3/1/2016

4/1/2016

5/1/2016

as below:

4

5

4

4

5

Thanks in advance

[PeterClemmensen · Accepted solution]

Welcome to the SAS communities 🙂

I am going to assume that what you actually want are the weeks started in the particular month, i.e. the number of mondays. In that case, you can do like this

data have;
input date:mmddyy10.;
format date mmddyy10.;
datalines;
1/1/2016
2/1/2016
3/1/2016
4/1/2016
5/1/2016
6/1/2016
7/1/2016
8/1/2016
9/1/2016
10/1/2016
11/1/2016
12/1/2016
1/1/2017
2/1/2017
3/1/2017
4/1/2017
;

data want(drop=d endMonth);
   set have;
   endMonth=intnx('month', date, 0, 'e');
   numMondays=0;

   do d=date to endMonth;
      if weekday(d)=2 then numMondays+1;
   end;

run;

[sanjus]

Thanks a lot.

That is what I wanted.

[PeterClemmensen]

Anytime, glad to help 🙂

[RW9]

@PeterClemmensen, Sorry, but that really isn't the way to do it.  Your code will loop X number of times, each time calling the function and code within = highly inefficient.  Two simple function calls is all it takes:

data want;
  t='01MAR2019'd;
  diff=week(intnx('month',t,1,'b')-1)-week(t);
run;

[PeterClemmensen]

@RW9, Couldn't agree more, your solution is way more efficient. I was aiming for a solution like this, which in my mind should give me the number of Mondays between two dates .. But it doesn't seem to give me the correct answer (therefore the brute force answer). Can you offer some thoughts on on why the code below does not result in the number of Mondays?

data want;
   set have;
   endMonth=intnx('month', date, 0, 'e');
   numMondays=intck('weekday134567w' ,date, endMonth);
run;

[RW9]

Well, not 100% sure, not used that weeday134567w thing before.  What I would guess however is that if your month starts on Tuesday or ends on a Monday then you will get odd results.  Thats why I went for whole week counts, should avoid the one day each way.  Its probably not a particularly good solution (or actually a good question either) as there really isn't a fixed number of weeks in each month as Jan this year for instance is 5.7 weeks, 5 whole weeks and one day.  So the whole concept of weeks in a month is a bit skewed.  I would advise OP to clarify what exactly is expected here, it would be more accurate to calculate number of days in month, then divide by 7, that would give a more accurate representation.

[RW9]

I think you have your "want" the wrong way round, should read 5,4,5...  There isn't 5 weeks in Feb for instance.  Anyways, you can get this quite simply by taking the result of week() function, and substracting that from the week function of month+1-1day:

data want;
  t='01MAR2019'd;
  week1=week(t);
  week2=week(intnx('month',t,1,'b')-1);
  diff=week2-week1;
run;

Note, you can do it all in one statement, I separate out into three to show the workings.

[novinosrin]

+1