Desktop productivity for business analysts and programmers

Easy : How to compare date field to fixed date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Easy : How to compare date field to fixed date

[ Edited ]

This is easy but I am struggling with it.

 

Field t1.TrnDt is a date but the following syntax gives an error.  I am try to create a new computed column.  What's wrong with this?

 

 

CASE  WHEN   t1.TrnDt >  "01/01/2011" THEN "Late"  ELSE  "Early"   END


Accepted Solutions
Solution
‎02-13-2017 11:41 AM
Super User
Posts: 11,134

Re: Easy : How to compare date field to fixed date

SAS requires literal dates in the form of ddMONyy or ddMONyyyy, in quotes and followed by D to indicate date

 

So 

CASE  WHEN   t1.TrnDt >  '01Jan2011'd ....

You can use '1Jan2011'd but I generally use the 01Jan so I know I didn't actually mean 10Jan and just missed a digit.

 

There are similar rules for Time or DATETIME literals.

View solution in original post


All Replies
Solution
‎02-13-2017 11:41 AM
Super User
Posts: 11,134

Re: Easy : How to compare date field to fixed date

SAS requires literal dates in the form of ddMONyy or ddMONyyyy, in quotes and followed by D to indicate date

 

So 

CASE  WHEN   t1.TrnDt >  '01Jan2011'd ....

You can use '1Jan2011'd but I generally use the 01Jan so I know I didn't actually mean 10Jan and just missed a digit.

 

There are similar rules for Time or DATETIME literals.

Occasional Contributor
Posts: 7

Re: Easy : How to compare date field to fixed date

Easy when you know how !!  Thanks again.

Occasional Contributor
Posts: 7

Re: Easy : How to compare date field to fixed date

[ Edited ]

Aaagh!  I spoke too soon.

 

The following date (cut and pasted from SAS)

 

t1.TrnDt = 12JUN2007:00:00:00.000

 

The above date gives an "After" result to the following calculated field;

CASE WHEN  t1.TrnDt >=  '01Jan2011'd      THEN "After" ELSE  "Before" END

 

But surely 2007 is BEFORE 2011 ???   Why does my case statement fail??

Super User
Posts: 11,134

Re: Easy : How to compare date field to fixed date

The value 12JUN2007:00:00:00.000 is a DATETIME value. SAS datetimes are counts of seconds and SAS dates are counts of days.

So either use something like "12JUN2007:00:00:00"DT OR compare the datetpart of the value to literal, which would probably be your intention:

 

CASE WHEN  datepart(t1.TrnDt)  >=  '01Jan2011'd      

one of my pet peeves are programmers that call datetime values dates or follow what seems to be a Microslop approach of defaulting things to datetime when you actually want dates.

 

Occasional Contributor
Posts: 7

Re: Easy : How to compare date field to fixed date

Thanks - That's it. Much appreciated.
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 606 views
  • 2 likes
  • 2 in conversation