topic how to automatically find week number of the month in SAS Programming

how to automatically find week number of the month

tparvaiz — Thu, 18 May 2017 22:48:41 GMT

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

Re: how to automatically find week number of the month

ballardw — Thu, 18 May 2017 22:58:49 GMT

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.

Re: how to automatically find week number of the month

tparvaiz — Thu, 18 May 2017 23:10:45 GMT

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

Re: how to automatically find week number of the month

ballardw — Fri, 19 May 2017 14:31:11 GMT

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.

Re: how to automatically find week number of the month

Astounding — Fri, 19 May 2017 00:09:21 GMT

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.

Re: how to automatically find week number of the month

ChrisNZ — Fri, 19 May 2017 03:30:42 GMT

If you want macro code only,

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

Re: how to automatically find week number of the month

Reeza — Fri, 19 May 2017 06:13:21 GMT

@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

Re: how to automatically find week number of the month

Kurt_Bremser — Fri, 19 May 2017 07:54:24 GMT

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.

Re: how to automatically find week number of the month

ChrisNZ — Fri, 19 May 2017 08:28:24 GMT

@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

Re: how to automatically find week number of the month

Ksharp — Fri, 19 May 2017 13:40:51 GMT

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