BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RPYee
Quartz | Level 8

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

___________________________________
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
Reeza
Super User

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


 

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
TomKari
Onyx | Level 15

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
Super User

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


 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1520 views
  • 0 likes
  • 4 in conversation