topic Re: week calculation for indian financial calender in SAS Programming

week calculation for indian financial calender

umashankersaini — Thu, 06 Jun 2019 06:52:03 GMT

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;

Re: week calculation for indian financial calender

Kurt_Bremser — Thu, 06 Jun 2019 07:18:55 GMT

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.

Re: week calculation for indian financial calender

ScottBass — Thu, 06 Jun 2019 07:23:17 GMT

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.

Re: week calculation for indian financial calender

Patrick — Thu, 06 Jun 2019 09:42:25 GMT

@umashankersaini

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

Re: week calculation for indian financial calender

Ksharp — Thu, 06 Jun 2019 13:23:10 GMT

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

Re: week calculation for indian financial calender

ballardw — Thu, 06 Jun 2019 15:24:18 GMT

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

Re: week calculation for indian financial calender

umashankersaini — Tue, 25 Jun 2019 18:31:56 GMT

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;