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
Content-Type: text/plain; charset="us-ascii"
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
From: email@example.com [mailto:firstname.lastname@example.org]
On Behalf Of greg warnick
Sent: Wednesday, October 03, 2007 2:24 PM
Subject: using IF EXIST in PROC SQL
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
Ok, no problem. I then submit the following:
proc sql ;
drop table SQLCA.endca;
drop table SQLCA.bcp;
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
"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
Fred Hutchinson Cancer Research Center