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.

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Viel, Kevin Sent: Monday, October 11, 2010 2:56 PM To: SAS-L@LISTSERV.UGA.EDU 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))) > ; > quit;

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))) 61 ; 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

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 kviel@sjha.org Confidentiality Notice: 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.