using IF EXIST in PROC SQL

[greg warnick]

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

[Ian Whitlock]

Summary: %IF or comment
#iw-value=1

Greg,

Try

%let bcp = ;
select "*" inti :bcp
from information_schema.tables
where tablename = "BCP" ;
&bcp drop table SQLCA.bcp ;

If this seems too kludgy, then put it in a macro and use a
%IF test to execute the DROP statement.

Ian Whitlock
===============

Date: Wed, 3 Oct 2007 18:23:36 -0000
Reply-To: greg warnick <***@GMAIL.COM>
Sender: "SAS(r) Discussion"
From: greg warnick <***@GMAIL.COM>
Organization: http://groups.google.com
Subject: using IF EXIST in PROC SQL
Comments: To: sas-l
Content-Type: text/plain; charset="iso-8859-1"

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

[Sigurd Hermansen]

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-***@listserv.uga.edu [mailto:owner-sas-***@listserv.uga.edu]
On Behalf Of greg warnick
Sent: Wednesday, October 03, 2007 2:24 PM
To: sas-***@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

[Dianne Rhodes]

Post by Sigurd Hermansen
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
CONNECT TO ODBC (<connection string);
EXECUTE (drop table IF EXISTS SQLCA.endca, SQLCA.bcp) by ODBC;
DISCONNECT FROM ODBC
;
S

My experience with using the libname engine, for example with Oracle, is
that you can allocate a libname to access your Oracle tables, but the
libname engine does not support writing to that libname. So, as Sig has
shown above, you need to use SQL pass through to do write operations,
including Dropping a table.

Dianne Rhodes @ bls.gov

[Gerhard Hellriegel]

Dianne,
I think, the ACCESS-libname engine allows updates and drop table's.
Depending on the parameters and the (DB) rights you have. If you can drop
a table with pass-throgh, you can also do it via a libname. The libnames,
assigned on a DB have nearly the same behaviour as a SAS library. What is
a important difference: you cannot replace a table, you have to drop it
first! With the ACCESS-libname you can use PROC SQL - drop table...
(without pass-through) or PROC DATASETS - delete...
Gerhard



On Thu, 4 Oct 2007 10:56:07 -0400, Dianne Rhodes

Post by Dianne Rhodes

Post by Sigurd Hermansen
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
CONNECT TO ODBC (<connection string);
EXECUTE (drop table IF EXISTS SQLCA.endca, SQLCA.bcp) by ODBC;
DISCONNECT FROM ODBC
;
S

My experience with using the libname engine, for example with Oracle, is
that you can allocate a libname to access your Oracle tables, but the
libname engine does not support writing to that libname. So, as Sig has
shown above, you need to use SQL pass through to do write operations,
including Dropping a table.

[Richard A. DeVenezia]

Post by greg warnick
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;";
proc sql ;
drop table SQLCA.endca;
drop table SQLCA.bcp;
quit ;
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
a. use SQL for other reasons
b. not get WARNINGS in my logs.
Any suggestions?
Regards,
Greg Warnick
Fred Hutchinson Cancer Research Center

In open code you can generate code conditionally by using the IFC function
in a %SYSFUNC wrapper. A cleaner approach might be to construct a macro the
generates the conditional drop statement.

----------------------------
data class;
set sashelp.class;
run;

proc SQL;
%SYSFUNC (IFC(%SYSFUNC(EXIST(work.class,data)), DROP TABLE WORK.CLASS, ));
%SYSFUNC (IFC(%SYSFUNC(EXIST(work.class1,data)), DROP TABLE WORK.CLASS1, ));
%SYSFUNC (IFC(%SYSFUNC(EXIST(work.class2,data)), DROP TABLE WORK.CLASS2, ));
quit;


%macro dropIfExist (object);
%if %sysfunc(exist(&object,data)) %then
drop table &object
;
%else
%if %sysfunc(exist(&object,view)) %then
drop view &object
;
%mend;

data class;
set sashelp.class;
run;

data class1 / view=class1;
set class;
run;

proc SQL;
%dropIfExist (class);
%dropIfExist (class1);
%dropIfExist (class2);
quit;
----------------------------

Richard A. DeVenezia
http://www.devenezia.com/