Determing dates of previous Monday and Sunday

[Sotarkadin]

Currently I have a report I run where I filter based on the date and 8 days back.

where a.action_dt > (date - INTERVAL '8' DAY)

I am looking to change this that the action_dt will always be between Monday and Sunday of the previous week no matter when I run the report that week.

Desired Result:

where a.action_dt between &monday and &sunday

I am just not sure how to get those variables to always be the previous dates I need based on whatever day I run the report.

[data_null__ · Accepted solution]

You need a shifted time interval.

data _null_;
    ref=today();
    monday = intnx('week1.2',ref,-1,'Begin');
    sunday = intnx('week1.2',ref,-1,'End');
    format _all_ weekdate.;
    put (_all_)(=/);
    run;

ref=Tuesday, May 26, 2015
monday=Monday, May 18, 2015
sunday=Sunday, May 24, 2015

[Sotarkadin]

Thank you so much that relly helped. I do have another question on this same report I am working on.  It's a little different but still revolves around day of week.  I am not sure if I shoudl start another discussion or can it just be answered here.

I am currently joining two tables based on the data an action occurs:

proc sql;

create table ltr_chck as

select a.cm15, a.case_nbr, a.action_dt, a.frd_action, a.user_id_cd, b.action_Dt as ltr_action, b.frd_action as ltr_action

from value_actions_dedup a

left join letter_actions_dedup b

on a.action_dt = b.action_dt

where a.cm15 = b.cm15

and a.user_id_cd NE 'XXXXX';

run;

I am looking to specify the join for different date criteria.   If the a.action_dt is a Friday, Saturday or Sunday, then it needs to look for the b.action_dt of the Monday after.

on a.action_dt = b.action_dt

or if a.ction_dt is Fri, Sat, Sun then b.action_dt = Mon after

[data_null__]

If you have another question start another thread.  Include some sample data that is representative of the join you want to do.  Show have and want.