Date:         Wed, 27 Jan 2010 14:37:47 -0800
Reply-To:     "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:      Re: SQL Server ODBC Date issue
Comments: To: Shaheen <siqubal@GMAIL.COM>
In-Reply-To:  A<aa3ef770-52e9-4d73-a938-5eb790bf348c@l30g2000yqb.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Hi Shaheen,

I'm going to give you two alternatives,
a couple caveats and slightly different
rede on two words.

Background:
SAS has two date types, DATE and DATETIME.
SQLSvr only has one date time, DATETIME.

Therefore, your two words "problem" and
"fix" I would revamp as "a difference" and
"convert".

Of course, if you were using DATETIME types
in SAS you would not need to "convert" the
type from DATETIME to DATE. But it is perfectly
fine and we all have many programs where we
prefer to use DATE, so we convert from DATETIME
to DATE just as you have.

Your example below is, of course, a very typical
way in SAS to process this. The other method is
that you can combine it all into the first SQL
query. As you know, the text inside the parentheses
is the PASS-THROUGH text that gets shipped off to
the SQL Server box to get run there on the SQL
Server box so it must be in SQL Server syntax.
Now, outside the parentheses, we have SAS SQL
syntax, so you could replace the wildcard asterisk
with the individual column names and you could
wrap the date column with the DATEPART() function
thereby combining both tasks into one step.

Now for the caveats, if your SAS query which
includes the pass-through gets a little too
complex and you start doing joins or significant
column manipulation, of course, joins between
the remote tables and local tables will start
doing the row-by-row joins over-the-wire and
your performance will get bad quick.

Obviously, joins between remote and local table have
to do everything across the i/o wire (which is the
slowest link in the chain), but also a lot of column
manipulation and expression handling on the SAS side
with only the records coming from the remote SQL Svr
also can bog down the i/o dump from SQL Svr as caching
and traffic-cop-i/o-handling are less proficient than
just a quick dump of records from the remote box.

In most situations, not only is it more straight
forward to keep them separate, but you typically
gain the best performance by keeping the download
from SQL Server very simple such as "SELECT *"
and then do all the subsequent joins and manipulation
in SAS because SAS is so much faster. So for plug-n-play
developing and for performance, you will find many of
us doing exactly what you have already!



Hope this is helpful.


Mark Terjeson
Investment Business Intelligence
Investment Management & Research
Russell Investments
253-439-2367


Russell
Global Leaders in Multi-Manager Investing







-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Shaheen
Sent: Wednesday, January 27, 2010 8:35 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL Server ODBC Date issue

Hi,
I am importing data from SQL server 2000 into SAS. I always have
problem with date when I do that and I run the second statement given
below to fix dates. Is there a way to combine these two in one step.

Step1
--------
dm log 'clear';
PROC SQL;
CONNECT TO ODBC (DSN=Prod');
CREATE TABLE USAlphas AS
SELECT * FROM CONNECTION TO ODBC
(
SELECT Ticker, Name_Short, Snapshot_Date, Alpha
FROM Hist
);
quit;

step2
--------
DATA USAlphas;
        SET USAlphas;
        AsOfDt=datepart(Snapshot_Date);
        format AsOfDt YYMMDD10.;
        drop Snapshot_Date;
run;