SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Adding two days to INTNX macro variable date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Adding two days to INTNX macro variable date

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.


Accepted Solutions
Solution
‎10-15-2012 06:51 AM
Respected Advisor
Posts: 3,777

Re: Adding two days to INTNX macro variable date

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

View solution in original post


All Replies
Super User
Posts: 5,256

Re: Adding two days to INTNX macro variable date

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

Data never sleeps
Frequent Contributor
Posts: 89

Re: Adding two days to INTNX macro variable date

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;

Solution
‎10-15-2012 06:51 AM
Respected Advisor
Posts: 3,777

Re: Adding two days to INTNX macro variable date

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

Super User
Posts: 5,256

Re: Adding two days to INTNX macro variable date

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.

Data never sleeps
Trusted Advisor
Posts: 1,300

Re: Adding two days to INTNX macro variable date

%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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 3206 views
  • 0 likes
  • 4 in conversation