BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sanjus
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

 

View solution in original post

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

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;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

@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
Tourmaline | Level 20

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1582 views
  • 5 likes
  • 4 in conversation