Finding YTD and Previous month

[_el_doredo]

Hello Experts,

I want to findout  the followings

1. Previous month begin and end(In this case, June 1 to June 30 of 2022)

2. Current Year beginning

3. Last Year Beginning

4. Last Year previous month(In this case, June 1 to June 30 of 2021)

If we following calendar year means then it's easy to find out. Below is my code for calendar year

DATA _NULL_;

call symput('pre_month_bn',compress(put(intnx('month',today(),-1,'beginning'),YYMMDDN.))));

call symput('pre_month_end',compress(put(intnx('month',today(),-1,'end'),YYMMDDN.))));

call symput('PY_pre_month_bn',compress(put(intnx('month',today(),-13,'beginning'),YYMMDDN.))));

call symput('PY_pre_month_end',compress(put(intnx('month',today(),-13,'end'),YYMMDDN.))));

call symput('Year_bn',compress(put(intnx('Year',today(),0,'beginning'),YYMMDDN.))));

call symput('PY_Year_bn',compress(put(intnx('Year',today(),-1,'beginning'),YYMMDDN.))));

run;

But, We are following different date as year beginning.

Date	Weekday	Calendar Yr	Our Yr	Complete Year
05-05-2016	Thursday	2016	2017	2017 starts from 05-05-2016 and ends at 05-03-2017
05-04-2017	Thursday	2017	2018	2018 starts from 05-04-2017 and ends at 05-02-2018
05-03-2018	Thursday	2018	2019	2019 starts from 05-03-2018 and ends at 05-01-2019
05-02-2019	Thursday	2019	2020	2020 starts from 05-02-2019 and ends at 04-29-2020
04/30/2020	Thursday	2020	2021	2021 starts from 04-30-2020 and ends at 04-28-2021
04/29/2021	Thursday	2021	2022	2022 starts from 04-29-2021 and ends at 04-27-2022
04/28/2022	Thursday	2022	2023	2023 starts from 04-28-2022 and ends at 04-26-2023
04/27/2023	Thursday	2023	2024	2024 starts from 04-27-2023 and ends at 04-25-2024

So if i want to find out begining of 2022 means it should fetch 04-29-2021. like wise for 2021 means it should fetch 04-30-2020.

Please suggest some way to find out current year and last year without hardcoding dates.

Now in this case i want to find beginning of 2023(Our year) and beginning of 2022(Our Year). suppose we crossed 04/27/2023 means it should automatically fetch beginning of 2024(Our year) and beginning of 2023(Our year)

Thanks in Advance

[Kurt_Bremser]

Set up a dataset which holds the dates for your special years:

data years;
input our_year (begin end) (:yymmdd10.);
format begin end yymmdd10.;
datalines;
2017 2016-05-05 2017-05-03
2018 2017-05-04 2018-05-02
2019 2018-05-03 2019-05-01
2020 2019-05-02 2020-04-29
2021 2020-04-30 2021-04-28
2022 2021-04-29 2022-04-27
2023 2022-04-28 2023-04-26
2024 2023-04-27 2024-04-25
;

Now you can run your CALL SYMPUTs from that dataset with a WHERE condition selecting the year.

[_el_doredo]

Thank you so much. It helps a lot

[Tom]

Are you year start dates always 364 days apart?  Or do they occasionally through in a "leap" week ?

If not then just add 364 times the number of years to the first date.

%let basedate='05MAY2016'd;

data want;
  do year=2017 to 2024;
     begin=&basedate + 364*(year-2017);
     end = begin+363 ;
     output;
  end;
  format begin end yymmdd10.;
run;
   

PS Your last END dates seems one day off.

[_el_doredo]

Hello,

It's always 364 days...

Thanks a lot

[FreelanceReinh · Accepted solution]

Hello @_el_doredo,

It looks like your "years" are shifted 364-day periods (=52 weeks) -- with only one exception: The last day of your "2024" (note that 2024 is a leap year) is stated to be Thursday (!), 25 Apr 2024, whereas Wednesday, 24 Apr 2024 would be consistent with all other years.

This inconsistency aside, you could always use 'day364.196' as your date interval.

---

@_el_doredo wrote:

So if i want to find out begining of 2022 means it should fetch 04-29-2021. like wise for 2021 means it should fetch 04-30-2020.

Please suggest some way to find out current year and last year without hardcoding dates.

---

data _null_;
call symputx(   'Year_bn',put(intnx('day364.196',today(), 0),YYMMDDN.)); /* result: 20220428 */
call symputx('PY_Year_bn',put(intnx('day364.196',today(),-1),YYMMDDN.)); /* result: 20210429 */
run;

(CALL SYMPUT would work as well, but I prefer the newer CALL SYMPUTX routine.)

Similarly, to determine the beginning of a particular "year" you can use the MDY function in the second argument of INTNX:

data _null_;
do yr=2017 to 2024;
  call symputx(cat('Yr',yr,'_bn'),put(intnx('day364.196',mdy(1,1,yr),0),YYMMDDN.));
end;
run;

[_el_doredo]

@FreelanceReinh , could you please explain more about 'day364.196' format

[FreelanceReinh]

---

@_el_doredo wrote:

@FreelanceReinh , could you please explain more about 'day364.196' format

---

All differences between consecutive start dates of your "years" (as specified in your initial post) are 364 days and, except for the last end date, all end dates are 363 days after their respective start date. This is why I used 364-day intervals in the first argument of the INTNX function. As is described in the documentation Multi-unit Intervals Other Than Multi-week Intervals, 364-day intervals in SAS ('day364') would start by default, e.g., on 1960-01-01 (SAS date value 0), 1960-12-30 (i.e. 364 days after 1960-01-01), 1961-12-29, 1962-12-28, ..., 2015-10-23, 2016-10-21, 2017-10-20, ..., 2023-10-13, ...

Apparently, the default start dates in the calendar years 2016 through 2023 differ from those in your specification (2016-05-05, 2017-05-04, ..., 2023-04-27). But there is a common pattern: each of your eight start dates is 195 days after the default start date in the previous calendar year. For example, 2017-05-04 is 195 days after 2016-10-21. The section Shifted Intervals of the documentation explains that the default start day of a 364-day interval is its first day and that a shift by 195 days means that the shifted interval starts on the 196th day of the interval (196=1+195). The result is the 'day364.196' interval specification that I used in the INTNX function.

[_el_doredo]

Thank you so much for explaining clearly. It's a new learning for me