where statement

[Sandeep77]

Hello Experts,

I am using an existing code which runs okay but when I am adding where b.client_code like "%Vanquis%", I am getting ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred prepare could not be completed.: Statement(s) could not be
prepared.: Invalid column name '%Vanquis%'.
(from [Pinsys].[dbo].[debt_trans] as b
65 inner join [Pinsys].[dbo].[transdescrn] as c
66 on b.Tran_code = c.Tran_code
67 where b.tx_date >= &Start_trace and
68 b.tx_date <= &End_Trace and
69 b.client_code like "%Vanquis%"
WARNING: Apparent invocation of macro VANQUIS not resolved.
70
71 and b.tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748');)) as t2
72
73 order by t2.debt_code, t2.tx_date
74 ;
ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred prepare could not be completed.: Statement(s) could not be
prepared.: Invalid column name '%Vanquis%'.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
75 quit;)

I am using the below code:

proc sql;
connect to oledb (provider=sqlncli11.1
properties = ("Integrated Security" = SSPI
"Persist Security Info" = False
"Initial Catalog" = pinsys
prompt = NO
"Data Source" = 'ELECTRA'
read_lock_type = no));
create table Manual_table as
select t2.debt_code,
t2.tran_code,
datepart(t2.tx_date) as Trace_date format date9.,
input(t2.tx_time,time8.) as trace_time format time8.,
t2.dt_tx_date as Trace_dt,
t2.tx_manauto,
t2.td_stddescrn

from (select * from connection to oledb
(select
b.debt_code,
b.tran_code,
b.tx_date,
b.tx_time,
b.dt_tx_date,
b.tx_manauto,
b.client_code
from [Pinsys].[dbo].[debt_trans] as b
inner join [Pinsys].[dbo].[transdescrn] as c
on b.Tran_code = c.Tran_code
where b.tx_date >= &Start_trace and
b.tx_date <= &End_Trace and
b.client_code like "%Vanquis%"

and b.tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748');)) as t2

order by t2.debt_code, t2.tx_date
;
quit;

[Kurt_Bremser · Accepted solution]

The error comes from the external DBMS. Make sure that the code in the explicit pass-through conforms to the syntax rules of the database provider. See how LIKE strings need to be written in your specific DB.

The SAS WARNING about the missing macro may be avoided by using single quotes, and that might also fix the error if the DB requires single quotes there. But you need to check that with the DB documentation, or ask your local DB admins/developers.

[Sandeep77]

Thank you!