Calculate week and year based on a Sunday to Satuday week

[gdaymte]

I am trying to calculate the week number and year with data that spans multiple years. The first day of any week is Sunday. The week number should be 1 to 52 or 53. Week 1 of any year should have at least 4 days in that calendar year. For example, if January 1 occurs on a Sunday, Monday, Tuesday or Wednesday, the calendar week that includes January 1 would be Week 1. If January 1 occurs on a Thursday, Friday, or Saturday, the calendar week that includes January 1 would be the last week of the previous year (52 or 53). This means December 29, 30, and 31 could potentially fall into week 1 of the following year.

I have included what the expected year and week should be in th code listed. I have utilized the week and year functions, but none seem to be what I am looking for. Any assistance would be great.

data a;
infile cards dsd dlm=',';
input Date ExpYear ExpWeek;
informat Date MMDDYY10. ExpYear 4. ExpWeek z2.;
format Date WEEKDATX17. ExpYear 4. ExpWeek z2.;
datalines;
12/24/2018,2018,52
12/25/2018,2018,52
12/26/2018,2018,52
12/27/2018,2018,52
12/28/2018,2018,52
12/29/2018,2018,52
12/30/2018,2019,01
12/31/2018,2019,01
01/01/2019,2019,01
01/02/2019,2019,01
01/03/2019,2019,01
01/04/2019,2019,01
12/24/2019,2019,52
12/25/2019,2019,52
12/26/2019,2019,52
12/27/2019,2019,52
12/28/2019,2019,52
12/29/2019,2020,01
12/30/2019,2020,01
12/31/2019,2020,01
01/01/2020,2020,01
01/02/2020,2020,01
01/03/2020,2020,01
01/04/2020,2020,01
12/24/2020,2020,52
12/25/2020,2020,52
12/26/2020,2020,52
12/27/2020,2020,53
12/28/2020,2020,53
12/29/2020,2020,53
12/30/2020,2020,53
12/31/2020,2020,53
01/01/2021,2020,53
01/02/2021,2020,53
01/03/2021,2021,01
01/04/2021,2021,01
;
run;
data b; set a;
    format year 4. week z2. week_u z2. week_v z2. week_w z2.; 
    year = YEAR(date);
    week   = week(date); 
    week_u = week(date, 'u'); 
    week_v = week(date, 'v'); 
    week_w = week(date, 'w');
proc print;
run;

[gdaymte · Accepted solution]

I was able to figure it out on my own using the following macro.

/*MACRO TO CALCULATE MMWR WEEK*/
%MACRO WEEKNO (DATE , START = 1 , KEY = 3 );
  /*
     DATE  = Date value to convert to weeks
     START = weekday of week beginning
     KEY   = determines majority day, i.e. &start + &key
     REQUIREMENT: 1 <= &start + &key <= 7

     Returns week number where first week of year is first week
     containing the majority day in that year, i.e. for defaults,
     week begins on Sunday and Wednesday determines year.
  */
  %LOCAL	MAJORDAY ;
  %LET 		MAJORDAY = (INTNX("WEEK.&START", &DATE, 0, "B") + &KEY) ;
  			INT((&MAJORDAY - INTNX("YEAR",&MAJORDAY,0,"B"))/7) + 1
%MEND WEEKNO;

data b; set a;
    format week_m z2. year_m 4.; 
    week_m = PUT((%WEEKNO(DtReport)),Z2.);
    
    IF PUT((%WEEKNO(DtReport)),Z2.) = 52 AND YEAR(intnx('week',DtReport,0,'B')) NE YEAR(intnx('week',DtReport,0,'E')) 
    	THEN YEAR_M=YEAR(intnx('week',DtReport,0,'B'));
    ELSE IF PUT((%WEEKNO(DtReport)),Z2.) = 53 AND YEAR(intnx('week',DtReport,0,'B')) NE YEAR(intnx('week',DtReport,0,'E')) 
    	THEN YEAR_M=YEAR(intnx('week',DtReport,0,'B'));
    ELSE IF PUT((%WEEKNO(DtReport)),Z2.) = 01 AND YEAR(intnx('week',DtReport,0,'B')) NE YEAR(intnx('week',DtReport,0,'E')) 
    	THEN YEAR_M=YEAR(intnx('week',DtReport,0,'E'));
    ELSE YEAR_M=YEAR(DtReport);
run;
    
proc print;
run;

[FreelanceReinh]

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.