Change Value of Weekday Number

[RPYee]

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

Global Trade Services

FedEx Express

[PaigeMiller]

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

[TomKari]

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;

[Reeza · Accepted solution]

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

Global Trade Services

FedEx Express

---