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

Hi.

I'm writing a very simple query from a table that contains a column (CREATE_DT) which, elsewhere SAS recognizes** as a valid date field without any complaints.  Well, when I add a computed column to select, as an experiment, just to show me the week number of a returned CREATE_DT, it just won't work.  I have a very simple computed column with the following statement:

WEEK(CREATE_DT) AS WeekNum


WeekNum above returns only dashes for all records.  Even MONTH(), which works in WHERE clauses, also returns only dashes.  I tried out several functions in several columns with CREATE_DT as arguments, including DATE, TIME, HOUR, QUARTER, JULIANDATE, WEEKDAY, etc.  Only the functions DATE, HOUR, and SECOND returned values (and the values are correct).  All the rest returned dashes.

 

 

Are these functions broken when used in computed columns...?  How do I get WEEK() and others to work as expected???


**When I say recognizes elsewhere, I mean that (e.g.)

MONTH (t1.CREATE_DT)

returns only records with CREATE_DT in a month of November, as expected.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@complimentarian wrote:

Hi.

I'm writing a very simple query from a table that contains a column (CREATE_DT) which, elsewhere SAS recognizes** as a valid date field without any complaints.  Well, when I add a computed column to select, as an experiment, just to show me the week number of a returned CREATE_DT, it just won't work.  I have a very simple computed column with the following statement:

WEEK(CREATE_DT) AS WeekNum


WeekNum above returns only dashes for all records.  Even MONTH(), which works in WHERE clauses, also returns only dashes.  I tried out several functions in several columns with CREATE_DT as arguments, including DATE, TIME, HOUR, QUARTER, JULIANDATE, WEEKDAY, etc.  Only the functions DATE, HOUR, and SECOND returned values (and the values are correct).  All the rest returned dashes.

 

 

Are these functions broken when used in computed columns...?  How do I get WEEK() and others to work as expected???


**When I say recognizes elsewhere, I mean that (e.g.)

MONTH (t1.CREATE_DT)

returns only records with CREATE_DT in a month of November, as expected.

 


What's the FORMAT and TYPE on the column?

 

If it's a DATETIME field, you need to use DATEPART() first to get the date component. If it's a character field you need to convert it to a numeric field with the appropriate format.

View solution in original post

4 REPLIES 4
Reeza
Super User

@complimentarian wrote:

Hi.

I'm writing a very simple query from a table that contains a column (CREATE_DT) which, elsewhere SAS recognizes** as a valid date field without any complaints.  Well, when I add a computed column to select, as an experiment, just to show me the week number of a returned CREATE_DT, it just won't work.  I have a very simple computed column with the following statement:

WEEK(CREATE_DT) AS WeekNum


WeekNum above returns only dashes for all records.  Even MONTH(), which works in WHERE clauses, also returns only dashes.  I tried out several functions in several columns with CREATE_DT as arguments, including DATE, TIME, HOUR, QUARTER, JULIANDATE, WEEKDAY, etc.  Only the functions DATE, HOUR, and SECOND returned values (and the values are correct).  All the rest returned dashes.

 

 

Are these functions broken when used in computed columns...?  How do I get WEEK() and others to work as expected???


**When I say recognizes elsewhere, I mean that (e.g.)

MONTH (t1.CREATE_DT)

returns only records with CREATE_DT in a month of November, as expected.

 


What's the FORMAT and TYPE on the column?

 

If it's a DATETIME field, you need to use DATEPART() first to get the date component. If it's a character field you need to convert it to a numeric field with the appropriate format.

complimentarian
Calcite | Level 5

Taking your advice,

WEEK(DATEPART(CREATE_DT))

Works as expected.  Thank you!

 

 

But this is still very confusing to me.  Why would WHERE MONTH(CREATE_DT)=11 filter as expected, but a computed column MONTH(CREATE_DT) needs DATEPART() to make it understand the date?

Reeza
Super User

I wouldn't expect them to behave the same and would double check my code and results.

 


@complimentarian wrote:

Taking your advice,

WEEK(DATEPART(CREATE_DT))

Works as expected.  Thank you!

 

 

But this is still very confusing to me.  Why would WHERE MONTH(CREATE_DT)=11 filter as expected, but a computed column MONTH(CREATE_DT) needs DATEPART() to make it understand the date?


 

ballardw
Super User

Date values are number of days from 01Jan1960.

Datetime values are number of seconds from 01Jan1960:00:00:00.

 

So except when your datetime values are kind of close to 1960 many of the date functions will not return any value. The date functions basically do not work for any date after 31 Dec 20000 (yes year 20,000).

Please see:

data junk;
   x=6589335;
   y=year(x);
   put "X as datetime: " x= datetime20. +1 "X as date: " x= mmddyy10. 'year: ' y=;
   x2 = x+100;
   y = year(x2);
   put "X2 as datetime: " x2= datetime20. +1  'year for X2: ' y=;
run;

 

Notice that the date displayed using the mmddyy10 format doesn't display because 5 digits don't fit into the 4 allowed by the format. None of the SAS supplied formats can display a year past 9999 correctly.

After we add 100 days to the date we get an error for the Year() function because the date is out of range.

So generally the date functions will not return anything useful when applied to a DATETIME value.

 

I would examine my data very carefully to see if that "filter" actually worked. Post example data in the form of data step to see others replicate your results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 4 replies
  • 2447 views
  • 0 likes
  • 3 in conversation