turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- SAS VA: WeekNumber from Monday to Sunday

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-13-2015 05:40 AM

Hi everyone!

I need to code a date variable by grouping the data that belong to the same week, using SAS Visual Analytics.

I found the "WeekNumber" function, that returns the number of the week in the current year. The problem is that I need the weeks to start on Mondays and to end on Sundays, whereas the SAS encoding is from Sunday to Saturday (the US encoding). Is there an easy way to prevent this problem to occur?

Giorgio

Accepted Solutions

Solution

05-17-2015
08:37 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_ipr

05-17-2015 08:37 PM

When building your report you could create a conditional calculated numeric data item that returns WeekNumber for Monday to Saturday and WeekNumber - 1 for Sunday. For example, this calculated item text returns the WeekNumber of the variable Date for weeks starting on a Monday :

IF ( DayOfWeek('Date'n) = 1 )

RETURN ( WeekNumber('Date'n) - 1 )

ELSE WeekNumber('Date'n)

The only issue with this calculation is that the days before the first Sunday (or Monday) of the year have a WeekNumber of zero (0) - but that is the same whether your week starts on a Sunday or a Monday as the SAS Visual Analytics WeekNumber function returns 0 for the days between New Year's Day and the first Sunday of the year if New Year's Day is not a Sunday. If you want New Year's Day to always be in the first week of the year then you also need to test what day New Year's Day falls on and which WeekNumber it is in too and then add 1 to the result of the WeekNumber function for all the other dates that year if New Year's Day would fall in WeekNumber zero (0) or was a Sunday.

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_ipr

05-13-2015 08:31 AM

Hi Giorgio,

I'm not aware of a way to change the start of the week based on locale. This is similar to discussions in the group about having financial year calculations.

For your week number requirement this can be achieved using SAS data step manipulation as part of the data preparation prior to loading the table into SAS Visual Analytics. There is a function, intck that can return the week number shifted by an interval so you can have it start on a Monday by using week.2 as the interval. Have a look at the user guide at SAS(R) 9.4 Functions - Intck

For example, using something like INTCK("week.2", MDY(1,1,year(today())),date_column). You may need to change the start date depending on your requirements and also add 1 to the result if you don't want the first partial week to have a result of 0.

Kind Regards,

Michelle

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_ipr

05-13-2015 10:37 AM

Hi Giorgio

As Michelle mentioned it is probably best to prepare the data first. SAS has a WEEK Function that will allow you to compute the week according to different rules. The V option is the way we are used to in Europe.

Bruno

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruno_SAS

05-13-2015 10:52 AM

Ahhhh good point to use the Week function if the start is the beginning of the calendar year. I find the INTCK and INTNX functions are my favourites as they are so versatile. :-)

Cheers,

Michelle

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_ipr

05-13-2015 10:57 AM

Unfortunately, I do not have a SAS licence (but only a Visual Analytics one). Moreover, in order to implement an automatic flow, I need to run all my analysis based on SAS Visual Analytics, where this feature is absent.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_ipr

05-18-2015 09:08 PM

After some more research, I found that **if** you have *Data Administrator* rights, so that you can prepare data using the SAS Visual Data Builder, then you can add calculated column(s) that use SAS functions as part of the expression formula. However, the WEEK function is not one of the formally listed "FUNCTIONS" keywords in the expression builder. So you would have to manually type the formula expression, using SAS syntax, rather than the Visual Analytics formula syntax that is used in the report designer. This works for both SAS source tables as well as imported data because Visual Analytics uses a SAS macro (and the SQL Procedure) to prepare the data tables that ultimately get loaded into the SAS LASR Server. Unfortunately, not everyone can be a Data Administrator, so this option may not always be available when designing a report. Also you might not want to go back and have to load (or reload) a different version of the table just to get something working on a single report, as this could use additional memory or impact on reports that have been built previously. A better place to address the problem would be in the source data table(s) being loaded into Visual Analytics.

Solution

05-17-2015
08:37 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_ipr

05-17-2015 08:37 PM

When building your report you could create a conditional calculated numeric data item that returns WeekNumber for Monday to Saturday and WeekNumber - 1 for Sunday. For example, this calculated item text returns the WeekNumber of the variable Date for weeks starting on a Monday :

IF ( DayOfWeek('Date'n) = 1 )

RETURN ( WeekNumber('Date'n) - 1 )

ELSE WeekNumber('Date'n)

The only issue with this calculation is that the days before the first Sunday (or Monday) of the year have a WeekNumber of zero (0) - but that is the same whether your week starts on a Sunday or a Monday as the SAS Visual Analytics WeekNumber function returns 0 for the days between New Year's Day and the first Sunday of the year if New Year's Day is not a Sunday. If you want New Year's Day to always be in the first week of the year then you also need to test what day New Year's Day falls on and which WeekNumber it is in too and then add 1 to the result of the WeekNumber function for all the other dates that year if New Year's Day would fall in WeekNumber zero (0) or was a Sunday.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cameron

05-18-2015 04:21 AM

Thank you so much, it was what I was looking for!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_ipr

05-18-2015 12:29 PM

Giorgio

You can use the following expression to calculate the week number as it is used in Europe

Format(TreatAs(_Number_, Trunc(( ( ( TreatAs(_Number_, 'startDate'n) - TreatAs(_Number_, DateFromMDY(1, 3, Year(TreatAs(_Date_, ( ( TreatAs(_Number_, 'startDate'n) - DayOfWeek(TreatAs(_Date_, ( TreatAs(_Number_, 'startDate'n) - 1 ))) ) + 4 ))))) ) + DayOfWeek(DateFromMDY(1, 3, Year(TreatAs(_Date_, ( ( TreatAs(_Number_, 'startDate'n) - DayOfWeek(TreatAs(_Date_, ( TreatAs(_Number_, 'startDate'n) - 1 ))) ) + 4 ))))) + 5 ) / 7 ))), 'Z2.0')

Bruno

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruno_SAS

02-13-2017 08:27 AM

Hi Bruno

I also have the problem med week-number and maked a copy of your expression.

But I ha' still a problem. Can you see what my problem is??

'Cosmic Kontakt start' is my 'startdate'

Format(TreatAs(_Number_, Trunc(( ( ( TreatAs(_Number_, ('Cosmic Kontakt start') - TreatAs(_Number_, DateFromMDY(1, 3, Year(TreatAs('Cosmic Kontakt start', ( ( TreatAs(_Number_, 'Cosmic Kontakt start') - DayOfWeek(TreatAs('Cosmic Kontakt start', ( TreatAs(_Number_, 'Cosmic Kontakt start') - 1 ))) ) + 4 ))))) ) + DayOfWeek(DateFromMDY(1, 3, Year(TreatAs('Cosmic Kontakt start', ( ( TreatAs(_Number_, 'Cosmic Kontakt start') - DayOfWeek(TreatAs('Cosmic Kontakt start', ( TreatAs(_Number_, 'Cosmic Kontakt start') - 1 ))) ) + 4 ))))) + 5 ) / 7 ))), 'Z2.0')

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruno_SAS

01-18-2018 10:27 AM

This solved for us a deffect, affecting 20 dashboards. THANKS