how to automatically find week number of the month

[tparvaiz]

Hi,

I have a code that's using week number of the month as constant and every time I have to change it manually.

is there  a way to automate it, for example, it I am running the code today (may 18, 2017) then the value of week should automatically set to 3

%let week = 3;

Please advise

Thanks in advance

[ballardw]

With week you have to be very explicit about how you define a week number.

Do you mean from day 1 to day 7 of the month regardless of which day of the week the month starts?

If the day of the week it starts on is important then what day does the week start, Sunday, Monday, Saturday something else?

If the week starts on sunday and the first day of the month is a saturday day which week does it belong to?

Is there a minimum number of days to consider as a week?

Note that if May starts on a Friday then 18 May would be in a week with the 4th Friday of the month.

With all that in mind consider:

data example;
   mydate = '18MAY2017'd;
   myweek = week(mydate) - week((intnx('month',mydate,0,'B'))) +1;
   put mydate= date9. myweek=;
run;

The +1 above is needed because the subtraction (or the intck WEEK function) returns intervals and you want the number.

[tparvaiz]

I tried following and it failed

%let week = week(today()) - week((intnx('month',today(),0,'B'))) +1;

maybe its not the way to define a variable.

Thanks

[ballardw · Accepted solution]

use the data step and call symputx if you must have a macro variable.

If you don't know the proper syntax to call datastep functions such as WEEK, INTNX then time to review basic macro language.

Since the "macro" version for this

%let week = week(today()) - week((intnx('month',today(),0,'B'))) +1;

would require using 5 calls to the macro function %sysfunc() and nested up to 3 levels that line would get VERY UGLY and getting all the () to match is a headach.

Easier

data _null_;

    week = week(today()) - week((intnx('month',today(),0,'B'))) +1;

    call symputx('week',week);

run;

Plus if end up needing other moderately complicated macro variables built then they may all go into the same data step.

[ChrisNZ]

If you want macro code only,

%put week = %eval( %sysfunc(week(%sysfunc(today()))) + 1  
                 - %sysfunc(week(%sysfunc(intnx(month,%sysfunc(today()),0,B)))) );

[Reeza]

@tparvaiz You never stated how you define a week. 

One quick and dirty way is to take the day of month and divide by 7 and either round it up or down depending on your rules. 

data _null_;
    day=day(today());
    week_ceil=ceil(day/7);
    week_floor=floor(day/7);
    call symputx('week_ceil', week_ceil);
    call symputx('week_floor', week_floor);
run;

%put Week (Rounded up): &week_ceil.;
%put Week (Rounded down): &week_floor.;

OUTPUT:

 65         
 66         %put Week (Rounded up): &week_ceil.;
 Week (Rounded up): 3
 67         %put Week (Rounded down): &week_floor.;
 Week (Rounded down): 2

[Kurt_Bremser]

I concur with what the others said.

NEVER, EVER do such a complex calculation directly in the %let macro statement. Use a data _null_ step and call symput or call symputx.

Part of Maxim 11.

[ChrisNZ]

@Kurt_Bremser  I disagree. Purely macro routines, without any L4G, can be called anywhere and are more useful than routines containing statements. Hence my for example this request

[Astounding]

Remember macro language %EVAL function performs integer arithmetic automatically.  Have you tried:

%let week = %eval(("&sysdate9"d + 6) / 7);

I think +6 in the formula is right, but you might need to play with it.

And I can't test right now whether %EVAL will recognize a date literal, but it might.

[Ksharp]

%let week=%eval(%sysfunc(intck(week,%sysfunc(intnx(month,%sysfunc(today()),0)) ,%sysfunc(today())))+1);
%put &week ;