Hi, I am trying to do the following to pass in the system date and subtract the date so the StartDate will read 09MAR2009 and the EndDate will be 15MAR2009. I'm not sure if I am doing this correctly. This will allow me to run the code automatically instead of hard coding a date each time. Any help is appreciated!
CREATE TABLE TD_Mature AS
SELECT distinct t.contract_no AS Matured_TD
FROM ptware.term_deposits t
WHERE t.mature_date between '&StartDate' and '&EndDate'
AND t.cease_date is not null
AND t.mature_date = t.cease_date;
Setting OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC; would likely help reveal SAS behavior when using your macro variables in your PROC SQL code. The macro variable string, when coded within single quote marks, will not be resolved, as compared to coding the macro variable string inside double-quote marks -- this is a SAS standard feature.
I'm not sure what kind of interval you are trying to create, but if you intend to always have Monday to Sunday in the current week, I think you misunderstood the use of the week.X interval. I think that you should stick with week.2, and use the BEGINNING and END with the intnx function: