how to correct the shift in date

[alepage]

data demo;
	format date weekdatx.  sat_start_of_week fri_end_of_week sat_start_of_year fri_end_of_year date9.;
	do date='28dec2013'd to '02jan2026'd;
		sat_start_of_week=intnx('week.7',date,0,'b');
		fri_end_of_week=intnx('week.7',date,0,'e');

		sat_start_of_year=mdy(1,1,year(date)) + mod(11-weekday(mdy(1,1,year(date))),7)-4;
  		fri_end_of_year=mdy(12,31,year(date))+ mod(10-weekday(mdy(1,1,year(date))),7)-4;
		output;
	end;
run;

The sat_start_of_week and the fri_end_of_week are in line with the date value.

But for the sat_start_of_year and fri_end_of_year, how can I make sure that 28dec2013 goes from date=28dec2013 until 02jan2015 same thing for 02jan2015

[FreelanceReinh]

---

@alepage wrote:

data demo;
    format date weekdatx.  sat_start_of_week fri_end_of_week sat_start_of_year fri_end_of_year date9.;
    do date='28dec2013'd to '02jan2026'd;
        sat_start_of_week=intnx('week.7',date,0,'b');
        fri_end_of_week=intnx('week.7',date,0,'e');

        sat_start_of_year=mdy(1,1,year(date)) + mod(11-weekday(mdy(1,1,year(date))),7)-4;
        fri_end_of_year=mdy(12,31,year(date))+ mod(10-weekday(mdy(1,1,year(date))),7)-4;
        output;
    end;
run;

Hello @alepage,

First of all, I think the two arguments in the last call of the MDY function which I highlighted in red should read 12 and 31, respectively (cf. my suggestion in the other thread).

In the other thread we started with a year (e.g., 2014) and calculated its redefined start and end dates (e.g., 28 Dec 2013 and 02 Jan 2015, resp.). Now you could use the inverse calculation to obtain that year (let's denote it by y) from those dates (and all dates between them). A similar reasoning as before leads to the following formula to achieve this:

y=year(date+mod(13-weekday(date),7)-2)

Using y as the third argument of the MDY function calls should produce the desired results:

y=year(date+mod(13-weekday(date),7)-2);
sat_start_of_year=mdy(1,1,y)+mod(11-weekday(mdy(1,1,y)),7)-4;
fri_end_of_year=mdy(12,31,y)+mod(10-weekday(mdy(12,31,y)),7)-4;

[alepage]

Thank you very much for the sas code.

Keeping the same logic, I was able to get the good value for Year, Quarter, Month and week.

I still need your help to get the proper value for:

start_quater_date='';

end_quater_date='';

start_month_date="";

end_mont_date='';

x_dayintheyear='';

x_day_inthemonth=''; and finally

sat_start_previous_year='';

fri_end_previous_year='';

[FreelanceReinh · Accepted solution]

If the definitions of these dates are analogous to those of the year start/end dates, the same formulas should be applicable:

1. The redefined "start date" of a given period (quarter, month, ...) -- always a Saturday -- is
   

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

   where d is the commonly used start date of the period (e.g., July 1 for the third quarter). Compared to the original date (d), the new date is shifted by −4, −3, −2, −1, 0, +1 or +2 days, depending on the weekday of d.
2. The redefined "end date" of a given period (quarter, month, ...) -- always a Friday -- is
   

   e+mod(10-weekday(e),7)-4

   where e is the commonly used end date of the period (e.g., September 30 for the third quarter). Compared to the original date (e), the new date is shifted by −4, −3, −2, −1, 0, +1 or +2 days, depending on the weekday of e.
3. Given an arbitrary date, the redefined period containing that date can be determined as the commonly used period of the shifted date
   

   date+mod(13-weekday(date),7)-2

   Example: For date='01OCT2015'd the expression above yields '30SEP2015'd, which is commonly regarded as a day in the third quarter of 2015. Hence, 1 Oct 2015 falls into the redefined "third quarter of 2015" -- and this is consistent with the start and end dates of that period as calculated using the two formulas under 1. and 2., i.e., 27 Jun 2015 and 02 Oct 2015, respectively.

[alepage]

I was able to estimate the good month_start_date and the good month_end_date

How do we estimate the x day of the year. Starting from 1 on 18dec2023 and ending with 371 on 02jan2015

[FreelanceReinh]

---

@alepage wrote:

How do we estimate the x day of the year. Starting from 1 on 18dec2023 and ending with 371 on 02jan2015

---

Can you please share your definition of "the x day of the year"? I could only guess that you mean the xth day of the (redefined) year or some x-days period (with, say, x=80 or x=100).

"371 on 02jan2015" matches the 371 days and the end date of the redefined year "2014" -- but its start date ("day 1") is '02JAN2015'd-370='28DEC2013'd, not '18DEC2023'd.

[alepage]

Hello,

I have been using the intck function and it works very well.

x day of the year=intck('day',PRV_Dt_Annee_Deb,date) + 1 ;

[Tom]

What exactly is the question?

Are you trying to calculate from a single DATE value which Fiscal Year (for want of a better term) it falls under?

It might just be easier to use the answers from your earlier questions to generate a custom format.  Or a  pair of formats, one to display the start date and one for the end date, that displays the date in style that DATE informat can read.

sat_start_of_year=input(put(date,sat_start_of_year.),date9.);
fri_end_of_year  =input(put(date,fri_end_of_year.),date9.);

As to your posted code you need add some logic to figure out which YEAR (fiscal year) the date belongs to.

Try something like this:

data demo;
  do date='25dec2013'd to '12jan2014'd;
    sat_start_of_week=intnx('week.7',date,0,'b');
    fri_end_of_week=intnx('week.7',date,0,'e');
    do fyear=year(date),year(date)-1,year(date)+1;
      sat_start_of_year=mdy(1,1,fyear) + mod(11-weekday(mdy(1,1,fyear)),7)-4;
      fri_end_of_year=mdy(1,1,fyear+1)-1 + mod(11-weekday(mdy(1,1,fyear+1)),7)-4;
      if sat_start_of_year<= date <=fri_end_of_year then leave;
    end;
    output;
  end;
  format date weekdatx. sat_: fri_: date9.;
run;