Last Wednesday AND Last Thursday (that is before Last Wednesday)

[Ronein]

Hello

For each date in the data set I want to :

1- Last Wednesday

2-Last Thursday (that is before Last Wednesday)

For example:

For 15JAN2023 (Sunday) last Wednesday was 11JAN2023 and last Thursday(before last Wednesday) was 05JAN2023

What is the way to calculate it and add 2 desired columns :

Last_WED

LAST_Thu_Before_Last_WED

data have;
format date ddmmyy10.;
input date : date9.;
cards;
15JAN2023
27MAR2023
01JAN2023
19NOV2023
;
Run;

[PaigeMiller · Accepted solution]

Last Thursday before last Wednesday is easy. Once you find last Wednesday, then you subtract 6.

To get last Wednesday, the INTNX function will do this, if you ask for the beginning of a week that contains a specific date using the WEEK.4 interval. (the 4 indicates Wednesday)

[Patrick]

One way to go:

data have;
  format date lastWedDt lastThuDt weekdatx.;
  input date : date9.;
  lastWedDt=intnx('week.4',date,0,'b');
  lastThuDt=intnx('week.5',lastWedDt,0,'b');
  cards;
15JAN2023
27MAR2023
01JAN2023
19NOV2023
;

proc print data=have;
run;

[Ronein]

Thanks,

However for 15JAN2023 I got last WED 10.01.2023  but it should be 11.01.2023

data want;
set have;
last_WED=Intnx('week.4',date,-1,'end');
Last_Thu_Before_Wed=last_WED-6;
last_WED_WeekDay=Weekday(last_WED);
Last_Thu_Before_Wed_WeekDay=Weekday(Last_Thu_Before_Wed);
format last_WED Last_Thu_Before_Wed  ddmmyy10.;
Run;
 

[Patrick]

@Ronein If you look at the code I've posted and the result it returned then you'll see that I've used the week.4 interval same as @PaigeMiller proposed.

If you get another result with this interval then you need to share your code.

[Ronein]

It is working 100%

data want;
set have;
last_WED=intnx('week.4',date,0,'b');
Last_Thu_Before_Wed=last_WED-6;
last_WED_WeekDay=Weekday(last_WED);
Last_Thu_Before_Wed_WeekDay=Weekday(Last_Thu_Before_Wed);
format last_WED Last_Thu_Before_Wed  ddmmyy10.;
Run;

[PaigeMiller]

last_WED=Intnx('week.4',date,-1,'end');

The -1 indicates you want the last Wednesday of the previous week, which is not what you asked for. How could you change this to get the last Wednesday of the current week?

[Ronein]

Great,
Use 0 and beginning
last_WED=intnx('week.4',date,0,'b');