topic how to correct the shift in date in SAS Enterprise Guide

how to correct the shift in date

alepage — Mon, 13 Jan 2025 21:46:17 GMT

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

Re: how to correct the shift in date

FreelanceReinh — Mon, 13 Jan 2025 23:34:47 GMT

@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;

Re: how to correct the shift in date

Tom — Tue, 14 Jan 2025 02:50:25 GMT

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;

Re: how to correct the shift in date

alepage — Tue, 14 Jan 2025 16:49:57 GMT

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='';

Re: how to correct the shift in date

FreelanceReinh — Tue, 14 Jan 2025 18:59:27 GMT

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

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

Re: how to correct the shift in date

alepage — Tue, 14 Jan 2025 20:26:34 GMT

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

Re: how to correct the shift in date

FreelanceReinh — Wed, 15 Jan 2025 08:12:23 GMT

@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.

Re: how to correct the shift in date

alepage — Wed, 15 Jan 2025 14:47:43 GMT

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 ;