I have created a "Day of Week" calculated item from a date field. I'd like to apply a custom sort so that the first day of the week is Monday, rather than Sunday. However, when I right-click on the "Day of Week" category, the "custom sort" option is not available. Any ideas? SAS VA 8.3
Hi!
Try this 🙂
Create a new calculated item (character) and add this code:
IF ( DayOfWeek('Date'n) = 1 )
RETURN 'Sunday'
ELSE (
IF ( DayOfWeek('Date'n) = 2 )
RETURN 'Monday'
ELSE (
IF ( DayOfWeek('Date'n) = 3 )
RETURN 'Tuesday'
ELSE (
IF ( DayOfWeek('Date'n) = 4 )
RETURN 'Wednesday'
ELSE (
IF ( DayOfWeek('Date'n) = 5 )
RETURN 'Thursday'
ELSE (
IF ( DayOfWeek('Date'n) = 6 )
RETURN 'Friday'
ELSE (
IF ( DayOfWeek('Date'n) = 7 )
RETURN 'Saturday'
ELSE '-' ) ) ) ) ) )
Then add the custom sort.
//Fredrik
Hi!
Are you creating a string variable with the weekday name?
If not maybe you should try that, it might solve both the sorting and the difference between the editor and report mode.
//Fredrik
my calculation is
DatePart('Offence TMST'n)
And I am applying the "Day of Week" format to this.
What should I be doing differently?
Thanks!
I deleted the old data item "Offence Day of Week" and created a new one by right-clicking on the "Offence TMST" (datetime) data item and selecting "Duplicate Data Item". I renamed the new item to "Offence Day of Week" and applied the "Day, Date" format.
That gives me what I want - "Offence Days of Week" of "Sunday", "Monday", "Tuesday", etc. However, I have the same problem when I view the report - they change to "03Jan1960", "04Jan1960", "05Jan1960", etc.
And I can't custom sort them.
Help! Very frustrating!
Hi!
Try this 🙂
Create a new calculated item (character) and add this code:
IF ( DayOfWeek('Date'n) = 1 )
RETURN 'Sunday'
ELSE (
IF ( DayOfWeek('Date'n) = 2 )
RETURN 'Monday'
ELSE (
IF ( DayOfWeek('Date'n) = 3 )
RETURN 'Tuesday'
ELSE (
IF ( DayOfWeek('Date'n) = 4 )
RETURN 'Wednesday'
ELSE (
IF ( DayOfWeek('Date'n) = 5 )
RETURN 'Thursday'
ELSE (
IF ( DayOfWeek('Date'n) = 6 )
RETURN 'Friday'
ELSE (
IF ( DayOfWeek('Date'n) = 7 )
RETURN 'Saturday'
ELSE '-' ) ) ) ) ) )
Then add the custom sort.
//Fredrik
Thanks, that would work. I took a different, but similar, path - I created a custom category called "Offence DOW" based on my "Offence Day of Week" values and used them instead.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.