Date: Thu, 15 Feb 2001 09:33:28 -0700
Reply-To: Kenneth Moody <KennethMoody@FIRSTHEALTH.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Kenneth Moody <KennethMoody@FIRSTHEALTH.COM>
Subject: Re: SAS v8 inconsistency -- LIBNAME <token> ODBC and SQL
Content-Type: text/plain; charset=us-ascii
Mark and the list,
I think that the key issue is that the purpose of the
CONNECTION TO dbms-name (dbms-query)
language element is to pass the "dbms-query" directly to "dbms-name"
with minimal SAS interferance; essentially to match the retrieved data
from "dbms-name" with the appropriate SAS variables. As valuable as
consistency is, and as disruptive as inconsistency is, if there are differences
between the SQL/data format/etc. implementations of SAS and "dbms-name"
then I think that they should remain in this case.
It's always critical in any implementation to know which piece of software
will be dealing with which statement, and in this case the whole point is to
pass "dbms-query" off from SAS to another product, so we are best served
if the content of "dbms-query" conforms only to the requirements of the
target software.
Ken Moody
First Health, Metrics Department
Voice: 916-374-3924
EMail: KennethMoody@firsthealth.com
>>> "Terjeson, Mark" <TerjeMW@DSHS.WA.GOV> 02/14 6:11 PM >>>
Hi Ian and All,
My concern is not so much that we have to use a different
test such as the IS NULL vs. testing for an empty string.
I am questioning that:
Is it really SI's knowledge and desire that in this
particular case you cannot cut-n-paste a SAS SQL
syntax from one "SAS PROC SQL ODBC via LIBNAME"
to the other "SAS PROC SQL FROM CONNECTION"?
It works in one and not the other. Also:
Is it SAS's knowledge and desire that the <condition>
cannot be cut-n-pasted between the SQL and a DATASTEP?
They have not broken these consistency rules before, that I know of.
Being a senior language developer for umpteen years for a couple of
SAS's competitors, having two routines within one language that
yielded different results for the exact same syntax would not have
been tolerated. And it doesn't matter which way it goes, but the
same syntax should yield results on the same side of the fence
or the other, but not both sides of the fence.
I understand that how companies choose to handle the padding
vs. null styles is differing in preferences sometimes, but having
the datastep not behave like its SQL counterpart, or having two
syntax packages for SAS SQL, doesn't sound right. To me, it
seems like the database language product should decide only
one way its interfaces will handle the null or pad conversions.
That way the users know what they need to tackle regardless
of which tools they choose to work with (such as datastep or sql).
Not to have one out of five avenues perform 180 degrees different.
In the purist's sense, we should test padded strings with string
comparisons and we should test for nulls on null values, yes,
they really are different things. But in the realm of "interfaces",
most language developers choose to provide some automated
conversion rules and routines, and it doesn't matter to what extent
they do, but usually the rules then apply to the whole product.
I'm sure many folks have different thoughts, but I've tried to put into
words the piece that bothers me, and why unknowing people in
our office generate incorrect results, sometimes without their
knowledge. I really don't think SAS wants large corporations or
small independent users to have one person in the office use one
acceptable and tested SAS SQL syntax, and give it to a second
person in the office (as tested and verified SAS code) and have
the second person merely use a different ODBC setup method,
both acceptable SAS methods, and have two people in the office
get devastatingly different results.
How would you package that as a marketing feature?
Anyway, it sounded like something to either be aware of, or to
worry about. So I brought it up just trying to be helpful. I thought
maybe it was worthy enough to be considered and evaluated in
the SAS suggestion box.
Mark
-----Original Message-----
From: Ian Whitlock [mailto:WHITLOI1@WESTAT.COM]
Sent: Wednesday, February 14, 2001 12:12 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SAS v8 inconsistency -- LIBNAME <token> ODBC and SQL
Mark,
I am not sure whether it is consistency or lack of ability that bothers you.
The only code marked bad is
libname mylib ODBC dsn=odbctoken;
proc sql;
create table test4 as
select *
from mylib.table1
where var eq '';
quit; * yields zero rows - BAD ;
This code worked for me as a replacement.
libname q odbc complete="dsn=MS Access 97 Database;dbq=c:\junk\db1.mdb" ;
proc sql;
create table test4 as
select *
from q.table1
where test is null ;
quit;
Ian Whitlock <whitloi1@westat.com>
