topic Re: Change Value of Weekday Number in SAS Enterprise Guide

Change Value of Weekday Number

RPYee — Tue, 10 Sep 2019 21:30:16 GMT

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

Re: Change Value of Weekday Number

PaigeMiller — Tue, 10 Sep 2019 21:47:42 GMT

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

Re: Change Value of Weekday Number

TomKari — Tue, 10 Sep 2019 21:59:12 GMT

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;

Re: Change Value of Weekday Number

Reeza — Tue, 10 Sep 2019 22:26:17 GMT

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