topic SAS and Use of Date Functions with ODBC Data in SAS Procedures

SAS and Use of Date Functions with ODBC Data

JDMTX — Thu, 03 Feb 2011 20:40:32 GMT

I am working with data from MS SQL Server that is surfaced in SAS as a data library via SAS Access for ODBC and wanted to know if there is a system option that treats the MS SQL data type of DATETIME as a SAS date instead of a SAS DATETIME. Right now I am having to use the datepart function to extract the date prior to doing any date comparisons. ANy ideas or am I stuck with using the datepart function?

Re: SAS and Use of Date Functions with ODBC Data

Doc_Duke — Thu, 03 Feb 2011 20:57:45 GMT

Stuck with Datetime. Welcome to the club.

Re: SAS and Use of Date Functions with ODBC Data

Peter_C — Fri, 04 Feb 2011 09:08:30 GMT

I understand how datepart() can damage performance, and so, why you want to avoid it: here are some tips
1 compare datetime columns with date constants extended into datetime constants like[pre] where SQL_date_column between "&date1:0:0:0"dt and "&date2:0:0:0"dt[/pre] with just date constants in &date1 and &date2

2 for the INTNX() function there are DTxxxxx intervals

3 for formatting there are DTxxxxx formats like DTDATE9 to display just the date part of a datetime value. There are also some NLxxxxx formats which might help with datetime values.

It is not a complete solution but deals with some of the trouble.

PeterC

Re: SAS and Use of Date Functions with ODBC Data

JDMTX — Sat, 05 Feb 2011 21:58:45 GMT

Peter -

Based upon your earlier reply, is the following statement true: "Even if I apply a format like DTDATE9. it is necessary to treat the underlying value as a datetime value when applying a subseting if statment in a data step as it is not possible to use a date constant short of creating a new variable that contains only the datepart or extend a date constant into a datetime constant (example: "&date2:0:0:0"dt)."

Re: SAS and Use of Date Functions with ODBC Data

Peter_C — Sat, 05 Feb 2011 23:56:10 GMT

> Peter -
>
> Based upon your earlier reply, is the following
> statement true: "Even if I apply a format like
> DTDATE9. it is necessary to treat the underlying
> value as a datetime value when applying a subseting
> if statment in a data step as it is not possible to
> use a date constant short of creating a new variable
> that contains only the datepart or extend a date
> constant into a datetime constant (example:
> "&date2:0:0:0"dt)."

I consider this true, as I think "&date2:0:0:0"dt will use that date constant correctly.

I think this extract
> ............................. it is not possible to
> use a date constant
is false
My reasoning for working with datetime values in data step or sql programming :
avoid functions (like datepart) that might cause an entire teradata or oracle table to be passed to SAS for processing.
It is neither difficult nor unclear (imho) to extend a sas date constant into a datetime constant.
If it is possible that the dbms column of datetime type might hold a time value as well as a date value, rather than seek equality with &date2:0:0:0"dt, we can use a range like
where dbms_datetime between "&sasdate:00:0"dt and "&sasdate:24:0:0"dt
in place of
where datepart( dbms_datetime) = "&sasdate"d

Re: SAS and Use of Date Functions with ODBC Data

Ksharp — Thu, 17 Feb 2011 05:36:54 GMT

Hi.
Refer to it.
http://support.sas.com/kb/6/413.html

Ksharp