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.
... View more