topic Calculate number of week in accounting year based on number of week in calendar year in SAS Programming

Calculate number of week in accounting year based on number of week in calendar year

mvalsamis — Wed, 12 Feb 2025 17:22:33 GMT

Hello to the team! I have created a table with all the dates from 01JAN1950 to 31DEC2100, which is used as a date dimension table in a dimensional model. I show here a simplified version of the table (I am using dates within 2025):

date	day_of_week_number	week_of_year_number	acc_week_of_year_number
01JAN2025	4	53	?
02JAN2025	5	53	?
03JAN2025	6	53	?
04JAN2025	7	53	?
05JAN2025	1	1	?
06JAN2025	2	1	?
...
26DEC2025	6	51	?
27DEC2025	7	51	?
28DEC2025	1	52	?
29DEC2025	2	52	?
30DEC2025	3	52	?
31DEC2025	4	52	?

Where

DATE: the date

DAY_OF_WEEK_NUMBER: the number of day in a week (1=Sunday, 7=Saturday)

WEEK_OF_YEAR_NUMBER: number of week in the calendar year

ACC_WEEK_OF_YEAR_NUMBER: number of week in the accounting year.

I have not figured out yet how to calculate the accounting year week number. I suppose that I will have to create it based on the number of the week in the calendar year, which I do have.

Here is a simplified code to create the table (the actual table has much more variables and observations):

data dim_date;
  do date='01jan2025'd to '31dec2025'd;
        day_of_week_number=weekday(date);
        week_of_year_number=week(date);
       /**** Here I need a statement to calculate 
              acc_week_of_year_number *******/
        output;
  end;
run;

Does anyone have any idea on how to calculate that variable? Thank you in advance.

Re: Calculate number of week in accounting year based on number of week in calendar year

Tom — Wed, 12 Feb 2025 17:35:06 GMT

I don't see where you defined what an accounting year is.

Re: Calculate number of week in accounting year based on number of week in calendar year

mvalsamis — Wed, 12 Feb 2025 19:29:41 GMT

Sorry for the omission, and I just remembered it. In this case, the accounting year runs from 1st July to 30th June.

Re: Calculate number of week in accounting year based on number of week in calendar year

SASKiwi — Wed, 12 Feb 2025 19:42:15 GMT

You should also check out the three methods for calculatng week number as implemented in the WEEK function to decide which is the appropriate one for you.

Re: Calculate number of week in accounting year based on number of week in calendar year

Tom — Wed, 12 Feb 2025 20:57:26 GMT

Why not just use INTCK() function?

data dim_date;
  do date='01jan2025'd to '07JAN2025'd
         ,'25jun2025'd to '04JUL2025'd
         ,'25dec2025'd to '31dec2025'd;
    year = year(date);
    fyear = year - (month(date)<7) ;
    day_of_week_number=weekday(date);
    week_of_year_number=week(date);
    acc_week_of_year_number=1+intck('week',mdy(7,1,fyear)-1,date);
    output;
  end;
  format date date9.;
run;

Re: Calculate number of week in accounting year based on number of week in calendar year

Patrick — Thu, 13 Feb 2025 00:12:59 GMT

@mvalsamis Under the assumption that the first day of the first week always starts on July 1st, irrespective of the day of the week, the following should work.

data dim_date;
  format date date9.;
/*   do date='01jan2025'd to '31dec2025'd; */
  do date='27jun2025'd to '08jul2025'd;
    day_of_week_number=weekday(date);
    week_of_year_number=week(date);
    /* financial week with count starting 01July<year> irrespective of day of week */
    fin_week=int((date-intnx('year.7', date, 0, 'b'))/7)+1;
    output;
  end;
run;

proc print data=dim_date;
run;