SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mvalsamis
Obsidian | Level 7

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):

dateday_of_week_numberweek_of_year_numberacc_week_of_year_number
01JAN2025

4

53?
02JAN2025553?
03JAN2025653?
04JAN2025753?
05JAN202511?
06JAN202521?
...   
26DEC2025651?
27DEC2025751 ?
28DEC2025152?
29DEC2025252?
30DEC2025352?
31DEC2025452?

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

Tom_0-1739393839690.png

 

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

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

mvalsamis
Obsidian | Level 7

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

SASKiwi
PROC Star

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
Opal | Level 21

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

Patrick_0-1739405483815.png

 

 

 

Tom
Super User Tom
Super User

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;

Tom_0-1739393839690.png

 

sas-innovate-white.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.

 

Early bird rate extended! Save $200 when you sign up by March 31.

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 726 views
  • 7 likes
  • 4 in conversation