topic Re: how to transfer date to week of year in SAS Enterprise Guide

how to transfer date to week of year

mengjiawei — Thu, 22 Dec 2016 22:24:16 GMT

Hi guys,

I'm faced a problem when trying to transfer specific date data into "week of year". SAS guidance tells me to use "right click the column--properties--formats--WEEK...". I did accordingly, but the result just shows "****", as the right column in the picture.

can anyone help with this? what is the right way to sovle it?

thank you!

Kira

Re: how to transfer date to week of year

Reeza — Thu, 22 Dec 2016 22:31:41 GMT

That would only change the format for the current display, it wouldn't be saved in any manner.

I would suggest creating a new variable and use the WEEK() function.

Use a computed column and advanced expression.

WEEK(DateVariable, U/W/V).

Check which of the U/W/V options you want when calculating WEEK from a date as there are several different methodologies.

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#n1ka2ulrvrjlasn0z7beco2yrgas.htm

Re: how to transfer date to week of year

ballardw — Thu, 22 Dec 2016 22:58:25 GMT

When manipulating dates first make sure that your original values are SAS date values. A number like 11052016 is not necessarily a date value. If the current display format says the value is $ (character), BEST or F then the value is not likely to actually be the "date" you may think.

If you have a numeric value such as that 11052016 and try to request a data manipulation such as a function call or format you will get an error as the largest data SAS currently handles, 31DEC19999 is 6588970 < 11052016.

The ******* usually means that you have requested SAS to display something that doesn't fit. So I suspect you don't actually have a date or you attempted to display more digits than would fit in the window.

If the value is currently not a date you may need to either re-import you data in a way the you can indicate that it should be a date or calculate a new variable but we would need to see actual values of the variable to suggest appropriate code.

Re: how to transfer date to week of year

mengjiawei — Fri, 23 Dec 2016 14:24:39 GMT

Hi thank you for your reply.

I'm sure this is a date column.

i'm not sure whether you could see my attachment.

i know i might be able to achieve this by change the code, but i'm wondering if there is any way throught query builder.

Re: how to transfer date to week of year

Reeza — Fri, 23 Dec 2016 14:52:56 GMT

Yes, it was provided above.

Use a computed column and advanced expression.

WEEK(DateVariable, U/W/V).

Re: how to transfer date to week of year

mengjiawei — Fri, 23 Dec 2016 15:25:00 GMT

I tried this way, the computed column returns ".", as showed in my attachment.

do you know why?

thanks

Kira

Re: how to transfer date to week of year

ballardw — Fri, 23 Dec 2016 15:34:15 GMT

You may have fallen victim to a common lazy practice of calling datetime values (those that incorporate a date and time) "dates". SAS dates are counts of days, datetimes are seconds. Your example is DATETIME.

So you need to calculate Week(datepart(t1.Requested_date))

Re: how to transfer date to week of year

Reeza — Fri, 23 Dec 2016 16:17:25 GMT

@ballardw wrote:

You may have fallen victim to a common lazy practice of calling datetime values (those that incorporate a date and time) "dates". SAS dates are counts of days, datetimes are seconds. Your example is DATETIME.

So you need to calculate Week(datepart(t1.Requested_date))

@ballardw Is correct, your variable is datetime not date. You can see this in your first snapshot as it has a calendar and clock symbol on the variable requested date.