Quartz | Level 8

## Change Value of Weekday Number

I have a series of dates for which I need to determine the day of week said date falls on, sum a value for each day of week (pivot), and sort totals from Monday thru Sunday.  I have a calculated field (day_nbr) which uses "datepart" and is formatted as "Weekday1.".  It automatically sets Sunday as a value of 1 when I need it to be 7.  I tried a case statement testing "day_nbr", but it's not working.  Any suggestions on how to make the day_nbr for Sunday be 7, then subtract one from all the others, for grouping purposes?

Thanks in advance for any assistance!

Rita Yee

Project Engineer

FedEx Express

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Change Value of Weekday Number

Assuming you're using a calculated field in the query builder?

Try some basic math then:

``8 - weekday(datepart(dateVariable))``

I'm assuming you're using DATEPART to first convert your datetime to a date and then you use the WEEKDAY function or format to create your dates?

@RPYee wrote:

I have a series of dates for which I need to determine the day of week said date falls on, sum a value for each day of week (pivot), and sort totals from Monday thru Sunday.  I have a calculated field (day_nbr) which uses "datepart" and is formatted as "Weekday1.".  It automatically sets Sunday as a value of 1 when I need it to be 7.  I tried a case statement testing "day_nbr", but it's not working.  Any suggestions on how to make the day_nbr for Sunday be 7, then subtract one from all the others, for grouping purposes?

Thanks in advance for any assistance!

Rita Yee

Project Engineer

FedEx Express

3 REPLIES 3
Diamond | Level 26

## Re: Change Value of Weekday Number

For grouping purposes, any assignment of the numbers 1 through 7 ought to work.

--
Paige Miller
Onyx | Level 15

## Re: Change Value of Weekday Number

Hi, Rita

SAS has a feature called "picture" formats. Believe it or not, the day of week default is 1 for Monday. This code snippet should help you.

Tom

``````proc format;
picture dwf
low-high = '%u' (datatype=date);
run;

data Have;
InDay = '9sep2019:00:00:00'dt;
format InDay datetime.;
day_nbr = datepart(InDay);
format day_nbr dwf.;
output;
run;``````
Super User

## Re: Change Value of Weekday Number

Assuming you're using a calculated field in the query builder?

Try some basic math then:

``8 - weekday(datepart(dateVariable))``

I'm assuming you're using DATEPART to first convert your datetime to a date and then you use the WEEKDAY function or format to create your dates?

@RPYee wrote:

I have a series of dates for which I need to determine the day of week said date falls on, sum a value for each day of week (pivot), and sort totals from Monday thru Sunday.  I have a calculated field (day_nbr) which uses "datepart" and is formatted as "Weekday1.".  It automatically sets Sunday as a value of 1 when I need it to be 7.  I tried a case statement testing "day_nbr", but it's not working.  Any suggestions on how to make the day_nbr for Sunday be 7, then subtract one from all the others, for grouping purposes?

Thanks in advance for any assistance!

Rita Yee

Project Engineer