DATA Step, Macro, Functions and more

how to automatically find week number of the month

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 134
Accepted Solution

how to automatically find week number of the month

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


Accepted Solutions
Solution
‎05-24-2017 03:02 PM
Super User
Posts: 11,343

Re: how to automatically find week number of the month

[ Edited ]

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.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: how to automatically find week number of the month

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.

 

Frequent Contributor
Posts: 134

Re: how to automatically find week number of the month

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

 

Solution
‎05-24-2017 03:02 PM
Super User
Posts: 11,343

Re: how to automatically find week number of the month

[ Edited ]

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.

PROC Star
Posts: 1,759

Re: how to automatically find week number of the month

If you want macro code only,

 

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

 

Super User
Posts: 19,782

Re: how to automatically find week number of the month

@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
Super User
Posts: 7,771

Re: how to automatically find week number of the month

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 1,759

Re: how to automatically find week number of the month

Posted in reply to KurtBremser

@KurtBremser  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

Super User
Posts: 5,500

Re: how to automatically find week number of the month

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.

Super User
Posts: 10,023

Re: how to automatically find week number of the month

%let week=%eval(%sysfunc(intck(week,%sysfunc(intnx(month,%sysfunc(today()),0)) ,%sysfunc(today())))+1);
%put &week ;
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 323 views
  • 4 likes
  • 7 in conversation