week calculation for indian financial calender

[umashankersaini]

Hi Team,

I need to derive a week_id variable based on dates. As SAS starts calculating week from 01JAN but i want that should start with 01APR.

Looking for following result.

     Date            Week_id

01APR2019        1

13MAY2019        7

06JAN2020         42

following code is working fine till 31DEC2019 but for jan it is not working. Kindly help.

DATA A;

Week_id=week(date)-12;

run;

[Kurt_Bremser]

You need to make the calculation conditional:

data have;
input date :date9.;
format date yymmddd10.;
datalines;
01APR2019
13MAY2019
06JAN2020
;

data want;
set have;
if date >= mdy(4,1,year(date))
then week_id = week(date) - 12;
else week_id = week(date) + 41;
run;

proc print data=want noobs;
run;

The result matches your example:

      date    week_id

2019-04-01        1  
2019-05-13        7  
2020-01-06       42  

But I'm not completely sure it works for all years, the values of 12 and 53 may need to be adapted depending on the weekday of January 1 or April 1.

[ScottBass]

In data warehousing this is usually handled by a date dimension. 

In SAS, you can do something similar with a format (or even a date dimension if you want to join on date).

All days from 01JAN1960 to 31DEC2099 are only 51K rows...not *that* big for a format.

Perhaps you can run with this code and modify it to suit your requirements.  For example, I don't know the day your week starts (eg. Sun or Mon):

data cntlin;
   length fmtname $32 start end label 8;
   length weekday 8;  * ignored by proc format but useful for debugging ;
   fmtname="financialweek";
   retain label 0;
   do start="01APR1960"d to "31DEC2099"d;
      end=start;
      if month(start)=4 and day(start)=1 then label=1;
      weekday=weekday(start);  * not needed for debugging ;
      if weekday=1 then label+1;
      output;
   end;
   format start end date9.;
run;

proc format cntlin=cntlin;
run;

data test;
   set cntlin;
   financialweek=input(put(start,financialweek.-L),8.);
run;
   

Also:

Formats can accept character or numeric arguments, and always return character results.

Informats always accept character arguments, and can return character or numeric results.

Since dates are numeric, and your desired week number is numeric, you'll need to convert either way.  I chose to create a numeric format, and use the input function to convert the format output to numeric.

[Patrick]

@umashankersaini 

Does week 1 of a year always start on the first Sunday in April? Is that the logic you need?

[Ksharp]

Week_id=week(date-('01apr1960'd - '01jan1960'd));

[ballardw]

---

@umashankersaini wrote:

Hi Team,

I need to derive a week_id variable based on dates. As SAS starts calculating week from 01JAN but i want that should start with 01APR.

Looking for following result.

     Date            Week_id

01APR2019        1

13MAY2019        7

06JAN2020         42

following code is working fine till 31DEC2019 but for jan it is not working. Kindly help.

DATA A;

Week_id=week(date)-12;

run;

---

You say that this works for 31DEC2019

DATA A;
Week_id=week(date)-12;
run;

Since that returns 40, how does  06JAN2020  become week 42 when it is only 7 days later? I really think that you mean week 41.

Here is an approach using the INTCK function. There a couple of different ways to treat start of week boundaries with the INTCK function, using the 'D' discrete or 'C' continuous method option.

data example;
   do date='01APR2019'd to '31MAR2021'd ;
      week = intck('week',mdy(4,1, year(date) - (month(date)<4) ),date,'D')+1;
      week2 = intck('week',mdy(4,1, year(date) - (month(date)<4) ),date,'C')+1;
      output;
   end;
   format date  date9.;
run;

BEAWARE: From the documentation

dates are not in the same discrete interval.)

Using the discrete method, WEEK intervals are determined by the number of Sundays, the default first day of the week, that occur between the start-date and the end-date, and not by how many seven-day periods fall between those dates. To count the number of seven-day periods between start-date and end-date, use the continuous method.

So since your "fiscal year" is likely to start on a day other than SUNDAY  you may have to consider what the definition of your actual start day of the first week might be.

For a number of purposes as "week" is a poor measure due to the dependency of start of the week, relationship to start of a year and the fact that years do not actually divide evenly into weeks 365/7 = 52.14 "weeks" or 366/7 = 52.28 "weeks"

[umashankersaini · Accepted solution]

Hi Team,

I am able to derive the desired output by following code:

Data _null_;
SD = intnx('week',today(),-1,'b')+1;
WK_ID=intck('week',intnx('month12.4',SD,0),SD)+1;
call symput('WK_ID',WK_ID);
Run;