Prev: connect SAS server to SQL servers
Next: Where clause usage in %if in macro
From: Paige Miller on 24 Jun 2010 14:01
I connect to an SQL Server database using SAS PROC SQL. Everything
works properly, I get the results I want from my query.

Then, I add a WHERE clause to the SQL code. I want to search for any
records that the variable spec in table db contains the character
string PKG, so I add, in the proper location

WHERE db.spec contains 'PKG'

but that doesn't work.

ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Incorrect syntax near the keyword 'contains'. : [Microsoft]
[ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.

How about

WHERE contains(db.spec,'PKG')

that doesn't work either.

I can't find the proper syntax to make this work. Can someone help?
(SAS 9.2, Windows XP)

--
Paige Miller
paige\dot\miller \at\ kodak\dot\com
From: Ya on 24 Jun 2010 14:15
On Jun 24, 11:01 am, Paige Miller <paige.mil...(a)kodak.com> wrote:
> I connect to an SQL Server database using SAS PROC SQL. Everything
> works properly, I get the results I want from my query.
>
> Then, I add a WHERE clause to the SQL code. I want to search for any
> records that the variable spec in table db contains the character
> string PKG, so I add, in the proper location
>
> WHERE db.spec contains 'PKG'
>
> but that doesn't work.
>
> ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Incorrect syntax near the keyword 'contains'. : [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Statement(s) could not be
> prepared.
>
> How about
>
> WHERE contains(db.spec,'PKG')
>
> that doesn't work either.
>
> I can't find the proper syntax to make this work. Can someone help?
> (SAS 9.2, Windows XP)
>
> --
> Paige Miller
> paige\dot\miller \at\ kodak\dot\com

It seems to that you are using SQL pass through. If so, "contains" may
not be right for SQL server.
The first way you wrote is right when used in pure proc sql.

Ya
From: Ya on 24 Jun 2010 14:34
On Jun 24, 11:15 am, Ya <huang8...(a)gmail.com> wrote:
> On Jun 24, 11:01 am, Paige Miller <paige.mil...(a)kodak.com> wrote:
>
>
>
>
>
> > I connect to an SQL Server database using SAS PROC SQL. Everything
> > works properly, I get the results I want from my query.
>
> > Then, I add a WHERE clause to the SQL code. I want to search for any
> > records that the variable spec in table db contains the character
> > string PKG, so I add, in the proper location
>
> > WHERE db.spec contains 'PKG'
>
> > but that doesn't work.
>
> > ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL
> > Server]Incorrect syntax near the keyword 'contains'. : [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Statement(s) could not be
> > prepared.
>
> > How about
>
> > WHERE contains(db.spec,'PKG')
>
> > that doesn't work either.
>
> > I can't find the proper syntax to make this work. Can someone help?
> > (SAS 9.2, Windows XP)
>
> > --
> > Paige Miller
> > paige\dot\miller \at\ kodak\dot\com
>
> It seems to that you are using SQL pass through. If so, "contains" may
> not be right for SQL server.
> The first way you wrote is right when used in pure proc sql.
>
> Ya- Hide quoted text -
>
> - Show quoted text -

Maybe you can try this :

where db.spec like '%PKG%'
From: Paige Miller on 24 Jun 2010 15:01
On Jun 24, 2:34 pm, Ya <huang8...(a)gmail.com> wrote:
>
> Maybe you can try this :
>
> where db.spec like '%PKG%'

That works! Thanks, Ya!
 | 
Pages: 1
Prev: connect SAS server to SQL servers
Next: Where clause usage in %if in macro