LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2007, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page