Date:         Wed, 3 Oct 2007 18:10:08 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: using IF EXIST in PROC SQL
In-Reply-To:  <1191435816.423746.84920@y42g2000hsy.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Greg:
At least one flavor of SQL (MS SQL Server) allows an IF EXISTS option
that you will be able to 'pass thru' an 'action query' in SQL Server
syntax. Try something along these lines:
   CONNECT TO ODBC (<connection string);
   EXECUTE (drop table IF EXISTS SQLCA.endca, SQLCA.bcp) by ODBC;
   DISCONNECT FROM ODBC
   ;

S

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of greg warnick
Sent: Wednesday, October 03, 2007 2:24 PM
To: sas-l@uga.edu
Subject: using IF EXIST in PROC SQL


Hi SAS-L,

I am connecting to a SQL Server in SAS using a LIBNAME statement that
uses an ODBC connection (System DSN) on my local computer.

libname SQLca odbc noPrompt = "driver=SQL
Server;server=JUPITER;database=dbStudy;";

Ok, no problem.  I then submit the following:

proc sql ;
   drop table SQLCA.endca;
   drop table SQLCA.bcp;
   quit ;

Ok, SAS drops the tables via ODBC, but I get a warning in my log:
WARNING: File SQLCA.bcp.DATA does not exist.
WARNING: Table SQLCA.bcp has not been dropped.

Not the end of the world, but I would prefer a cleaner log and would
like to use IF EXIST (select * from information_schema.tables where
tablename =
"BCP") DROP TABLE SQLCA.endca;

However, SAS SQL doesn't seem to support this... I can use a proc
datasets, or simply drop the table without the conditional logic, but
would prefer to: a. use SQL for other reasons b. not get WARNINGS in my
logs.

Any suggestions?

Regards,
Greg Warnick
Fred Hutchinson Cancer Research Center