Unable to find the begining and the end of a year for a calendar starting on Saturday

[alepage]

I am unable to find the logic behind this. 

How to find the begining and the end of the year when the week start on Saturday and end on Friday.

Does someone understand the logic of that.

/* Therefore, if the fist January 2025 is Weneday then the begining of the year will be Saturday 28, 2024 */
data demo;
	format date weekdatx.  sat_start_of_week sat_end_of_week sat_start_of_year sat_end_of_year year_beg_date year_end_date date9.;
	do date='28dec2013'd to '02jan2026'd;
		sat_start_of_week=intnx('week.7',date,0,'b');
		sat_end_of_week=intnx('week.7',date,0,'e');
		 year_beg_date=intnx('year',intnx('week.7',date,0,'b'),0,'beg');
		 year_end_date=intnx('year',intnx('week.7',date,0,'b'),0,'end');


/*		sat_start_of_year=intnx('year', sat_start_of_week, 0, 'b');*/
/*		sat_end_of_year=intnx('year', sat_start_of_week, 0, 'e');*/
		output;
	end;
run;

proc print data=demo;
run;

/*create dataset*/
data original_data;
    format year_start_date year_end_date date9.;
    input year_start_date :date9. year_end_date :date9.;
    datalines;
28Dec2013 02Jan2015
03Jan2015 01Jan2016
02Jan2016 30Dec2016
31Dec2016 29Dec2017
30Dec2017 28Dec2018
29Dec2018 27Dec2019
28Dec2019 01Jan2021
02Jan2021 31Dec2021
01Jan2022 30Dec2022
31Dec2022 29Dec2023
30Dec2023 27Dec2024
28Dec2024 02Jan2026

;
run;

/*view dataset*/
proc print data=original_data;

[FreelanceReinh]

Hello @alepage,

Your dataset ORIGINAL_DATA suggests that

• the beginning of the year is defined as the Saturday between Dec 28 of the previous calendar year and Jan 3 of the year in question,
• the end of the year is defined as the Friday between Dec 27 of the year in question and Jan 2 of the following calendar year.

(Note that by this definition some years, e.g. 2014, have 53 weeks and others, e.g. 2015, have 52 weeks.)

You can compute those days as shown below:

data want;
do year=2014 to 2025;
  d=mdy(1,1,year);
  e=mdy(12,31,year);
  year_start_date=d+mod(11-weekday(d),7)-4;
  year_end_date=e+mod(10-weekday(e),7)-4;
  output;
end;
drop year d e;
format y: date9.;
run;

The resulting dataset WANT equals ORIGINAL_DATA.

[Tom]

---

@FreelanceReinh wrote:

Hello @alepage,

Your dataset ORIGINAL_DATA suggests that

• the beginning of the year is defined as the Saturday between Dec 28 of the previous calendar year and Jan 3 of the year in question,
• the end of the year is defined as the Friday between Dec 27 of the year in question and Jan 2 of the following calendar year.

...

---

So what you describe can be derived from YEAR like this:

  s2 = intnx('week',mdy(12,28,year-1),0,'e');
  e2 = intnx('week.7',mdy(12,27,year),0,'e');

[alepage]

Hello,

Your SAS code provide me the expected results. Thank you very much.

However, I would like to understand the logic of those estimate.

  year_start_date=d+mod(11-weekday(d),7)-4;
  year_end_date=e+mod(10-weekday(e),7)-4;

The weekday could vary from 1 to 7, so 11 minus (1...7) could give 10 or 9 or 8 or 7 or 6 or 5 or 4 , which gives mod(10,7) or mod(9,7), or mod (8,7), or mod (7,7) or mod(6,7) or mod(5,7) or mod(4,7) plus the d minus 4.

For the other one we have The weekday could vary from 1 to 7, so 10 minus (1...7) could give  9 or 8 or 7 or 6 or 5 or 4 , 3  which gives mod(9,7), or mod (8,7), or mod (7,7) or mod(6,7) or mod(5,7) or mod(4,7) or mod(3,7) plus the e minus 4.

Could you please explain the logic of this estimate.

How do we estimate 

PRV_Dt_Trim_Deb = '' ;
PRV_Dt_Trim_Fin = '' ;

[Tom]

---

@alepage wrote:

...

How do we estimate 

PRV_Dt_Trim_Deb = '' ;
PRV_Dt_Trim_Fin = '' ;

---

Please explain in words the meaning of those two variables.  YEAR_START_DATE is pretty apparent what it means, but these two not so much.  And does what you want vary from the normal meaning?  Similar to how what you want for YEAR_START_DATE is NOT the date of the start of the calendar year, but the first day of the WEEK that includes the start of the year.  So can you describe in similar words what these other two variable contain?

[Tom]

Make a TABLE of values and you should be able to see how it works.

data test;
  do weekday=1 to 7 ;
    eleven_minus = 11 - weekday;
    mod7 = mod(eleven_minus,7);
    minus4 = mod7-4 ;
    date = mdy(1,1,2024) + minus4;
    output;
  end;
  format date mmddyy10.;
  label eleven_minus = '11 - weekday'
        mod7 = 'mod(11 - weekday,7)'
        minus4 = 'mod(11 - weekday,7)-4'
        date = 'First day of first week of 2024'
  ;
run;

proc print label;
run;

                  eleven_
Obs    weekday     minus     mod7    minus4          date

 1        1          10        3       -1      12/31/2023
 2        2           9        2       -2      12/30/2023
 3        3           8        1       -3      12/29/2023
 4        4           7        0       -4      12/28/2023
 5        5           6        6        2      01/03/2024
 6        6           5        5        1      01/02/2024
 7        7           4        4        0      01/01/2024

[alepage]

From you table, if the first day of the month is a Sunday, you substract one day to shift to Saturday. if the first day of the month is a Monday, you substract two days, if it is a Tuesday you substact three days and a Wenesday four day. If we substact x days from the start date we do the same for the end date.

If the fist day of the month is a Thursday, you add two days, and a Friday on days.  Then we add two or one days at the end.

Is that correct ?

[FreelanceReinh]

As you say, the WEEKDAY function, when applied to a valid SAS date value, always returns one of the seven values 1, 2, ..., 7. Similarly, the expression

mod(11-weekday(d),7)-4

with a valid SAS date value d results in one of the seven values −4, −3, ..., 2. These are the numbers of days by which the start date of the calendar year, d=mdy(1,1,year), must be shifted to obtain your desired year_start_date as "d + shift" -- depending on the weekday of d.  Once the formula works for all seven possible cases (i.e., weekdays of d), it will necessarily work for all years. Your dataset ORIGINAL_DATA covers all these seven cases, as does Tom's table.

Of course, the elements of the formula can be explained:

• Using the MOD(..., 7) function is plausible because a shift by one or more whole weeks doesn't change the weekday.
• The number 11 ensures that the first argument of the MOD function is always positive, which is useful to align the SAS function MOD with the mathematical mod function. Its exact value is determined by your definition of year_start_date.
• The minus sign after the 11 makes sense because in six of the seven cases mentioned above, moving to the next weekday with d requires the shift to decrease by 1 day. (The only exceptional case is handled by the MOD function value jumping from 0 [for 11-4=7] to 6 [for 11-5=6], see Tom's table.)
• The -4 at the end is necessary to get from the values returned by the MOD function (0, ..., 6) to the required range of shift values −4, −3, ..., 2.

The formula for year_end_date can be explained analogously.

---

@alepage wrote:

How do we estimate 

PRV_Dt_Trim_Deb = '' ;
PRV_Dt_Trim_Fin = '' ;

---

Maybe we can discuss this question about start and end dates of trimesters (?) in a separate thread?

[Tom]

Nice explanation.  

I would add

•  the logic of using 11 and 4 makes sense because 11 minus 4 is exactly one week.
• The only reason it uses 10 in the second equation is because it is using the day of week for the END of the interval instead of the START of the next interval.  If you base the calculation of the number of days to add or subtract based on the start of the following interval then the logic is exactly the same.

So try doing it quarters.

data quarter;
  do year=2021 to 2024 ;
    do qtr=1 to 4;
      length downame $12;
      start = intnx('qtr',mdy(1,1,year),qtr-1,'b');
      downame=put(start,downame.-L);
      end = intnx('qtr',start,0,'e');
      start_x = start + mod(11-weekday(start),7)-4 ;
      end_x = end + mod(11-weekday(end+1),7)-4 ;
      days=end-start+1;
      days_x=end_x-start_x+1;
      output;
    end;
  end;
  format start end start_x end_x date9.;
run;
proc print;
run;

And you can see that normal quarters vary between 90 and 92 days.  Put for the modified week based quarters most will have 91 days (13 weeks) but every once in awhile you get one with an extra week or fewer weeks.

Obs    year    qtr     downame         start          end      start_x        end_x    days    days_x

 1     2003     4     Wednesday    01OCT2003    31DEC2003    27SEP2003    02JAN2004     92       98
 2     2008     4     Wednesday    01OCT2008    31DEC2008    27SEP2008    02JAN2009     92       98
 3     2009     1     Thursday     01JAN2009    31MAR2009    03JAN2009    27MAR2009     90       84
 4     2009     3     Wednesday    01JUL2009    30SEP2009    27JUN2009    02OCT2009     92       98
 5     2014     4     Wednesday    01OCT2014    31DEC2014    27SEP2014    02JAN2015     92       98
 6     2015     1     Thursday     01JAN2015    31MAR2015    03JAN2015    27MAR2015     90       84
 7     2015     3     Wednesday    01JUL2015    30SEP2015    27JUN2015    02OCT2015     92       98
 8     2020     3     Wednesday    01JUL2020    30SEP2020    27JUN2020    02OCT2020     92       98

[alepage]

I was more interested to underdstand the equation that gives the start and the end date of the year if the week start on a Saturday.

As I don't understand the logic, I am not able to  estimate neither the start and the end month, samething for the start and the end of the quarter and so on.

Could please provide the equation and explaned those are developped

[Tom]

---

@alepage wrote:

I was more interested to underdstand the equation that gives the start and the end date of the year if the week start on a Saturday.

As I don't understand the logic, I am not able to  estimate neither the start and the end month, samething for the start and the end of the quarter and so on.

Could please provide the equation and explaned those are developped

---

So what are the rules for months and quarters?  Once you know the rules you can write a program.

If you don't have rules yet then it would help to see the answers for the 14 different possible years.  One for each starting day of week, with examples for both normal years and leap years.  There should not be any difference for January between normal years and leap years, but after that the extra day in February should make a difference.