Output week ending Tuesday from a Date colum

[aazzarello]

I'm trying to get the last day of the week ending Tuesday for a Date column in my dataset. I've been using the intnx function with the optional 'e' identifier at the end, but I get the week ending Saturday. Just subtracting days from the output doesn't quite work either due to some days falling on different weeks. Code below:

proc sql;
create table erewq as 
select 
intnx('week',t1.'Period Dt'n , 0, 'e') as DTM2 format=date9.,
t1.'period dt'n
from WORK.EDJEC t1
;quit;

Is there another function that would perform this better, or a different way to use the intnx function? Any help would be appreciated.

[FreelanceReinh · Accepted solution]

Hello @aazzarello,

Shift the start of the week to Wednesday by using 'week.4' instead of 'week' in the first argument of the INTNX function, then the weeks will end on Tuesday as desired (see documentation of the shift index).