Date: Sat, 4 Sep 2010 09:27:46 -0400
Reply-To: Suzanne McCoy <Suzanne.McCoy@CATALINAMARKETING.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Suzanne McCoy <Suzanne.McCoy@CATALINAMARKETING.COM>
Subject: Re: SQL server dates not date times
In-Reply-To: <201009032139.o83I2ptR003981@willow.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"
New school comment:
Definitely look at oledb instead. I found it much faster against SQL/Server 2008 using 9.1.3 in a Windows Server environment. With OLEdb you can also use ssid= to allow the users to authenticate against AD instead of the database so you don't have to put username and password on the connection string.
Old school idea:
Years ago, back in version 6, we were using Oracle 5.something and upgraded to 5.3 (?). Oracle did something to the way they stored datetimes. It was still number of seconds since a point in time but the point in time was so far back that SAS didn't recognize it. We ended up building views adding ~32K to the value and it resolved the issue so we read from views instead of tables. SQL/Server 2008 may store the dates a bit differently than the datetimes. If you could dump unformatted values of both data types there may be a difference in the number of siqnificant digits that you could use to tell which type was which.
________________________________________
From: SAS(r) Discussion [SAS-L@LISTSERV.UGA.EDU] On Behalf Of Quentin McMullen [qmcmull@YAHOO.COM]
Sent: Friday, September 03, 2010 5:39 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SQL server dates not date times
Thanks Boss (and thanks to Mike as well- oh how I miss the halls of SAS
Mekka),
Somehow I managed to write the whole post below without mentioning that we
are using odbc. But despite that oversite, Sig I think your diagnosis is
correct. I am going to look into this some more next week. But my sense
is the ODBC middleware is telling SAS the wrong variable attribute.
I will look into an OLE solution at the same time, as if these options
prove useful we could consider using that instead of ODBC.
Of course when I was writing this morning, was hoping someone would
say "Of course you can store a date column in SQL server and have SAS pull
it down and know it's a date." But as that hasn't happened yet, will have
to put more thought into it.
Thanks again,
--Q.
On Fri, 3 Sep 2010 17:05:19 -0400, Sigurd Hermansen <HERMANS1@WESTAT.COM>
wrote:
>Quentin:
>I suspect that you have SAS/ACCESS OLEdb connections to a MS SQL Server
database. The LIBNAME XXX OLEDB ....; statement appears to let you set
data types using a DBTYPE= or DBSASTYPE = option:
>http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_91/access_oled
b_7366.pdf
>
>These date options (p. 22) look promising:
> <default>
>DBTYPE_DBDATE DATE9.
>DBTYPE_DBTIME TIME8.
>DBTYPE_DBTIMESTAMP and DBTYPE_DATE
>DATETIMEm.n, where m depends on precision
>and n depends on scale
>
>Also,....
>
>"The following table shows the default data types that the SAS/ACCESS
interface to
>OLE DB uses when creating DBMS tables.
>Table 1.5 Default OLE DB Output Data Types
>SAS Variable Format Default OLE DB Data Type
>m.n
>DBTYPE_R8 or DBTYPE_NUMERIC using m.n
>if the DBMS allows it
>$n. DBTYPE_STR using n
>date formats DBTYPE_DBDATE
>time formats DBTYPE_DBTIME
>datetime formats DBTYPE_DBTIMESTAMP
>The SAS/ACCESS interface to OLE DB allows nondefault data types to be
specified
>with the DBTYPE= data set option."
>
>The problem that you are having seems to be related to a setting in the
OLEdb middleware since your database has a date data type and SAS has a
date format that operates on a range of numbers. The SAS "date" value
amounts to nothing more than a number. For some reason the middleware is
converting the SQL Server date value to number on the datetime scale
instead of the date scale and assigning a datetime format instead of a
date format.
>S
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Quentin McMullen
>Sent: Friday, September 03, 2010 10:35 AM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: SQL server dates not date times
>
>Hi All,
>
>Short version of question (which yes, I've seen similar in the archives):
>How can I get DATE variables from SQL server 2008 into SAS as dates
>(rather than as date-times)? Without specifying a conversion for each
>variable (e.g. datepart or whatever).
>
>Longer version:
>Suppose we have a clinical trial database which is happpily growing. Data
>are stored in SQL server 2008. Database has hundreds of tables (say, one
>table/view per case report form per trial), and some of the columns are
>defined in SQL server as dates. [nb: as I understand it, SQL server 2008
>introduced a new DATE type, so these are really stored as dates, not date-
>times (http://msdn.microsoft.com/en-us/library/bb630352.aspx) ]
>
>We want our SAS programmers to be able to hit the database using
>SAS/ACCESS or PROC SQL to select * from a view and pull a table into SAS.
>When we do this now, the date columns from the SQL server database come
>into SAS as date-time variables.
>
>Yes, we could tell everyone how "easy" it is to convert datetimes to dates
>(using datepart or whatever). But that means every time someone pulls a
>table they need to know which columns are dates, and write the conversion
>themselves (could be a fun macro to write, but I'm hoping to avoid even
>that). And when they forget to do that, a bunch of utility code which
>expects standard elements like DOB to be dates will start breaking.
>
>I've seen lots in the archives about this issue, but mostly the responses
>have been to explain date-times vs dates, and how to convert, which I
>understand. But since SQL server 2008 has actual DATE columns, it feels
>reasonable to expect SAS to import these as date variables.
>
>A colleague asked tech support about this issue. They pointed him to:
> http://support.sas.com/kb/2/551.html
> http://support.sas.com/kb/6/413.html
>
>Which seems to say that if we were accessing an Oracle database, we could
>set a global SASDATEFMT option in config file to have all Oracle dates
>come into SAS as SAS dates (rather than date times). This is exactly what
>we want. Unfortunately the first note ends by saying that SAS/ACCESS for
>databases other than Oracle "may offer this global support in a future
>release (i.e. 9.1.3 SP3 or later)." Well, it's later than 9.1.3, so would
>love this global option.
>
>As I hinted above, if there isn't a way to flip a global switch somewhere
>to have SAS import SQL server dates as SAS dates, my next thought is to
>write a macro %downloadfromsql(data=) that would identify the sql server
>columns that are defined as dates (using sql server equivalent of
>dictionary tables), and generate the SQL statement to convert each of the
>date columns to date variables when SAS pulls them down. But seems like a
>hassle of a workaround. It's just hard to believe that everyone who is
>using SAS to access dates in SQL server is using such a workaround. But I
>guess if SQL server really only created the DATE type in SQL server 2008,
>up until that point there was no other option, so there might not have
>been pressure on SAS for this functionality.
>
>Okay, long post (making up for too many years of not posting ?). Best
>wishes to all for a happy weekend (long weekend for US folks, hopefully
>hurricane-free here on the East Coast).
>
>Warm Regards,
>--Quentin
| 
