Adding two days to INTNX macro variable date

[TurnTheBacon]

In the precode of my Extract transformation, I need to establish a macro variable that designates Tuesday last week, relative to the date the job is run. If I run it today, it should appear like this: 09OCT12:00:00:00. I'm almost there, but need help with a detail.

This is my current code: %let last_tuesday = %sysfunc(intnx(week, %sysfunc(today()),-1,b), date9.):00:00:00;

Logically enough, it sets the date to 07OCT12:00:00:00. Next I need to add two days to it, to make it Tuesday(3) instead of Sunday(1). However, no matter where I try to insert "+2", it seems to fail. Can someone please advise me how I can edit the above code to make it add two days?

P.S. I've tried using week1.3 and week.3 in the above code, but those only select the Tuesday of the first week of the month no matter when the job is run (02OCT2012:00:00:00 if I run it now).

Thanks.

[LinusH]

week.3 works fine for me:

4          %let last_tuesday = %sysfunc(intnx(week.3, "31oct2012"d,-1,b), date9.):00:00:00;

5         

6          %put &last_tuesday ;

23OCT2012:00:00:00

[TurnTheBacon]

How bizarre. When I run the code below, I get 02OCT12:00:00:00. It should be 09OCT12:00:00:00.

     %let tuesday_lastweek = %sysfunc(intnx(week.3, %sysfunc(today()),-1,b), date9.):00:00:00;

Do anyone have an idea what's wrong?

UPDATE: I can solve this if I can only figure out how to add days manually. If I use the code below, how do I add days? I've tried placing +2 most places, I think.

     %let tuesday_last_week = %sysfunc(intnx(week, %sysfunc(today()), -1,b), date9.):00:00:00;

[data_null__ · Accepted solution]

20         %let tuesday_last_week = %sysfunc(sum(2,%sysfunc(intnx(week,%sysfunc(today()),-1,b))),date9.):00:00:00;

21         %put tuesday_last_week = &tuesday_last_week;

tuesday_last_week = 09OCT2012:00:00:00

[LinusH]

Ok, week.3 doesn't work for your case. If I understand you right, you want to base your calculation on an ordinary week (sun-sat, or mon-sun), but you want to move the date to the Tuesday.

If you run your code, it assumes that it's still the week before, since the week ends on the Tuesday (and today is Monday).

Since the macro compilator is text by default, it does not try to understand the meaning of any operators, function calls etc, if you don'r specifically tell it to do otherwise (like %sysfunc with function calls). For arithmetical operations use %eval() macro function.

[FriedEgg]

%let today=%sysfunc(datetime(),datetime20.);

%put Today is &today;

%let last_tue_day=%sysfunc(intnx(dtweek.3,"&today"dt,0,b),datetime20.);

%put Last Tuesday was &last_tue_day;

Today is 15OCT2012:09:24:00

Last Tuesday was 09OCT2012:00:00:00