BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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')

View solution in original post

10 REPLIES 10
ballardw
Super User

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
Barite | Level 11

@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
Super User Tom
Super User

@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
Barite | Level 11
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
Super User

@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
Super User Tom
Super User

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
Jade | Level 19

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
Barite | Level 11

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
Super User Tom
Super User

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
Jade | Level 19

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 2459 views
  • 3 likes
  • 4 in conversation