using if object_id in SQL server via SAS

Question

I am running some SQL queries via SAS using the credentials below.

The Drop Table command on it's own is fine, creating the tables is fine. I get an error with the "if object_id" line.

ERROR: Execute error: ICommand::Execute failed. : Invalid column name 'scratch.dbo.eof_tv_channel_orders_fy16_NEW_t'

I am assuming it does not like the SAS macro variable in the quotes (I tried single too).

Is there some other SQL command I could use? I could do a proc delete in SAS outside the SQL but would rather not.

%let test_flag=t;

PROC SQL;
CONNECT TO OLEDB (
  CURSOR_TYPE=STATIC 
  BULKLOAD=YES 
  READ_ISOLATION_LEVEL=RU 
  PROPERTIES=("Integrated Security"=SSPI)  
  PROVIDER=SQLOLEDB.1  
  DATASOURCE="XXXXXXX"
);


EXECUTE (


if object_id ("scratch.dbo.eof_tv_channel_orders_fy16_NEW_&test_flag.", 'u') is not null
  drop table scratch.dbo.eof_tv_channel_orders_fy16_NEW_&test_flag.

) by OLEDB;

quit;

Take a look at the BQUOTE suggestion here: http://stackoverflow.com/questions/31714447/use-sas-macro-variable-within-proc-sql-teradata-passthrough/31716601 — Stig Eide, Aug 04 '15 at 10:37

Pekka · Answer 1 · 2015-08-04T10:53:35.250

The problem is that you are using double quotes instead of single quotes. In SQL server (and in most SQL flavors) double quotes don't mean character string.

For object_id function character string is required for the first parameter.

To overcome this problem you must use single quotes. However when you use macro-variable in single quotes it won't be resolved.

So you have to do this:

object_id(%str(%'scratch.dbo.eof_tv_channel_orders_fy16_NEW_&test_flag%'), 'u')

Then it will be resolved to:

object_id('scratch.dbo.eof_tv_channel_orders_fy16_NEW_t', 'u')

and it should work.

E: as Stig Eide suggested also this can be used and it is equivalent to the %str(... method above:

object_id(%BQUOTE('scratch.dbo.eof_tv_channel_orders_fy16_NEW_&test_flag'), 'u')

You have to choose by yourself which syntax looks better.

using if object_id in SQL server via SAS

1 Answers1