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

[mvalsamis]

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.

[Tom]

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

[mvalsamis]

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

[SASKiwi]

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.

[Patrick]

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

[Tom · Accepted solution]

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;