Desktop productivity for business analysts and programmers

Extract Day of Week name from a Time Stamp field

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

Extract Day of Week name from a Time Stamp field

I have a timestamp field that displays as DDMMMYYYY:HH:MMSmiley FrustratedS.  I need to get to the day of the week (Monday, Tuesday, etc) for this field.  When I insert a computed field and simply try to format this field as DOWNAME9., I get a return of *********.  Can someone point me to a solution for this?  Keep in mind that I am using the Enterprise Guide query builder and have no experience with coding.
I appreciate any help.

 

Thanks

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.

Accepted Solutions
Solution
‎06-29-2018 10:38 AM
Respected Advisor
Posts: 3,293

Re: Extract Day of Week name from a Time Stamp field

[ Edited ]

you want to create a variable that is datepart(timestamp)

 

and then the DOWNAME9. format should work

--
Paige Miller

View solution in original post


All Replies
Solution
‎06-29-2018 10:38 AM
Respected Advisor
Posts: 3,293

Re: Extract Day of Week name from a Time Stamp field

[ Edited ]

you want to create a variable that is datepart(timestamp)

 

and then the DOWNAME9. format should work

--
Paige Miller
Contributor
Posts: 41

Re: Extract Day of Week name from a Time Stamp field

Posted in reply to PaigeMiller

Thanks so much.  I just had a "blonde moment" Smiley Happy  I'm still trying to figure out where and when to just reformat versus when to apply a function before formatting.  

 

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Contributor
Posts: 41

Re: Extract Day of Week name from a Time Stamp field

Posted in reply to PaigeMiller

Hi again Paige,

So, I'm getting what I wanted in the way of the weekday name, but I still have an issue. 

 

This computed column is DayName and is shown as

datepart(t1.arrdate)  which is formatted to DOWNAME9.

 

After this step, I want to use DayName to determine way the data is grouped.  (This is hard to explain)  In short,  I assign numbers to the days based on their name (1-7).  I change the order in which they are assigned based on the day an issue happened.  So, if we had an issue on Tuesday, Tuesday would be assigned 1, Wednesday would be 2, Thursday is 3, until Monday becomes 7.  The reasoning is that I have 4 weeks worth of data and I want like-days grouped together.  In this scenario,  all 4 Tuesdays will be grouped together in the outcome.  That way, I can compare the day of the issue (Current Tuesday) with the previous three Tuesdays.

 

The code in the computed column is:

CASE
WHEN (datepart(t1.arrdate))='MONDAY' THEN 7
WHEN (datepart(t1.arrdate))='TUESDAY' THEN 1
WHEN (datepart(t1.arrdate))='WEDNESDAY' THEN 2
WHEN (datepart(t1.arrdate))='THURSDAY' THEN 3
WHEN (datepart(t1.arrdate))='FRIDAY' THEN 4
WHEN (datepart(t1.arrdate))='SATURDAY' THEN 5
ELSE 6
END

 

When trying to create the computed column, I get the following: 

ERROR: Expression using equals (=) has components that are of different data types.

 

Keep in mind that I am migrating a program previously written for Hyperion.  There may be an easier way to do this in SAS.  I'm too new to really know all that is available at this point.  I only use the designer in Enterprise Guide, too, so that may have limitations.

 

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Respected Advisor
Posts: 3,293

Re: Extract Day of Week name from a Time Stamp field

WHEN WEEKDAY(datepart(t1.arrdate))=2 THEN 7

 

See the documentation for the WEEKDAY function

http://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=p0...

--
Paige Miller
Super User
Posts: 2,075

Re: Extract Day of Week name from a Time Stamp field

will this help?

 

data want;
var=datetime();/*today's datetime value for example*/
dayofweek=put(datepart(var),weekdate9. -l);
format var datetime20.;
run;
Contributor
Posts: 41

Re: Extract Day of Week name from a Time Stamp field

Posted in reply to novinosrin

Thanks for the help.  Paige reminded me of what I'd missed Smiley Happy  I posted another issue here, so if you have any suggestions for my problem, I'm open!

 

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Super User
Posts: 2,075

Re: Extract Day of Week name from a Time Stamp field

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

 

I love that line. Smiley Happy  Did anybody assume so ? I would like to meet that Einstein 

Contributor
Posts: 41

Re: Extract Day of Week name from a Time Stamp field

Posted in reply to novinosrin

I've run into a few condescending derrieres here. Smiley Happy  

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Contributor
Posts: 41

Re: Extract Day of Week name from a Time Stamp field

Posted in reply to novinosrin

This helped me with another issue... how to comment inside the code for my computed field.   THANKS Smiley Very Happy

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Super User
Posts: 2,075

Re: Extract Day of Week name from a Time Stamp field

[ Edited ]

Ok try

 

CASE
WHEN upcase(put(datepart(t1.arrdate),DOWNAME9.))='MONDAY' THEN 7

ELSE 6
END

 

If the above works. please repeat for all other when conditions as well

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 164 views
  • 2 likes
  • 3 in conversation