topic Re: How to calculate last thursday in month in SAS Programming

How to calculate last thursday in month

ErikLund_Jensen — Sun, 16 Feb 2020 10:51:04 GMT

As a follow-up to the discussion about calculating last sunday in month, posted by @Brad39 and answered by @Ksharp and @Patrick .

I had a similar problem last week. The task was to compute batch run dates for a lot of jobs as tuesday after last thursday in every month. I solved it with a "brute force" approach, but I am sure it can be done smarter, and it bothers me that I couldn't find a more elegant solution myself.

Here is my code:

* calculate tuesday after last thursday in months 202001 - 202112;
data want (drop=weekday i); 
	format actual_date month_last last_thursday run_date nldatew30.;

	do y = 2020 to 2021;
		do m = 1 to 12;
			actual_date = mdy(m,1,y);

			month_last = intnx('month',actual_date,0,'e');
			weekday = weekday(month_last);
			do i = 0 to 7;
				last_thursday = month_last - i;
				weekday = weekday(last_thursday);
				if weekday = 5 then leave;
				last_thursday = month_last - i;
			end;
			run_date = last_thursday + 5;
			output;

		end;
	end;
run;

Re: How to calculate last thursday in month

ed_sas_member — Sun, 16 Feb 2020 11:47:07 GMT

Hi @ErikLund_Jensen

Here is one approach:

data want (drop= y m); 
	format run_date nldatew30.;

	do y = 2020 to 2021;
		do m = 1 to 12;
			run_date=intnx('week1.5',intnx('month',mdy(m,1,y),0,'e'),0) + 5;
			output;
		end;
	end;
run;

Re: How to calculate last thursday in month

FreelanceReinh — Sun, 16 Feb 2020 12:57:19 GMT

Hi @ErikLund_Jensen,

Wouldn't Ksharp's solution from that other discussion simply suggest:

run_date=nwkdom(5,5,m,y)+5;

Re: How to calculate last thursday in month

ErikLund_Jensen — Sun, 16 Feb 2020 14:44:06 GMT

Hi @FreelanceReinh ans @ed_sas_member

Thank you very much. Both solutions work very well, but I accept @FreelanceReinh 's solution, because he made me aware of the nwkdom function specially designed to do the job.

I strive for keeping myself updated on "what's new" in SAS versions, so I should have known, but there has been so many "what's news" since my introduction to SAS with version 79, and I tend to forget the smart features again, if I don't have any immediate use for them.

I have tried to understand @ed_sas_member 's solution using intnx function. I never tried using modifiers on the interval type, and I can see that it works, but it doesn't make me any wiser, because I cannot understand why. Spending the afternoon on reading the Function Reference documentation of complex time intervals didn't help me much either, so I hope you would take the time to enlighten me.

Take as example May 2021. The intnx function intnx() with WEEK1.5 as interval and a increment of 0 has Monday, 31. May as starting point, so I would expect it to return the 5. day of the week containing the starting point, not the fifth day of the previous week.

Re: How to calculate last thursday in month

FreelanceReinh — Sun, 16 Feb 2020 17:34:02 GMT

@ErikLund_Jensen wrote:

Take as example May 2021. The intnx function intnx() with WEEK1.5 as interval and a increment of 0 has Monday, 31. May as starting point, so I would expect it to return the 5. day of the week containing the starting point, not the fifth day of the previous week.

The logic here is: The first argument 'WEEK1.5' (or equivalently 'WEEK.5') specifies that week intervals will be considered which are shifted (by four days) so that they start on Thursday (=5th weekday for SAS) rather than Sunday (=1st weekday, the default). So, in your example we are looking at the 7-day interval, Thursday to Wednesday, containing the 31 May 2021. We don't increment it (third argument 0) and just take the first day of this interval ('BEGINNING' is the default for the 'alignment' argument). The result is necessarily the last Thursday of the same month, i.e. 27 May 2021.

Re: How to calculate last thursday in month

ErikLund_Jensen — Sun, 16 Feb 2020 18:51:33 GMT

I understand it now thanks to your explanation.