Hello @gdaymte,
Sorry, I'm way too late, but happy to see that you've found such a general and flexible solution.
The DATA step could be simplified a bit and various automatic type conversions (and the accompanying notes "Character values have been converted to numeric values ..." in the log) avoided by omitting the unnecessary PUT function in the definition of week_m:
week_m = %WEEKNO(DtReport);
The subsequent IF statements could use week_m directly rather than recompute it multiple times:
IF week_m = 52 AND ...
For the specific problem described in your initial post further simplifications are possible for certain date ranges, e.g., the range 01 Jan 1917 - 31 Dec 2099, which may be sufficient for many practical purposes these days:
/* Valid for '01JAN1917'd <= DtReport <= '31DEC2099'd */
data b;
set a;
week_m=week(DtReport-5844,'v');
year_m=year(DtReport+9-week_m);
format week_m z2.;
run;
The week_m and year_m values match those obtained with your DATA step, provided that the DtReport values are within the 183-year range mentioned above.
The "magic" number 5844 equals 16*365.25, i.e., you kind of look up the week number in a 16-year-old calendar which uses the "Monday - Sunday" convention, but otherwise the same rules for numbering the weeks ('v' descriptor of the WEEK function). Since mod(5844,7)=6, the Mondays 16 years back fell on the same dates as the Sundays in the current year, which compensates for the difference in first-day-of-the-week conventions. Leap years are taken into account as well, because 16 is a multiple of 4 and leap years occur in every fourth year within the range of the years 1901-2099. This is why the range above starts with the year 1901+16=1917.
The constant 9 used in the calculation of year_m is rather arbitrary (could be any integer between 4 and 50). The idea is just to add a couple of days (at least 3) if week_m=1 and to subtract a couple of days (at least 2 or 3) if week_m=52 or 53, respectively, in order to reach the year to which the week belongs. In most cases this shift is redundant, but the impact on performance should be small.
... View more
