topic Re: Extract Day of Week name from a Time Stamp field in SAS Enterprise Guide

Extract Day of Week name from a Time Stamp field

RPYee — Fri, 29 Jun 2018 14:28:49 GMT

I have a timestamp field that displays as DDMMMYYYY:HH:MM:SS. 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

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

PaigeMiller — Fri, 29 Jun 2018 14:33:51 GMT

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

and then the DOWNAME9. format should work

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

novinosrin — Fri, 29 Jun 2018 14:35:11 GMT

will this help?

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

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

RPYee — Fri, 29 Jun 2018 14:44:48 GMT

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

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

RPYee — Fri, 29 Jun 2018 15:38:56 GMT

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.

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

RPYee — Fri, 29 Jun 2018 15:44:49 GMT

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

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

PaigeMiller — Fri, 29 Jun 2018 16:09:28 GMT

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=p0ahi8tk3trkv5n173sil9hd7c62.htm&locale=en

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

novinosrin — Fri, 29 Jun 2018 17:21:47 GMT

"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. 🙂 Did anybody assume so ? I would like to meet that Einstein

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

RPYee — Fri, 29 Jun 2018 17:25:27 GMT

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

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

RPYee — Fri, 29 Jun 2018 17:27:27 GMT

I've run into a few condescending derrieres here. 🙂

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

novinosrin — Fri, 29 Jun 2018 17:30:55 GMT

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