since your database doesn't appear to like implicit pass-thru then try changing the original code from val ne . to val is not null. That should keep the processing in the database.
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Viel, Kevin
Sent: Monday, October 11, 2010 2:56 PM
Subject: Re: SAS/ACCESS ODBC simple query results in long real time
> -----Original Message-----
> From: Suzanne McCoy [mailto:Suzanne.McCoy@catalinamarketing.com]
> Sent: Monday, October 11, 2010 2:21 PM
> To: Viel, Kevin; SAS-L@LISTSERV.UGA.EDU
> Subject: RE: SAS/ACCESS ODBC simple query results in long real time
> Try it this way:
> Proc SQL ;
> Select Count( * ) Format = Comma15.
> From resultv(where=(not missing(val)))
I was initially unsure of using a dataset option, but the following shows that it should not be used in this manner:
58 Proc SQL ;
59 Select Count( * ) Format = Comma15.
60 From lab.Resultv ( Where = ( Not missing(val)))
ODBC: AUTOCOMMIT turned ON for connection id 0
ODBC_1: Prepared: on connection 0
SELECT * FROM lab.Resultv
SAS_SQL: Cannot handle dataset options.
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error.
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.
ODBC_2: Prepared: on connection 0
SELECT "val" FROM lab.Resultv
ODBC_3: Executed: on connection 0
Prepared statement ODBC_2
62 Quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 14:52.97
cpu time 1:42.73
Kevin Viel, PhD
Senior Research Statistician
Patient Safety & Quality
International College of Robotic Surgery
Saint Joseph's Translational Research Institute
Saint Joseph's Hospital
5671 Peachtree Dunwoody Road, NE, Suite 330
Atlanta, GA 30342
(678) 843-6076: Direct Phone
(678) 843-6153: Facsimile
(404) 558-1364: Mobile
This e-mail, including any attachments is the
property of Catholic Health East and is intended
for the sole use of the intended recipient(s).
It may contain information that is privileged and
confidential. Any unauthorized review, use,
disclosure, or distribution is prohibited. If you are
not the intended recipient, please delete this message, and
reply to the sender regarding the error in a separate email.