INTNX 'Weekday' Function Issue

[elwayfan446]

Hello.

I am trying to create a variable from data in a data set with dates.  When using the intnx('weekday'...) function, if the previous weekday was in the month/year prior to the beginning of the month I am looking for, it goes back to that date.  I am trying to figure out a way to either just leave it at the first of the month if it is on the weekend or take it forward to the first weekday.  For instance, January 1st 2022 was on Saturday.  Using the weekday function pulls December 31st even when indicating the month should be at the beginning.  How can I handle this?

proc sql;
	select intnx('weekday',intnx('month',max(date1),-2,'b'),0) 
	into :last2monthbegindate
	from table 1 
	where name in ('name1','name2','name2')
;
quit;

The result of this query above is 12/31/2021 instead of 1/1/2022 (or 1/3/2022, the first weekday in January).  If adjusting the code to look back to February, it gives the desired results because February 1 was a weekday.

[ballardw]

Your really need to provide example of input/output. When I try  your intnx code with a date like 04Jan2022 the result I get is 01NOV2021. Which is not anywhere close to your stated 12/31/2021. The Nov date is more of what I expect given that your inner intnx goes back 2 months to the beginning. Since you are "advancing" the 'weekday' by 0 that does not change the result from inner intnx result. So maybe you need to edit the code you have shown for your intnx call.

data example;
  date1='04Jan2022'd;
  date2 = intnx('weekday',intnx('month',max(date1),-2,'b'),0) ;
  format date1 date2 date9.;
run;

I am not even sure exactly what your desired result is expected to be.

[elwayfan446]

@ballardw 

max(date1) represents the current date in my query so today would be 3/3/2022.  Going back 2 months looking for the beginning of that month I would be looking for 1/1/2022, instead I am getting 12/31/2021.

[Tom]

---

@elwayfan446 wrote:

@ballardw 

max(date1) represents the current date in my query so today would be 3/3/2022.  Going back 2 months looking for the beginning of that month I would be looking for 1/1/2022, instead I am getting 12/31/2021.

---

I don't understand.  If you want to go to the beginning of the month why are you using WEEKDAY interval?  You should use MONTH interval.

[elwayfan446]

Sorry, I am confusing the matter I guess. What I really need is the first weekday of every month. So if I use the MONTH interval, how can I jump to the first weekday if the 1st of the month is on a weekend?

[ballardw]

---

@elwayfan446 wrote:
Sorry, I am confusing the matter I guess. What I really need is the first weekday of every month. So if I use the MONTH interval, how can I jump to the first weekday if the 1st of the month is on a weekend?

---

Please provide some concrete example of start date and the expected result.

If the first day of a month is a weekend you can't go "back" at all and still be in the same month. But I am still not sure what you want.

I suspect that you will need a CASE statement to conditionally advance a date if the first day of the month is on a weekend.

[Tom]

So what date to you want when the normal result goes back to the previous month? 

Do you want the first of the month, which is on a week-end?  Take the MAX() of the two dates.

Or the first weekday of the month? Use INTNX() again to move from first of month to first weekday of month.

data have;
  input date :yymmdd. ;
  format date yymmdd10.;
cards;
2022-01-01
2022-01-15
2022-02-01
;

data want;
  set have;
  date_previous_wkday = intnx('weekday',date,0);
  date_day_1 = intnx('month',date,0,'b');
  date_max= max(date_day_1,date_previous_wkday);
  if date_previous_wkday>=date_day_1 then date_want=date_previous_wkday;
  else date_want=intnx('weekday',date_day_1,1);
  format date: yymmdd10.;
run;

Results

                     date_previous_
Obs          date        wkday         date_day_1      date_max     date_want

 1     2022-01-01      2021-12-31      2022-01-01    2022-01-01    2022-01-03
 2     2022-01-15      2022-01-14      2022-01-01    2022-01-14    2022-01-14
 3     2022-02-01      2022-02-01      2022-02-01    2022-02-01    2022-02-01

[FreelanceReinh · Accepted solution]

Hello @elwayfan446,

---

@elwayfan446 wrote:

I am trying to figure out a way to either just leave it at the first of the month if it is on the weekend or take it forward to the first weekday.

...

select intnx('weekday',intnx('month',max(date1),-2,'b'),0)

---

If the first day of the month two months back is acceptable, then just omit the outer INTNX call. However, if you want to "take it forward to the first weekday" of that month, then modify the outer INTNX call as follows:

select intnx('weekday12w',intnx('month',max(date1),-2,'b'),0,'e')

[elwayfan446]

This is exactly what I needed.  Thanks so much.

Sorry to everyone for the confusion of my question.  I didn't word it very well but I appreciate everybody's help.

[Tom]

Not sure I understand why taking the end of the work week that runs from Tue-Sat works, but it does.

data have;
  input weekday date :yymmdd.;
  format date yymmdd10.;
cards;
1 2000-10-01
2 2000-05-01
3 2000-08-01
4 2000-03-01
5 2000-06-01
6 2000-09-01
7 2000-07-01
;

data want;
  set have;
  date_weekday = intnx('weekday12w',date,0,'e');
  diff = date_weekday - date ;
  format date: yymmdd10.;
run;

                                     date_
Obs    weekday          date       weekday    diff

 1        1       2000-10-01    2000-10-02      1
 2        2       2000-05-01    2000-05-01      0
 3        3       2000-08-01    2000-08-01      0
 4        4       2000-03-01    2000-03-01      0
 5        5       2000-06-01    2000-06-01      0
 6        6       2000-09-01    2000-09-01      0
 7        7       2000-07-01    2000-07-03      2

[FreelanceReinh]

---

@Tom wrote:

Not sure I understand why taking the end of the work week that runs from Tue-Sat works, but it does.

---

Yes, at first glance it seems a bit counterintuitive, but when I read the documentation "Intervals by Category" (saying that the default, WEEKDAY17W, means that Friday, Saturday, Sunday are counted as the same day) and also tested the example WEEKDAY35W mentioned there (where apparently Monday and Tuesday are counted as the same day and, separately, also Wednesday and Thursday), I realized that WEEKDAY12W in conjunction with increment 0 and the "e" modifier was exactly what the OP needed: Whenever the initial target date falls into {Saturday, Sunday, Monday}, INTNX returns the last subinterval of this three-day interval (treated "as the same day"), i.e., Monday. All other days of the week are left unchanged.