- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I don't see where you defined what an accounting year is.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry for the omission, and I just remembered it. In this case, the accounting year runs from 1st July to 30th June.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;