Count the weeks

[SASdevAnneMarie]

Hello Experts,

I would like to count the weeks in the same way like the function no.semaine in Excel.

For exemple, in my code, I'm applying the sas sas function week for 01/01/2024 and I get the week 0, but I would like to have the week 1. The 01/01/2024 starts in Monday, so I would like that the weeks starts on monday and has the values from 1 to 53.

Thank you for your help.

[PaigeMiller]

You could use the week function with the 'V' descriptor, according to the documentation

specifies the number-of-the-week whose value is represented as a decimal number in the range 1–53. Monday is considered the first day of the week and week 1 of the year is the week that includes both January 4 and the first Thursday of the year. If the first Monday of January is the 2nd, 3rd, or 4th, the preceding days are part of the last week of the preceding year.

so this seems as if it will do what you want.

If you want something else, it seems as if that is not defined in SAS, but should be easy to program based on the results of the above and whatever your specific requirements are.

[SASdevAnneMarie · Accepted solution]

Thank you, but I tried all descriptors, I don't have the right values. With 'V' descriptor I have the 30 JUN 2024 as week 26, but with Excel function it is the week 27. 😞 I don't know how to get the same values as in Excel function, please.

[PaigeMiller]

For certain days of the week, you add 1 to the week number. You should be able to compare the SAS output to the Excel output and see what days the week numbers differ, and then fix it for those days of the week by adding 1.

[s_lassen]

If you are calling the WEEKNUM function in Excel without any "Return type" (the second parameter), the result should be like the WEEK function in SAS with the 'U' modifier, except that the first week in Excel always has number 1. So, if the first day of the year is a Sunday, the two calls should return the same values for that year, but if it is not, the Excel function will return a value that is one greater than the SAS function.

You should be able to get the same result as in Excel like this:

week_no=week(date,'U')+(weekday(mdy(1,1,year(date)))>1);

- assuming your date variable is called DATE.

If you implement this code, I would recommend putting in a comment (you can steal the text I wrote above, be my guest), as the code by itself is pretty cryptic.