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
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
For grouping purposes, any assignment of the numbers 1 through 7 ought to work.
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;
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
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: